Excel guide · 5 min read
How to do a bank reconciliation in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Bank date | Bank reference | Bank amount | Book date | Book reference | Book amount | Statement balance | Cash book balance | ||
| 2 | Oct 1, 2026 | DEP-201 | 1200 | Oct 1, 2026 | DEP-201 | 1200 | 6631.95 | 6934.2 | ||
| 3 | Oct 3, 2026 | CHQ 1041 | -350 | Oct 2, 2026 | CHQ 1041 | -350 | ||||
| 4 | Oct 6, 2026 | CHQ 1042 | -125.4 | Oct 5, 2026 | CHQ 1042 | -125.4 | ||||
| 5 | Oct 9, 2026 | DEP-202 | 860 | Oct 8, 2026 | DEP-202 | 860 | ||||
| 6 | Oct 15, 2026 | CHQ 1043 | -480 | Oct 12, 2026 | CHQ 1043 | -480 | ||||
| 7 | Oct 20, 2026 | BANK FEE | -15 | Oct 13, 2026 | CHQ 1044 | -125.4 | ||||
| 8 | Oct 28, 2026 | DEP-203 | 540 | Oct 27, 2026 | DEP-203 | 540 | ||||
| 9 | Oct 31, 2026 | INTEREST | 2.35 | Oct 30, 2026 | CHQ 1045 | -310 | ||||
| 10 | Oct 31, 2026 | DEP-204 | 725 |
Download this sheet as an Excel practice file (.xlsx)
Set the two lists up so they can be matched
The sheet above is October for a small business account. Columns A:C are the bank statement export, E:G are the cash book (what you recorded), and I2 and J2 hold the two closing balances. Both lists started the month from the same reconciled balance, so every difference comes from October's items.
Two things make the formulas simple. First, one signed amount column per list: deposits positive, payments negative. If your bank exports separate Debit and Credit columns, add a column with =Credit-Debit and match on that. Second, a reference you can match on, here the cheque or deposit number. Banks often bury it in a description like "CHQ 1042 CLEARED"; pull it into its own column with Flash Fill (Ctrl+E) or TEXTAFTER before you start. Keep the bank export as values: never retype it. If the references still refuse to match, the usual culprits are text-vs-number IDs and trailing spaces, covered in compare two columns in Excel.
Step 1: The trap of matching on amount alone
The obvious first try counts how often each book amount appears on the statement and keeps the ones that appear zero times.
Result: spills 2 rows × 1 column
| CHQ 1045 |
| DEP-204 |
CHQ 1045 and DEP-204 spill down. It looks finished, but it's wrong: CHQ 1044 is missing.
Step 2: Match on reference and amount
CHQ 1042 and CHQ 1044 are both for 125.40, and only CHQ 1042 has cleared. Matching on amount alone sees one -125.40 on the statement and ticks off both cheques. Repeated amounts are everywhere in real books (rent, subscriptions, the same supplier invoice every week), so this is the error that makes a reconciliation "balance" when it shouldn't. Add the reference as a second condition and the outstanding cheque appears.
Result: spills 3 rows × 1 column
| CHQ 1044 |
| CHQ 1045 |
| DEP-204 |
CHQ 1044, CHQ 1045 and DEP-204: two cheques written but not yet cleared, and one deposit made on the 31st that the bank hadn't credited.
No reference column? Use a running count
Number each repeat of an amount and only count it as matched while there are that many on the statement. In a helper column next to the book amounts, copied down: it's TRUE for a match, FALSE for an unmatched line. In the sheet above, row 7 is the second -125.40 in the book against one on the statement.
Result: FALSE
FALSE: CHQ 1044 is the unmatched one. More on the technique in the running count guide.
Matching on date too
Cheques clear days after you write them, so an exact date match fails. Allow a window instead: same amount, cleared on or up to 7 days after the book date. Shown for CHQ 1043, written 12 October.
Result: 1
1: it cleared on 15 October, inside the window.
Step 3: Find what's on the bank but not in your books
Now check the other direction. Skipping this is the most common miss, and it's where bank charges, interest, direct debits and card fees hide.
Result: spills 2 rows × 1 column
| BANK FEE |
| INTEREST |
BANK FEE and INTEREST: the 15.00 fee and 2.35 interest aren't in the cash book yet. Enter them in your books; they're real.
Step 4: Adjust both balances and prove they agree
The standard layout: statement balance, plus deposits in transit, minus outstanding payments, gives the adjusted bank balance. Cash book balance, plus or minus the bank-only items, gives the adjusted book balance. The two must be equal. Because payments are negative, adding every unmatched book amount handles both the deposits and the cheques in one SUMPRODUCT.
For the printed reconciliation, list the pieces separately: outstanding payments are =SUMPRODUCT((COUNTIFS(B2:B9,F2:F10,C2:C9,G2:G10)=0)*(G2:G10<0)*G2:G10), which gives -435.40 (CHQ 1044 and CHQ 1045), and deposits in transit use >0 instead, which gives 725.00.
Adjusted bank balance
Statement balance plus every book item the bank hasn't processed yet.
Result: 6921.55
6,921.55: 6,631.95 + 725.00 - 435.40.
Adjusted book balance
Cash book balance plus every bank item you haven't recorded.
Result: 6921.55
6,921.55: 6,934.20 - 15.00 + 2.35. The two agree, so October is reconciled.
The check cell
Wrap the difference in ROUND so floating-point dust (0.0000001) doesn't show as a difference. Format it red when it isn't zero.
Result: 0
0: nothing left unexplained.
When it doesn't balance
- Difference equals one item: that item is matched when it shouldn't be, usually a repeated amount (step 2) or a line entered twice.
- Difference divisible by 9: a classic sign of transposed digits, such as 860 typed as 680 (difference 180).
- Difference is double an item: a payment was entered as a deposit, or the sign is flipped in one list.
- Same reference, different amount: pull the bank amount next to each book line with XLOOKUP and subtract, rounded to 2 decimals.
- Next month, carry forward the outstanding items: they should clear on November's statement. One still open after a couple of months needs following up.
- Prefer a fill-in form to formulas? The free bank reconciliation template has the same layout. For a test-style version of this task, see the Excel test for accounting roles.
Common mistakes
Matching on amount only
Repeated amounts tick each other off. Match on reference and amount, or use a running count when there's no reference.
Checking one direction
Book-only items adjust the bank side; bank-only items adjust the book side. You need both lists.
Plugging the difference
A typed "adjustment" to make it balance hides the error until it's bigger. Find the item, or list it as unreconciled with a note.
Comparing unrounded totals
Wrap the final difference in ROUND(...,2) so a 0.00 difference really is zero.
FAQ
How do I reconcile a bank statement in Excel?
Paste the bank export next to your cash book, match lines with COUNTIFS on reference and amount, list the unmatched items from each side, and adjust both balances. Adjusted bank and adjusted book must be equal.
What formula matches bank transactions in Excel?
COUNTIFS with the reference and the amount as two conditions, wrapped in FILTER to list the unmatched lines (Excel 365 or 2021). In older versions, put =COUNTIFS(...)=0 in a helper column and filter it for TRUE.
Why doesn't my bank reconciliation balance?
Usually a repeated amount matched to the wrong line, a bank fee or interest you haven't recorded, a transposed number (difference divisible by 9) or a sign flipped. Check the difference against each of those.
What are outstanding cheques and deposits in transit?
Outstanding cheques (or payments) are in your books but haven't cleared the bank yet. Deposits in transit are deposits you recorded that the bank hasn't credited by the statement date. Both adjust the bank balance.
Functions used
FILTERKeeps matching rows, spillsCOUNTIFSCounts with several conditionsCOUNTIFCounts cells that matchSUMPRODUCTMultiply then addROUNDRounds to digitsXLOOKUPModern lookup in any direction
Related guides
- How to create a general ledger in Excel
- How to make a petty cash log in Excel
- How to make an accounts payable aging report in Excel
- How to make a trial balance in Excel
- Excel test for accounting roles: what it covers and how to pass (or set) one
- Excel formulas cheat sheet: the 25 everyone should know
Updated