How to do a bank reconciliation in Excel (step by step, with formulas)

Bank reconciliation answers one question: does what your books say match what the bank says, and if not, exactly why? Done monthly in a spreadsheet it takes twenty minutes. Left until year-end it becomes an afternoon of hunting a €40 difference through six hundred rows. This is the twenty-minute version, with every formula included.

What reconciliation actually proves

Your ledger and your bank statement will almost never show the same balance on the same day, and that's normal. A cheque you wrote hasn't cleared; a customer payment landed after the statement cut-off; the bank charged a fee you haven't recorded. Reconciliation doesn't force the two numbers to be equal — it explains the gap item by item until nothing is left unaccounted for.

The formal statement of it:

Adjusted bank balance = closing balance per statement
  + deposits in transit − outstanding payments

Adjusted book balance = ledger balance
  + items the bank recorded that you haven't (interest)
  − bank charges, returned payments

When those two adjusted figures agree, you're reconciled.

Step 1 — Get both sides into the same shape

Two sheets: Bank and Books, both with the same columns — Date, Description, Amount — and amounts in one signed column (negative for money out) on both sides. Mixed conventions between the two sheets is the single most common reason a reconciliation "doesn't work."

Getting the bank side in is usually the tedious part. If your bank only provides PDFs, convert the statement rather than retyping — 200 rows retyped by hand introduces exactly the kind of transposition error you're about to spend an hour hunting. (Every Statement Mill export is checked against the statement's own opening and closing balances first, so you start from data that's already proven complete — the CSV guide covers getting it into the right column layout.)

Step 2 — The matching formula

On the Bank sheet, add a Matched column. This counts how many rows in your books share the same date and amount:

=COUNTIFS(Books!$A:$A, $A2, Books!$C:$C, $C2)

Where column A is Date and C is Amount on both sheets. Read the result as:

• 1 — clean match, nothing to do
• 0 — on the bank statement but not in your books: an unrecorded charge, a payment you forgot, or a deposit in transit
• 2 or more — duplicates on the books side. Genuine repeat transactions are common, so don't assume error, but check each pair by hand

Then mirror it on the Books sheet to find items in your ledger that never reached the bank:

=COUNTIFS(Bank!$A:$A, $A2, Bank!$C:$C, $C2)

Allowing for date drift. Card payments often post a day or two after you record them, which breaks an exact-date match. Widen the window to ±3 days:

=COUNTIFS(Books!$A:$A, ">=" & $A2-3,
          Books!$A:$A, "<=" & $A2+3,
          Books!$C:$C, $C2)

Start strict, then loosen only for the rows that didn't match — a wide window from the start creates false matches between similar amounts, which is worse than an unmatched row you can see.

Step 3 — Work the unmatched items

Filter both sheets to Matched = 0. Every remaining row falls into one of four buckets, and naming the bucket tells you what to do:

A quick sanity rule: a timing item from last month that still hasn't cleared this month is no longer a timing item. It's one of the other three.

Step 4 — The reconciliation summary

A small block on a third sheet:

Column E is the Matched column, so E = 0 selects unmatched rows. Both outstanding formulas add signed amounts, so subtraction is already handled by the signs — don't negate again. Wrap the difference in ROUND(…, 2): floating-point arithmetic produces differences like 0.0000000001 that look alarming and mean nothing.

Step 5 — When it still won't balance

The difference itself usually tells you what happened. Four diagnostics, in the order worth trying:

• Divides evenly by 9? Almost certainly a transposition — 54 written as 45, 190 as 910. Classic and instant to confirm.
• Exactly equal to a transaction amount? That item is on one side only, or recorded twice on one side. Search the amount on both sheets.
• Exactly twice a transaction amount? A sign error: something entered as +50 that should be −50 moves the total by 100.
• Under a euro or two? Usually an unrecorded bank charge, a rounding difference on a currency conversion, or a card fee.

And the check that comes before all of them: is the bank side even complete? Confirm that your imported statement rows satisfy the statement's own arithmetic:

=ROUND(<opening balance> + SUM(Bank!$C:$C) - <closing balance>, 2)

That must be zero. If it isn't, a transaction was dropped or misread during data entry, and every reconciling item you've been chasing is downstream of a broken source. This is why Statement Mill runs that exact check on every conversion before you download — it removes one entire category of reconciliation mystery.

Step 6 — Make next month take five minutes

Keep the workbook. Next month, paste the new statement under the existing bank rows, the new ledger entries under books, drag the matching formulas down, and carry forward last month's unmatched items to confirm they cleared. The setup cost is paid once.

Two habits make it stay easy: reconcile monthly, when the statement arrives, so unmatched items stay in single digits; and check that each statement's opening balance equals last month's closing — the balance chain — which catches a missing statement before it becomes a missing month in your books.

Convert this month's statement free — 3 pages without signing up, 20 with an account, no credit card and no trial timer — and start from data that's already proven complete.