How to reconcile a bank statement in Excel (step by step)

Reconciliation answers one question: does the money your records say you have match what the bank says you have? Doing it monthly catches missed transactions, duplicate entries and fraud while they are still easy to fix.

1. Get the statement into rows

Start with one transaction per row: Date, Description, Debit, Credit and Balance. Converting the PDF with a statement converter is the fastest route; retyping is where most errors start.

Add a signed Amount column with =D2-C2 (credit minus debit) so every row has a single number you can sum.

2. Prove the statement is internally consistent

Before comparing to your books, prove the statement data itself is complete. In a Running column put the opening balance in the first cell, then =G1+E2 down the sheet. Add a Check column with =ROUND(F2-G2,2)=0. Any FALSE means a missing or mistyped line above it.

This is exactly the check SheetStatement performs automatically and highlights in the review table.

3. Match against your ledger

Export your ledger for the same period. Use a helper key such as =TEXT(A2,"yyyy-mm-dd")&"|"&E2 in both sheets and XLOOKUP the key from one into the other. Unmatched rows are your reconciling items: outstanding checks, deposits in transit, bank fees you haven't recorded, or errors.

4. Build the reconciliation summary

Bank ending balance + deposits in transit − outstanding checks = adjusted bank balance. Book balance + interest − bank fees ± errors = adjusted book balance. The two adjusted numbers must be equal. Save the workbook with the month in its name; auditors and future you will thank you.