# How to Reconcile a Bank Statement in Excel, Step by Step Your register says one number, your bank statement says another. Here's the exact formula that explains the gap and gets both balances to agree. --- - **Canonical URL:** https://dothecalculation.com/blog/templates/how-to-reconcile-a-bank-statement-in-excel - **Category:** Templates - **Author:** Do The Calculation Team - **Published:** 2026-08-03 - **Reading time:** 10 min read - **Publisher:** Do The Calculation (https://dothecalculation.com) - **Methodology:** https://dothecalculation.com/methodology --- You open your bank statement, look at the ending balance, then look at your own register, and the two numbers don't match. Not wildly off — usually somewhere between a few dollars and a few hundred — but off enough that you can't just shrug and move on. It's your money. You want to know why. The good news: a mismatch between your checkbook and your bank statement almost never means something is actually wrong. It almost always means something hasn't happened yet — a check you wrote that the recipient hasn't cashed, or a deposit you made that the bank hasn't posted. Reconciliation is just the process of proving that, line by line, instead of hoping it's true. ## Why Your Register and Your Bank Statement Almost Never Match on the Same Day Your register records a transaction the moment it happens — you write the check, you make the transfer, you note it down. Your bank records the same transaction the moment it actually clears — which for a mailed check can be days or even weeks later. That gap in timing is normal, not a bug, and it's the entire reason reconciliation exists as a monthly habit rather than a once-a-year cleanup. - Outstanding checks — you wrote it and logged it, but the person or business hasn't deposited it yet. - Deposits in transit — you deposited cash or a check, but it posted to your account after the statement period closed. - Bank fees not yet recorded — a monthly maintenance fee or overdraft charge the bank applied that you haven't logged in your own register. - Interest not yet recorded — small interest payments the bank credited that never made it into your register as a line item. - Pending transfers — a transfer between accounts that shows as pending on one side and cleared on the other. ## The Two Balances You're Actually Comparing It helps to name the two numbers before you try to reconcile them. Your book balance (sometimes called your register balance) is the running total in your own records — every transaction you've logged, cleared or not. Your bank balance is whatever the bank's statement says as of its closing date — only the transactions that have actually posted. Reconciliation isn't about forcing these to be identical; it's about accounting for the difference until both sides describe the same true position. ## The Reconciliation Formula The standard method starts from the bank's number and adjusts it for anything the bank doesn't know about yet: **Adjusted bank balance** ``` Adjusted Bank Balance = Statement Ending Balance − Outstanding Checks + Deposits in Transit ``` - Outstanding checks are subtracted because they've already left your register but the bank hasn't paid them out yet — the money is still sitting in the bank's number. - Deposits in transit are added because they're already in your register but the bank hasn't posted them yet — the money is missing from the bank's number. - If your book balance also needs adjusting (an unrecorded bank fee or unrecorded interest payment), add or subtract that on the book side before comparing the two totals. Once both sides account for every timing difference, the adjusted bank balance and your book balance should land on the exact same number. If they do, you're reconciled. If they don't, the gap between them is small enough — and specific enough — to actually go find. ## A Worked Example Say your register shows a book balance of $25,027.68 at the end of the month. Your bank statement, for the same period, shows an ending balance of $25,177.68 — a $150.00 gap. Before assuming something is wrong, walk the bank's number through the formula. **Reconciling a $150.00 gap** | Line Item | Amount | Running Adjusted Balance | | --- | --- | --- | | Statement ending balance | $25,177.68 | $25,177.68 | | Less: outstanding check (dated June 10, not yet cashed) | −$275.00 | $24,902.68 | | Plus: deposit/refund in transit (dated June 29, not yet posted) | +$125.00 | $25,027.68 | | Adjusted bank balance | | $25,027.68 | | Book (register) balance | | $25,027.68 | | Difference | | $0.00 — reconciled | _[Figure: Walking the Bank Balance to the Adjusted Balance — Two outstanding items, applied in order, account for the entire $150.00 gap.]_ That's the actual value of doing this by formula instead of by eye: the $150.00 was never a mystery, it was two specific, datable transactions. Once you name them, the difference resolves to zero — and you know exactly which two items to keep an eye on until they clear. ## A Reusable 6-Step Reconciliation Checklist - Step 1: Gather your statement — Get the exact statement period (start and end date) and the ending balance the bank reports for that period. - Step 2: Mark every cleared item — Go through your register and mark each transaction that actually appears on the statement as cleared, using the same date the bank posted it. - Step 3: List what's still outstanding — Anything logged in your register but not marked cleared is outstanding: unclaimed checks, deposits in transit, pending transfers. - Step 4: Apply the formula — Take the statement ending balance, subtract outstanding checks, add deposits in transit, to get your adjusted bank balance. - Step 5: Compare — The adjusted bank balance should equal your book (register) balance. If it does, you're reconciled for the period. - Step 6: Investigate any remaining difference — If the two numbers still don't match after step 4, the gap is usually a data-entry error, a duplicate entry, or an unrecorded bank fee — not a mystery, just an unfound line. ## Common Reasons the Two Balances Don't Match - A check that hasn't been cashed yet — the single most common outstanding item, and often the oldest one on the list. - A deposit made near the end of the statement period that posted just after the cutoff date. - A bank fee (monthly maintenance, overdraft, wire fee) that was never logged in the register as its own transaction. - Interest the bank paid that was never entered as a credit. - A transaction entered twice, or entered with the wrong sign (a withdrawal typed as a deposit, or vice versa). - A transposed number — $275.00 typed as $257.00 — which is why reconciling by exact date and amount, not just by eyeballing totals, matters. ## Doing This in Excel Instead of by Hand The formula above works on paper, but it's easy to lose track of which specific items are outstanding once you're several months and dozens of transactions into an account. In a spreadsheet, the fix is to give every transaction a Cleared column (Yes/No/Review) tied to the register, then let a separate reconciliation section sum up cleared vs. outstanding amounts automatically instead of you re-adding them by hand every month. That's the exact structure behind the [Free Checkbook Register Excel Template](/excel-templates/checkbook-register-template). Its Reconciliation sheet takes your statement's ending balance and your register's cleared/uncleared status, runs the same adjusted-balance formula automatically, and breaks any outstanding amount down by transaction type in an "Uncleared Items by Transaction Type" table — so instead of a single unexplained gap, you get a short, specific list of exactly what's still open. ## Frequently Asked Questions **What if my adjusted bank balance and book balance still don't match after accounting for outstanding items?** Recheck three things first: a transaction entered twice, a transaction entered with the wrong sign (debit vs. credit), and a transposed digit in an amount. Those three cover the large majority of leftover differences once the obvious outstanding checks and deposits are accounted for. **How often should I reconcile my checkbook?** Monthly, tied to your bank statement cycle, is the standard cadence. Reconciling more often (weekly) makes each session faster since there are fewer outstanding items to track; waiting several months makes the outstanding-items list long enough that finding a real error inside it gets much harder. **Do I need special software to reconcile a bank statement?** No — the formula is simple arithmetic (statement balance, minus outstanding checks, plus deposits in transit) that works on paper, in a plain spreadsheet, or in accounting software. What software adds is automatically tracking which specific items are still outstanding so you don't have to re-list them from memory every month. **What's the difference between "cleared" and "reconciled"?** Cleared describes one transaction — has it actually posted at the bank yet. Reconciled describes the whole account for a given period — have you confirmed that your adjusted bank balance and your book balance agree after accounting for every outstanding item. A transaction can be cleared without the month being fully reconciled yet, if other items are still outstanding. **Should outstanding checks that are very old still be listed as outstanding?** Yes, until they actually clear or you confirm with your bank that they've been voided or reissued. A check outstanding for several months is worth following up on directly — it may have been lost, and many banks treat checks as stale after six months. ## Related Reading [Free Checkbook Register Excel Template](/excel-templates/checkbook-register-template) — A ready-made workbook with a running balance, a guided Reconciliation sheet, and a dashboard that all pull from the same transaction log; download it free. [How to Build a Checkbook Register in Excel (Running Balance Formula Explained)](/blog/templates/how-to-build-a-checkbook-register-in-excel) — Start here first if you don't have a running-balance register set up yet; reconciliation assumes you already have one. Once your reconciled balance is stable month to month, the [Savings Calculator](/calculators/savings-calculator) can help you plan where a consistent surplus should go next. --- _Source: [Do The Calculation](https://dothecalculation.com/blog/templates/how-to-reconcile-a-bank-statement-in-excel). Quote freely with attribution and a link to this page._