How to Reconcile Bank Statements in Excel
Last updated
Reconciliation answers a narrower question than extraction: do the transactions in the spreadsheet explain the balances printed by the bank? It is a useful control for missing rows, reversed signs, duplicates, and transcription errors.
Collect the statement controls
Record the statement period, opening balance, closing balance, currency, and account tail. Keep these values separate from the transaction table so they remain visible assumptions rather than hidden numbers inside formulas.
Use consistent debit and credit signs
Choose one convention and keep it throughout the workbook. A clear layout uses positive numbers in separate Debit and Credit columns. The signed movement is then credit minus debit. Do not mix negative debits with a separate debit column.
Check every running balance
For each transaction, calculate expected balance as previous balance plus credit minus debit. Compare it with the balance printed on that row. A non-zero difference identifies the first point where the spreadsheet and statement stop agreeing.
Check the statement total
Calculate opening balance plus total credits minus total debits. Compare the result with the declared closing balance. If the difference is zero but individual running-balance checks fail, look for offsetting mistakes or duplicate rows.
Investigate, do not force the result
Never insert a balancing transaction merely to make the difference disappear. Return to the source page and look for a missing fee, a dropped decimal point, a sign reversal, an unreadable line, or an incomplete page sequence. Record corrections in a review log.
Finish with a human review
A balanced statement is stronger evidence than an unchecked export, but it is not proof that descriptions or dates are perfect. Review flagged and material transactions before importing the workbook into accounting software.
Create a balance-checked export · Read the conversion workflow