Bank reconciliation compares your book balance against the bank's statement balance and explains every difference with reconciling items, rather than assuming the two should already match. This guide covers the two-sided reconciliation formula, a worked numeric example, how to build the summary with SUMIF so it updates automatically, matching transactions by amount and date to avoid false positives, and where people actually get stuck in practice.
Last updated 2026-08-23
Bank reconciliation compares your own accounting records against the bank's statement and explains every difference, rather than assuming the two should already match. In Excel, that means: start from your book balance, add back deposits the bank hasn't processed yet, subtract checks or payments still outstanding, then apply anything the bank recorded that your books haven't caught up to yet (fees, interest, a bounced check). If the adjusted number matches the bank's ending balance, you're reconciled. If it doesn't, the gap is real and needs tracing — it's not something a formula should paper over.
Your book balance and your bank's statement balance are almost never identical on any given day, and that's normal — not a sign something's wrong. A check you wrote last week might not have cleared yet. A deposit you made this morning might not show on the bank's side until tomorrow. Reconciliation is the process of accounting for that timing gap explicitly, so that what's left over is either zero (everything's explained) or a real discrepancy worth chasing down.
This guide walks through the formula, a worked example, a way to build the matching in Excel with SUMIF instead of eyeballing two lists, and the mistakes that make a reconciliation look balanced when it isn't.
Every bank reconciliation reduces to the same equation, regardless of the business or the software involved:
That adjusted bank balance should equal your adjusted book balance, calculated the other direction:
If Adjusted bank balance = Adjusted book balance, the reconciliation ties out. If it doesn't, the difference between the two adjusted figures is the actual unexplained amount — and that's what you investigate, not the raw, unadjusted balances.
Say your books show a checking account balance of $12,400.00 at month-end, but the bank statement shows $13,150.00. Before assuming an error, work through the reconciling items:
| Item | Amount |
|---|---|
| Bank statement balance | $13,150.00 |
| − Outstanding checks (two checks, not yet cashed) | −$900.00 |
| + Deposit in transit (made on the last business day of the month) | +$200.00 |
| = Adjusted bank balance | $12,450.00 |
| Book balance | $12,400.00 |
| − Bank service fee (not yet recorded in the books) | −$25.00 |
| + Interest earned (not yet recorded in the books) | +$75.00 |
| = Adjusted book balance | $12,450.00 |
Both adjusted figures land on $12,450.00. The $750 gap between the two raw balances wasn't an error at all — it was fully explained by two outstanding checks, one deposit in transit, a bank fee, and interest income. That's what a clean reconciliation looks like: not zero difference between the raw numbers, but a fully accounted-for difference between them.
A simple, reliable layout uses three areas on one sheet:
For the summary block, use SUM and SUMIF instead of manually adding up outstanding items, so the numbers update automatically as you check items off:
=SUMIF(BookRegister[Cleared], "No", BookRegister[Amount])=SUMIF(BookRegister[Type], "Deposit In Transit", BookRegister[Amount])
Add a Cleared column (Yes/No) to your book register and mark each transaction as you confirm it appears on the bank statement. The SUMIF formulas above then total the "No" rows automatically — the outstanding-items total in your reconciliation summary stays correct without retyping it every time you check another transaction off.
The riskiest shortcut in a spreadsheet reconciliation is matching purely on amount. Two unrelated transactions for $45.00 in the same statement period will match on amount every time, and a formula that stops there will happily mark both as reconciled even if one of them is actually the wrong transaction. Match on amount AND date proximity at minimum — a transaction dated three weeks apart from its "match" is very likely not the same transaction, even if the dollar figure lines up.
Use conditional formatting to surface anything that didn't get an automatic match: highlight rows in your bank list with no corresponding book entry, and rows in your book register still marked "No" in the Cleared column after you've gone through the statement once. Anything still highlighted after that pass is a real item to investigate — not a sign the reconciliation failed.
| Item | Where it comes from | Adjust |
|---|---|---|
| Outstanding checks | Written and recorded in your books, not yet cashed by the payee | Subtract from bank balance |
| Deposits in transit | Recorded in your books, not yet processed by the bank (common at month-end) | Add to bank balance |
| Bank service fees | Charged directly by the bank, not entered in your books until you see the statement | Subtract from book balance |
| Interest earned | Credited directly by the bank on interest-bearing accounts | Add to book balance |
| NSF / returned-item charges | A deposited check bounced; the bank reverses the credit and may add a fee | Subtract from book balance |
| Bank or book errors | Rare, but a transposed digit or a duplicate entry on either side happens | Correct at the source, then re-adjust |
The reconciliation logic above is genuinely simple once it's set up. In practice, the friction is almost always somewhere else:
If you're converting a PDF statement to get the bank side into Excel in the first place, verifying the converted statement's own math (opening balance + credits − debits = closing balance) is a separate, earlier check — it confirms the conversion didn't drop or misread a row, before you ever get to comparing it against your books.
Balance verification checks that a single statement's own numbers are internally consistent (opening balance plus credits minus debits equals the closing balance) — it catches a dropped or misread row during conversion. Bank reconciliation is a separate, later step: comparing that statement against your own accounting records to explain any difference between what your books say and what the bank says.
Timing. Checks take time to clear, deposits take time to process, and the bank applies fees and interest on its own schedule. None of that means an error occurred — it means the two records are measuring the same account at slightly different points in a process that hasn't finished yet.
Both. Amount alone produces false matches whenever two unrelated transactions happen to share a dollar figure in the same period, which is common with recurring amounts like rent or subscription charges.
That's a real discrepancy, not a formula problem. Re-check each reconciling item for accuracy first (a wrong amount on an outstanding check is a common culprit), then look for a transaction recorded on only one side — in your books but not the statement, or vice versa.
Partially. SUMIF-based totals and conditional-formatting flags for unmatched items remove most of the manual arithmetic and let you see what still needs review at a glance. The judgment calls — is this really the same transaction, is this fee expected — still need a person to confirm them.
Starting from a PDF statement? ClearlyLedger converts it to a structured Excel or CSV file with balance verification built in, so the transaction list you reconcile against is accurate from the start.
Loading interactive converter… Try ClearlyLedger free