Cellwise Start free

Excel guide · 5 min read

How to do a bank reconciliation in Excel

Quick answerPut the bank statement and your cash book side by side, then use COUNTIFS to match each line on reference and amount. Book items with no match are outstanding payments or deposits in transit, so they adjust the bank balance. Bank items with no match (fees, interest) adjust the book balance. When adjusted bank equals adjusted book, the account is reconciled; any difference left is an error to find.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJ
1Bank dateBank referenceBank amountBook dateBook referenceBook amountStatement balanceCash book balance
2Oct 1, 2026DEP-2011200Oct 1, 2026DEP-20112006631.956934.2
3Oct 3, 2026CHQ 1041-350Oct 2, 2026CHQ 1041-350
4Oct 6, 2026CHQ 1042-125.4Oct 5, 2026CHQ 1042-125.4
5Oct 9, 2026DEP-202860Oct 8, 2026DEP-202860
6Oct 15, 2026CHQ 1043-480Oct 12, 2026CHQ 1043-480
7Oct 20, 2026BANK FEE-15Oct 13, 2026CHQ 1044-125.4
8Oct 28, 2026DEP-203540Oct 27, 2026DEP-203540
9Oct 31, 2026INTEREST2.35Oct 30, 2026CHQ 1045-310
10Oct 31, 2026DEP-204725

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.

=FILTER(F2:F10,COUNTIF(C2:C9,G2:G10)=0)

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.

=FILTER(F2:F10,COUNTIFS(B2:B9,F2:F10,C2:C9,G2:G10)=0)

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.

=COUNTIF(G$2:G7,G7)<=COUNTIF($C$2:$C$9,G7)

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.

=COUNTIFS($C$2:$C$9,G6,$A$2:$A$9,">="&E6,$A$2:$A$9,"<="&E6+7)

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.

=FILTER(B2:B9,COUNTIFS(F2:F10,B2:B9,G2:G10,C2:C9)=0)

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.

=I2+SUMPRODUCT((COUNTIFS(B2:B9,F2:F10,C2:C9,G2:G10)=0)*G2:G10)

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.

=J2+SUMPRODUCT((COUNTIFS(F2:F10,B2:B9,G2:G10,C2:C9)=0)*C2:C9)

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.

=ROUND(I2+SUMPRODUCT((COUNTIFS(B2:B9,F2:F10,C2:C9,G2:G10)=0)*G2:G10)-(J2+SUMPRODUCT((COUNTIFS(F2:F10,B2:B9,G2:G10,C2:C9)=0)*C2:C9)),2)

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

Related guides

Updated