Bank Reconciliation Template for Excel (Free, With Automatic Matching)
Your bank says the account closed the month at 84,312.90. Your ledger says 81,940.15. Between them sits a cheque that has not cleared, a bank charge nobody entered, and possibly a figure typed with two digits swapped. You need a file to open, paste two exports into, and see what disagrees.
Download the free bank reconciliation template (.xlsx) — no sign-up, no email gate.
Scope: this is the template page. The matching method in depth lives in reconciling a bank statement in Excel with AI, the scripted version in the Python bank reconciliation tutorial.
What is in the template
Three sheets, all formulas live, nothing locked.
| Sheet | What it does |
|---|---|
| Reconciliation | The two-column statement. Bank side: closing balance per bank statement, add deposits in transit, less outstanding cheques, adjusted bank balance. Book side: closing balance per ledger, add bank credits not recorded, less bank charges / fees not recorded, adjusted book balance. Then a DIFFERENCE (must be zero) row printing RECONCILED or OUT BY <amount>. |
| Bank statement | Columns Date / Description / Amount / Matched? for 200 rows. Paste your bank export here. |
| Ledger | The same four columns, 200 rows. Paste your cash/bank GL export here. |
The Matched? column on each sheet is a COUNTIFS against the other sheet's Amount column. Paste both sides and every filled line reads matched or UNMATCHED; unused rows stay blank. The unmatched lines are your worklist.
Bank rec is one step in a wider close: the month-end close checklist template sequences the 20 tasks around it (walkthrough).
Why the two balances differ in the first place
A bank reconciliation proves your cash records against the bank's, and almost nobody's two balances agree on the last day of the month. Three causes:
- Timing. You posted a cheque on the 29th; the supplier banks it on the 4th. Both records are correct, at different points in the same transaction.
- Items only one party knows about. The bank charged 80 in fees and credited 12 of interest; your ledger never heard.
- Errors. A transposed figure, a payment entered twice, an amount on the wrong side. Yours are far more common than the bank's.
The classic two-column format (and which side each item belongs on)
People get this backwards: a bank reconciliation adjusts both balances independently until they meet, rather than forcing one to the other.
Bank side — start from the bank statement's ending balance:
- Add: deposits in transit — money you recorded as received that the bank has not yet processed.
- Add: bank errors that understated the account (the standard OpenStax example: +320).
- Subtract: outstanding cheques — cheques issued and recorded that have not yet cleared.
Book side — start from your own cash/general ledger balance:
- Add: income not recorded on the books, e.g. bank interest income.
- Subtract: expenses not recorded on the books, e.g. bank account charges.
- Subtract: NSF (returned/dishonoured) cheques.
- Add or subtract: your own recording errors (the same example deducts 240 here).
The rule that keeps you straight: items the bank already knows about but your books do not go on the book side. Charges, interest earned and bounced cheques are all book-side. Only deposits in transit and outstanding cheques adjust the bank statement side — the things you know about and the bank does not yet.
You are finished when the two adjusted figures agree: "after the bank reconciliation is prepared accurately, both the bank balance and the company balance will be the same amount." The template's DIFFERENCE row is that test, automated.
Which items need a journal entry
Only the book-side ones: "the transactions that the bank is aware of but the company is not must be journalized in the entity's records." Deposits in transit and outstanding cheques generate no journal entry at all — the timing simply has not caught up. That is the commonest mistake in a manual rec: "correcting" an outstanding cheque with a journal creates a real error where there was none.
How the Matched? formula works
The template's matching flag is a COUNTIFS. Microsoft's documented syntax is:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)criteria_range1 and criteria1 are required; further range/criteria pairs are optional, up to a documented 127 pairs, and they need not be adjacent. The logic is AND across pairs — "if all of the first cells meet their associated criteria, the count increases by 1" — and it returns a count, not a value, which is exactly what a yes/no flag needs.
The Bank statement sheet's Matched? column (D2 filled down to D201) asks whether the amount appears anywhere in the Ledger's Amount column; the Ledger runs the mirror image:
=IF(C2="","",IF(COUNTIFS(Ledger!$C:$C,C2)>0,"matched","UNMATCHED"))
=IF(C2="","",IF(COUNTIFS('Bank statement'!$C:$C,C2)>0,"matched","UNMATCHED"))Three things to notice. The outer IF(C2="","", …) leaves unused rows blank instead of stamping 200 rows with UNMATCHED before you paste anything. The sheet name sits in single quotes — Excel requires them whenever a sheet name contains a space. And the whole-column $C:$C means pasting past row 201 only needs the formula filled down.
Tightening it with a second criteria pair
Amount alone will happily match a 500 payment in January to a 500 payment in March. If both exports carry dates, add a date pair, keeping the blank guard:
=IF(C2="","",IF(COUNTIFS(Ledger!$A:$A,A2,Ledger!$C:$C,C2)>0,"matched","UNMATCHED"))If clearing dates lag a few days, match within a window using the operator-and-ampersand pattern from Microsoft's documentation (=COUNTIFS(A2:A7,"<" & A6,B2:B7,"<" & B4)):
=IF(C2="","",IF(COUNTIFS(Ledger!$A:$A,">=" & A2-3,Ledger!$A:$A,"<=" & A2+3,Ledger!$C:$C,C2)>0,"matched","UNMATCHED"))Three details matter. The Ledger date range appears twice, once per criterion — that is required: COUNTIFS(range, criteria1, criteria2) is not valid syntax. Every range must have the same rows and columns as the first, which is why they all stay whole-column once one of them is. And both sheets need real dates in column A; a blank date makes A2-3 evaluate as −3 and the row reads UNMATCHED even when the amount exists.
Two documented quirks if you rewrite the formula: wildcards work in criteria (? = any single character, * = any sequence, tilde ~ escapes a literal one), and a criteria argument pointing at an empty cell is treated as 0 — the reason for the blank guard. COUNTIFS works in Excel 2016 and later, including Microsoft 365 and Excel for the web.
Working the unmatched items
Filter both sheets to UNMATCHED and triage each line into four buckets.
- On the bank, not in the ledger. Charges, interest, a forgotten direct debit, a returned cheque. Book-side adjustments, each needing a journal entry.
- In the ledger, not on the bank. Outstanding cheques and deposits in transit. Bank-side adjustments, no journal entry.
- On both sides but flagged unmatched. Nearly always an amount discrepancy or a sign problem — see troubleshooting below.
- Genuinely wrong. A duplicate posting, a transaction belonging to another account, fraud. Investigate before adjusting.
Enter bucket-1 totals into the book side of the Reconciliation sheet ("bank credits not recorded", "bank charges / fees not recorded") and bucket-2 totals into the bank side ("deposits in transit", "outstanding cheques"), then read the DIFFERENCE row. Only the shaded input cells need typing; the balances and DIFFERENCE are formulas.
The standard adjusting journals
Every book-side line becomes a journal. The three most common, in textbook wording:
| Item | Entry |
|---|---|
| Monthly bank service charges | Debit Bank Service Charges, credit Cash — "Debit Bank Service Charges and credit Cash for 80 each." |
| Dishonoured / NSF customer cheque | Debit Accounts Receivable, credit Cash — "to adjust the account for the returned check for insufficient funds." The receivable revives; the customer still owes you. |
| Cheque or direct debit never entered | Debit the expense, credit Cash — "Debit Dues expense and credit Cash each for 240 … To adjust for check that was not recorded properly." |
Interest credited by the bank runs the other way: debit Cash, credit Interest Income.
If you reconcile inside an accounting system instead, the same completion test applies. Zoho Books, for example, puts it at Banking > select the account > Gear icon (top right) > Reconcile Account, and tells you to "make sure that the Closing Balance and Cleared Amount are the same and the difference is zero". One warning from the same help page: "The opening balance cannot be edited once the transactions have been reconciled."
When it will not balance: a troubleshooting order
Work these in sequence rather than re-reading the whole sheet.
1. Divide the difference by 9
A whole-number result is the classic sign of a transposition error — two digits swapped. The standard worked example: 35,800 − 34,000 = 1,800; 1,800 ÷ 9 = 200; equipment entered as 5,300 when it should have been 3,500. Search for a figure whose digits, reordered, give the right value. Treat it as a strong hint, not proof — the source says only that you "may have transposed a figure", a shifted decimal (100 versus 1,000) divides by 9 too, and differences can be divisible by 9 by coincidence.
2. Divide the difference by 2
This catches an amount posted on the wrong side: 34,100 − 33,900 = 200; 200 ÷ 2 = 100; dividends of 100 posted as a credit instead of a debit. In a bank rec it usually surfaces as a sign error — a receipt entered as a payment, or a bank export where debits arrive positive while your ledger stores them negative. If the conventions disagree, multiply one Amount column by −1 in a helper column and match against that.
3. Check the opening balance
If last month's closing balance was never rolled forward, or someone edited a prior-period transaction after sign-off, every line can match and the totals still be out by a constant. Compare this month's opening figure to last month's reconciled closing figure.
4. Look for duplicates
A payment entered twice, or a bank export downloaded across overlapping date ranges, produces a difference exactly equal to one transaction. Scan for an amount equal to the difference; full method in removing duplicates in Excel.
5. If none of the tests fire
The textbook's honest answer: "you will have to go back through one step at a time until you find the error." Halve the problem — reconcile the first fortnight, then the second. And balancing is not proof of correctness: "we can have errors and still be mathematically in balance." Two compensating errors reconcile perfectly.
Where this template stops
It matches on amount only (plus date if you add the second pair), so it cannot handle:
- One deposit covering several invoices. A 4,800 credit settling invoices of 1,500, 1,300 and 2,000 reads
UNMATCHEDon both sides forever — no single ledger line equals 4,800. - Descriptions that differ.
CARD PMT ACME TRADING L.L.C 4471versusAcme Trading LLCgives the formula nothing to use (fuzzy matching in Excel is the formula workaround). - Duplicate amounts. Two identical 500 payments both flag
matchedeven if only one exists on the other side —COUNTIFScounts occurrences rather than pairing rows one-to-one, so check repeated amounts by eye. - A PDF-only statement. Get the data into cells first: converting a bank statement PDF to Excel.
For a few hundred lines a month those limits are manageable. Past a few thousand, manual triage stops being viable.
One option for what the template cannot reach
That is the gap HISAB 360 is built for: a paid Excel add-in with an AI chat panel docked in the workbook. For reconciliation it matches statement lines to ledger rows on amount plus a date tolerance, handles many-to-one deposits, matches descriptions fuzzily, flags duplicates and writes a reconciliation report into the sheet. It also reads the ledger side straight from QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks or Sage, and can post the adjusting journals back. Pricing: $7/month or $60/year if you bring your own AI key, $10 or $20/month for HISAB's own AI, or $199 once for a perpetual licence.
The honest limits: Windows desktop Excel only (365 or 2019+) — not Mac, not Excel on the web. Paid after a 15-day free trial of every feature plus 50 AI credits, no card, once per machine; there is no permanently free tier. Connecting an accounting system means authorising it yourself over OAuth. And any write back to a ledger should be reviewed before approval, then reconciled afterwards — an AI match is a proposal, not an audit conclusion.
If your reconciliations fit inside the free template, use the free template. Keep a copy per bank account and it carries you through most months for nothing.
Frequently asked questions
Is this bank reconciliation template really free?
Yes. The .xlsx file downloads directly, no sign-up or email, and nothing inside is locked — all three sheets use ordinary formulas you can edit.
Which side of the reconciliation do bank charges go on?
The book side, as a deduction: the bank already knows about its own charges, so your ledger is the out-of-date record. Same for interest earned (added) and dishonoured cheques (deducted).
Do I need a journal entry for an outstanding cheque?
No. Outstanding cheques and deposits in transit are timing differences; your books are already correct. Entries are required only for book-side items — the transactions your records are missing.
Why does the difference divide evenly by 9?
That is the classic signature of a transposition error, two digits swapped. Look for an amount whose digits reordered give the correct value (1,800 ÷ 9 = 200 pointed to 5,300 entered instead of 3,500). A strong indicator, not proof — a shifted decimal gives the same signature.
Will the Matched? column work in older versions of Excel?
Yes. It uses only IF and COUNTIFS, and COUNTIFS is available in Excel 2016 and later, including Microsoft 365 and Excel for the web. No dynamic-array functions are involved.
How do I match when the bank description and the ledger description differ?
The template cannot — amount-only matching has no description data. Either normalise both description columns and match on a cleaned key (the fuzzy matching guide), or use a tool that matches descriptions approximately (reconciling a bank statement in Excel with AI).
Try HISAB 360 on your own workbook
HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.