The best template is the one that tells you when the reconciliation is wrong, not the one with the nicest layout.
A good bank reconciliation template in Excel matches bank lines to the cash book, lists unmatched items by type, builds a statement that takes the bank balance to the ledger balance, and shows zero difference only when everything is explained. Free templates give you a layout; paid systems add checks and sign-off; AI-assisted tools do the matching for you.
The statement in formula form, with payments shown as positive amounts:
Adjusted bank balance: =BankBalance+DepositsInTransit-UnpresentedPayments
Adjusted ledger balance: =LedgerBalance+BankOnlyReceipts-BankOnlyCharges
Unexplained difference: =ROUND(AdjustedBank-AdjustedLedger,2)
Step-by-step method: bank reconciliation in Excel. Reconciling item types: outstanding cheques, deposits in transit and more.
| Free template | Paid Excel system | AI-assisted tool | |
|---|---|---|---|
| Layout for the statement | Yes | Yes | Yes |
| Transaction matching | Usually manual ticking | Formula-based for common cases | Automated, including descriptions that don't match exactly |
| Several bank accounts | Copy the sheet | Often built in | Depends on the product |
| Reconciling items by type | Sometimes | Usually | Usually |
| Free template | Paid Excel system | AI-assisted tool | |
|---|---|---|---|
| Flags an unexplained difference | Sometimes | Usually | Usually |
| Preparer and reviewer sign-off | Rarely | Often | Depends on the product |
| Protected formulas | Rarely | Often | Not applicable (software) |
| Ageing of reconciling items | Rarely | Often | Depends on the product |
| Free template | Paid Excel system | AI-assisted tool | |
|---|---|---|---|
| Price | Free | Usually one-time | One-time or subscription |
| Monthly effort | Highest: you match by hand | Medium: you clear exceptions | Lowest: you review suggested matches |
| Best for | Low volume, one account | Small teams who need controls and evidence | Higher volume or messy bank descriptions |
"Usually", "often" and "depends" are deliberate: products vary, so check each one against the list at the top of this page.
UK templates say "unpresented cheques" and "cash book"; US templates say "outstanding checks" and "cash account" or "book balance". The reconciliation logic is identical.
AI software that matches your bank statement to your ledger on your own computer and builds the reconciliation statement. $1,997 once-off; see it on the built-in example first.
The Bank Reconciliation Template reconciles up to five bank accounts across eight transaction categories, with an allocation check that confirms every transaction is coded before the reconciliation counts as clean. Excel, no macros, one-time price.
For low volumes and one account, yes, if it calculates the difference and you add a reviewer sign-off. As volume, accounts or audit needs grow, a paid system with checks saves time and risk.
Pasted bank and ledger data, matching, unmatched items by type, a statement from bank to ledger balance, a calculated difference, preparer and reviewer, and ageing of reconciling items.
When you have a high number of transactions or bank descriptions that rarely match ledger references, so manual or formula matching takes most of the time.
Last updated 15 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.