Bank Reconciliation Template
Transaction matching plus a full formal Bank Reconciliation Statement, with a live reconciled/unreconciled check.
No sign-up or email required. Real Excel formulas, not a screenshot.
What this template does
- ✓Matches each bank-statement credit to an open receivable automatically, using XLOOKUP wrapped in IFERROR.
- ✓Builds the complete formal reconciliation statement: cash-book balance, add outstanding cheques, less deposits in transit, add bank interest, less bank charges, to the bank-statement balance.
- ✓Flags the final difference automatically — green when reconciled, red when it isn't.
What’s inside
Receivables
Sample open invoices used as the matching source for bank credits.
Bank Statement
Sample bank transactions, each matched to a receivable via a live XLOOKUP + IFERROR formula.
Reconciliation Statement
The full two-way adjustment from cash-book balance to bank-statement balance: outstanding cheques, deposits in transit, bank interest and charges, and a conditionally-formatted difference cell (red if mismatched, green if reconciled).
Read Me
The workbook's own documentation and a link back to the full guide.
How to use it
Replace the sample receivables, bank transactions, outstanding cheques and deposits-in-transit with your own data. Every total and the final difference check recalculates automatically.
Want the full walkthrough?
The guide explains every formula in this workbook step by step, with a worked example.
Go deeper
Advanced Excel for Finance & Business
This template is one piece of a bigger workflow. The course builds the whole thing with real datasets, step by step, with a graded assessment and a verifiable certificate.