Bank reconciliation template
By the Analistable team · Updated · 3 min read
The workbook has a Bank sheet, a Ledger sheet and a Reconciliation sheet. Paste the month's bank lines and cash-book lines, enter the opening balance, and formulas match items by reference and amount, list what appears on only one side, and prove the adjusted bank balance agrees with the ledger.
Part of our guide: How to reconcile data in Excel
What the template does
A bank reconciliation explains why the bank statement balance and your cash book balance differ at month end. The differences are nearly always timing items — receipts banked late, payments not yet cleared — or items the bank knows about first, such as fees and interest. The template does the matching for you and lays out the classic reconciliation, following the method in how to do a bank reconciliation in Excel.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Bank | Date, Description, Reference, Amount | Status: Matched or Bank only |
| Ledger | Date, Description, Reference, Amount | Status: Matched or Ledger only |
| Reconciliation | Opening balance (B2) | Closing balances, adjustments, difference |
How to fill it in
- Download the statement as CSV and paste date, description, reference and signed amount (money in positive, money out negative) into Bank from A2.
- Export the bank account's ledger or cash book for the same period and paste it into Ledger in the same column order.
- Enter the agreed opening balance in Reconciliation B2. If the two opening balances differ, reconcile last month first.
- Fill the Status column down to the last row, then review every Bank only and Ledger only line.
How the formulas work
A line counts as matched when the other sheet has the same reference and the same amount. Dates are ignored on purpose, because clearing dates rarely equal booking dates:
=IF(COUNTIFS(Ledger!$C$2:$C$100,C2,Ledger!$D$2:$D$100,D2)>0,"Matched","Bank only")The Reconciliation sheet adds ledger-only items to the bank balance and removes bank-only items:
=SUMIFS(Ledger!D2:D100,Ledger!E2:E100,"Ledger only")
=B4+B7-B8
=ROUND(B9-B6,2)Worked example
September sample: seven bank lines and seven ledger lines. Five match; the bank fee and interest are bank-only, and a cheque not yet cleared (PAY-224) and a late deposit (INV-1047) are ledger-only.
| Line | Value |
|---|---|
| Opening balance | 10000 |
| Bank closing balance | 12977 |
| Ledger closing balance | 13240 |
| Add: ledger-only items | 250 |
| Less: bank-only items | -13 |
| Adjusted bank balance | 13240 |
| Difference | 0 |
Bank-only items are usually real transactions you still need to post. Once fees and interest are booked in the ledger, they match next time.
Limits and when to move on
- Matching needs a shared reference. If your bank shows only free-text descriptions, match on amount and a date window instead; joining on dates and date ranges shows how.
- Two lines with the same reference and amount both show Matched even if only one has a partner. Scan duplicates before signing off.
- One account and one month per copy. For several accounts, or a year of statements, combine the monthly bank statements first, or load the files into Analistable and ask which items are unmatched.
Frequently asked questions
- Why is my difference not zero?
- Usually a line matched on reference but the amounts differ slightly, or an opening balance is wrong. Sort both sheets by Status and check each one-sided item; the difference equals the items you have not yet explained.
- Should payments be negative?
- Yes. Use one signed Amount column on both sheets: receipts positive, payments negative. If your bank export has separate debit and credit columns, add a column that subtracts one from the other.
- Does it work in Google Sheets?
- Yes. Open the downloaded file in Google Sheets; it uses only COUNTIFS, SUMIFS, SUM, IF and ROUND.