Bank Reconciliation Example: A Worked Bank Rec Example With Real Numbers in Excel

A bank reconciliation example is easier to follow with real numbers than with a definition, so this one uses a full month of activity for a small US agency and works both sides to the penny. The statement balance and the book balance start $1,880.00 apart, and by the end of the page you will see exactly which items explain the gap, which side of the reconciliation each one belongs on, and which of them need a journal entry.

The short version, if you only need the mechanics: you adjust the bank balance for items the bank has not seen yet, you adjust the book balance for items you have not recorded yet, and the two adjusted figures must land on the same number. Only the book side generates journal entries. That last point is where most people get tripped up.

What is a bank reconciliation example?

A bank reconciliation example is a worked comparison of a bank statement balance against a general ledger cash balance for the same date, showing every difference between them. The bank side is adjusted for deposits in transit and outstanding checks. The book side is adjusted for bank fees, interest, returned checks, and your own posting errors. When both adjusted balances match, the account is reconciled.

The bank reconciliation formula

Two formulas, one for each side. Run them independently and then compare the answers:

• Adjusted bank balance = bank statement balance + deposits in transit - outstanding checks
• Adjusted book balance = book cash balance + bank credits you have not recorded - bank debits you have not recorded +/- your own errors

If those two results are equal, you are done. If they are not, the difference is either an item you have missed or an item you have put on the wrong side, and there is a short list of usual suspects further down.

The scenario

Meridian Design Co. is a six person agency in Ohio with one operating checking account. At June 30, 2026 the bank statement shows a closing balance of $48,312.75. The general ledger cash account shows $46,432.75. That is a $1,880.00 gap, and none of it is theft or error in the alarming sense. It is four ordinary categories of difference.

Bank reconciliation example, the bank side

Start with what the bank says and adjust for the transactions the bank has not processed yet. Two client payments were deposited on the last two business days of June and did not clear until July 1, so they are deposits in transit. Four checks written in June had not been cashed by June 30, so they are outstanding checks.

The adjusted bank balance is $45,492.15. Note that nothing here is an error. Every one of these six items is a timing difference that will resolve itself in July without anybody doing anything.

Bank reconciliation example, the book side

Now start with what the ledger says and adjust for everything the bank did that has not been recorded, plus any mistakes in the books themselves. Meridian has four such items in June: interest the bank paid, a monthly service charge, a client check that bounced, and a transposition error on the rent check.

The adjusted book balance is $45,492.15, the same figure the bank side produced. The account reconciles. If you are following along in a spreadsheet, that matching pair of numbers is the only proof you need, and it is worth locking both cells so a later edit cannot quietly break the tie.

The transposition error, in detail

Check #1420 paid June rent of $2,600.00 and cleared the bank at that amount. Whoever posted it typed $2,960.00 into the ledger, a classic digit swap. Because the ledger recorded $360.00 more cash going out than actually left, book cash is understated by $360.00, so the reconciliation adds it back.

There is a useful shortcut buried in that number. A transposition error always produces a difference divisible by nine. $360.00 divided by nine is $40.00, cleanly. If your reconciliation is out by an amount divisible by nine, look for swapped digits before you look for anything else.

Which items need a journal entry?

Only book side items. Deposits in transit and outstanding checks never get an entry, because you already recorded those transactions when they happened. The bank is simply behind you. This table is the part worth keeping.

So Meridian posts four adjusting entries at June 30, and nothing at all for the deposits in transit or the outstanding checks.

Cash moves by a net $940.60 down, from $46,432.75 to $45,492.15, which is precisely the adjusted balance both sides of the reconciliation agreed on. Post the entries, and the ledger now matches the reconciliation instead of merely being explained by it.

What if the bank reconciliation does not balance?

Work through the differences in order of likelihood rather than searching at random. Most unbalanced reconciliations come down to one of six things, and the size of the gap usually tells you which.

• Difference divisible by nine. Almost always transposed digits, as with the $360.00 above.
• Difference exactly equal to a transaction amount. An item was missed entirely, or entered on the wrong side of the reconciliation.
• Difference exactly double a transaction amount. The sign is wrong. Something was added that should have been subtracted.
• Difference equal to a small odd amount. Look for fees. Wire fees, returned item fees, analysis charges, and card processing charges are the items people forget to record.
• The opening balance is already wrong. If last month tied and this month does not, but no current item explains it, the prior period was closed with an error or a transaction was edited after the fact. In QuickBooks this shows up as a beginning balance discrepancy.
• A duplicate. A deposit entered twice, or a bank feed that imported a transaction you had already keyed manually.

One practical habit prevents most of it: reconcile monthly, on the same day each month, and never let two unreconciled periods stack up. Finding one month of differences takes twenty minutes. Finding six months of them takes a weekend, and the arithmetic gets ambiguous because you can no longer tell which period an item belongs to. Bank reconciliation is the item that gates everything else in a month end close checklist, which is why it is usually first on the list.

Bank reconciliation example in Excel

The layout above translates to a spreadsheet almost directly. Build two blocks on one sheet, bank on the left and book on the right, each with its starting balance at the top, its adjustments below, and a subtotal at the bottom. Then add a single check cell that subtracts one subtotal from the other and formats to zero. That cell is your entire reconciliation status.

Three details make the difference between a sheet you rebuild every month and one you reuse. Keep the adjustment rows as a proper table so new items extend the subtotal automatically. Date every outstanding check and deposit in transit, so next month you can see at a glance which ones cleared. And keep a column for the resolution of each item, because in six months you will not remember why a $325.00 check sat outstanding for two periods. Our bank reconciliation template for Excel is laid out this way, and the walkthrough on how to do a bank reconciliation in Excel covers the formulas for the matching step.

The genuinely slow part is not the arithmetic, it is getting the statement into rows you can work with. If your bank only gives you a PDF, or the download window is too short to cover the period you are reconciling, a bank statement converter turns the PDF into dated rows with debits, credits, and the running balance intact, which is what makes the line by line comparison possible at all. From there you can reconcile bank statements against the ledger in the same sheet.

How many months should you reconcile at once?

One. Reconcile each month separately and in order, closing each period before you open the next. Combining months makes it impossible to tell whether an item is a timing difference or an error, because a check outstanding at the end of June that cleared in July is normal, while the same check outstanding across a combined June to July period looks like a missing transaction.

Catch up work is the exception that proves the rule. When a client arrives with nine unreconciled months, the only workable approach is still month by month, oldest first, carrying each period's outstanding items forward. It is slower than it sounds and it is why catch up bookkeeping costs what it does.

When a spreadsheet stops being the right tool

A two column reconciliation like the one above works well for one or two accounts. It starts to strain at around a dozen, and it stops working entirely once you are matching card processor payouts and merchant deposits against bank credits, because the matching is many to one rather than one to one. At that point teams move to software that reconciles the accounts continuously instead of monthly, and matches at the transaction level across bank feeds and processors rather than at the balance level. There is also a middle path worth knowing about: purpose built bank reconciliation software that keeps the monthly rhythm but does the matching for you.

Whichever route you take, the reconciliation logic does not change. Something the bank has not seen, something you have not recorded, or something one of you got wrong. Every difference you will ever find is one of those three.