A bank reconciliation answers one question: does the money your records say you have match what the bank says you have, and if not, why? Excel handles it well once both lists are in the same shape.
1. Put the statement in a sheet
Create a sheet called Bank with the statement’s transactions: Date (A), Description (B), Money out (C), Money in (D), Balance (E). StatementEasy produces exactly this from the PDF. Add a signed amount in F:
F2: =D2-C2
2. Check the statement itself
Make sure no row is missing before comparing anything. In G2 (and fill down from row 3), compare each printed balance with the previous one plus the movement:
G3: =IF(ABS(E2+F3-E3)>0.005,"check","")
Any “check” means a missing or misread row right there. (StatementEasy runs this check for you; rows that fail are highlighted before you download.)
3. Put your records next to it
Create a sheet called Books from your accounting system, cash book or budget, with at least Date (A), Description (B) and a signed Amount (D), money out negative.
4. Match the transactions
On the Bank sheet, flag rows that appear in your books with the same amount within three days:
H2: =IF(COUNTIFS(Books!$D:$D,F2,Books!$A:$A,">="&A2-3,Books!$A:$A,"<="&A2+3)>0,"matched","missing in books")
Do the same the other way round on the Books sheet, against the Bank sheet’s F and A columns, to find entries the bank has not processed yet.
5. Explain the difference
Build a small summary:
| Line | Formula idea |
|---|---|
| Closing balance per bank | last value in Bank!E |
| + Deposits in your books not yet on the statement | SUMIFS of positive unmatched Books amounts |
| − Payments in your books not yet on the statement (outstanding checks) | SUMIFS of negative unmatched Books amounts |
| = Adjusted bank balance | should equal your books balance |
Bank-only items, such as fees, interest or direct debits you did not record, are flagged “missing in books”. Record them in your books, and the two balances should meet.
Tips
- Reconcile monthly, statement by statement; small differences are easy to find, large ones are not.
- Keep the statement rows untouched and do your matching in extra columns, so you can always compare with the PDF.
- Several months at once? Drop all the PDFs on StatementEasy and use “All files combined” to get one Bank sheet with a File column.