How to reconcile a bank statement in Excel

Reconciling means proving that your records and the bank's statement agree. A spreadsheet is enough if you follow a fixed routine.

1. Start from clean statement data

Get the statement into rows: date, description, amount, balance. If you convert from PDF, make sure the converted total ties to the printed closing balance before you do anything else, otherwise every later step inherits the error.

2. Check the arithmetic

Opening balance + credits − debits = closing balance. If the gap is zero, the statement data is complete. If not, find the missing row or flipped sign first.

3. Match against your ledger

Add a column for your ledger entry. For each statement row, find the matching record by amount and date (allow a few days for clearing). Mark matched rows.

4. List what is unmatched

Unmatched statement rows are usually bank fees, interest or deposits you have not recorded. Unmatched ledger rows are usually uncleared cheques or payments in transit.

5. Prove the difference

Adjusted ledger balance should equal the statement closing balance after accounting for the unmatched items. Keep the sheet with the statement as your evidence.

Where PennyTie fits

PennyTie handles steps 1 and 2: convert a text-based PDF to CSV in your browser and see exactly whether the totals tie out.

Try it: open PennyTie, drop a text-based statement PDF (or use the built-in fictional sample). Free for statements up to 5 pages; nothing is uploaded.

More guides: PDF to CSV · Totals don't match · No-upload conversion · Scanned vs text · Reconcile in Excel · For bookkeepers