How to Reconcile Bank Statements in Excel
Reconciliation is a matching problem: every statement transaction should correspond to a ledger entry, and the differences should explain themselves. Excel handles it well — once the statement side is actual data rather than a PDF.
Set up the two sides
Put statement transactions on one sheet and your ledger export on another, each with date, description and signed amount columns. If your statement is a PDF, convert it rather than retyping — you'll get numeric amounts and a running balance column that becomes your integrity check.
Match with formulas
Start coarse and refine: exact amount+date matches first, then amount-only within a date window, then manual review for the remainder.
- XLOOKUP or INDEX/MATCH on a concatenated date|amount key catches the bulk
- COUNTIFS flags duplicate amounts that need date disambiguation
- A status column (Matched / Timing / Investigate) keeps the residue visible
Explain the difference
The reconciliation completes when: ledger balance + unmatched ledger items − unmatched statement items = statement closing balance. Timing differences (deposits in transit, uncleared checks) should account for everything; anything left is an error or a missing entry.
The statement's running balance column makes the final check trivial: if your converted data's last balance equals the printed closing balance, the statement side is complete.