Bank reconciliation basics Updated November 2026

Bank reconciliation in Excel

Set up two blocks: Balance per bank with rows for deposits in transit and outstanding checks summed with SUM, and Balance per books with rows for interest, fees, NSF checks and errors. A final cell =AdjustedBank-AdjustedBook must equal 0. Keep the outstanding checks list on a second sheet and total it with SUMIF.

Syntax

Adjusted bank =B2+SUM(DIT)-SUM(OC); Adjusted book =B10+SUM(additions)-SUM(deductions); Check =B7-B15 (must be 0)

Part	What it is
`SUMIF(Cleared, "", Amount)`	Total of outstanding checks: rows where the Cleared column is blank
`=B7-B15`	Difference cell; conditional format red when not zero
`MATCH / COUNTIF`	Flag whether a book amount appears on the statement list
`ABS()`	Compare a difference against transaction amounts when hunting an error

Examples

A1 Balance per bank      B1 6,340
A2 Deposits in transit   B2 1,200
A3 Outstanding checks    B3 =-SUMIF(Checks!D:D,"",Checks!C:C)   -> -860
A4 Adjusted bank         B4 =SUM(B1:B3)

Result: B4 = 6,680

A6 Balance per books     B6 6,715
A7 Interest earned       B7 5
A8 Service charge        B8 -40
A9 Adjusted book         B9 =SUM(B6:B8)
A11 Difference           B11 =B4-B9

Result: B9 = 6,680 and B11 = 0.

Checks sheet: column C amount, column D cleared date
E2 =IF(COUNTIF(Statement!B:B,C2)>0,"on statement","outstanding")

Result: Each check is labeled on statement or outstanding by matching its amount to the statement download.

Matching on amount alone can mis-tick two checks of the same value; add the check number to be safe.

Common mistakes

Typing the adjusted balances instead of calculating them: use SUM so a changed item flows through.
Hard-coding the outstanding checks total: link it to the list so items drop off when marked cleared.
Checking for zero with =B4=B9 on rounded figures: use =ROUND(B4-B9,2)=0 to avoid floating point noise.

Practice Bank reconciliation in Excel

These drills check your answer on live data, free, no account needed.

A busy monthClassify every line on the bank statement and in the cash book, then enter the adjusted bank and book balances.
Same payee, different checksClassify every line on the bank statement and in the cash book, then enter the adjusted bank and book balances.

Questions

Is there a free bank reconciliation template?

Yes, Excel ships with reconciliation templates under File, New, and the layout above fits on one sheet. The structure matters more than the template.

Can Excel auto-match the statement to the ledger?

Partially. COUNTIF or XLOOKUP on amount plus date catches most items; the leftovers are your reconciling items. Dedicated software does the same with fuzzier matching.

Bank reconciliation formatThe standard bank reconciliation format: a bank side starting from the statement balance and a book side starting from the ledger, both ending at one figure.
Outstanding checksAn outstanding check has been written and recorded but has not cleared the bank. Subtract the total from the statement balance and carry unpaid ones forward.
Bank reconciliation in QuickBooksReconcile a bank account in QuickBooks Online: enter the statement ending balance and date, tick cleared transactions until the difference is zero, finish.