How to Reconcile Your Business Bank Account With Your Bookkeeping Spreadsheet

Reconciling sounds like an accountant’s chore, but it’s actually the single step that makes your books trustworthy — and it’s simpler than the word suggests. All you’re doing is asking one question: does my record of what happened match what the bank says happened? When the answer is yes, you can trust every number your spreadsheet produces. When it’s no, you’ve just caught a mistake before it became a problem. Here’s exactly how to do it each month.

What reconciling actually is

Your bookkeeping spreadsheet is your record of transactions. The bank statement is the bank’s record. Most of the time they agree — but not always, and not because anyone did anything wrong. A check you wrote hasn’t been cashed yet. A payment you deposited on the 31st doesn’t post until the 1st. A monthly account fee hit that you forgot to log. Reconciling is the process of matching the two records, ticking off everything that appears in both, and accounting for the handful that appear in only one — until you can prove the two agree.

The payoff: a reconciled account means your profit number, your expense totals, and your tax summary are all built on transactions that actually happened. Skip it, and you’re trusting numbers you never checked.

The reconciliation logic in one line

Everything below is just this:

Bank ending balance + deposits not yet cleared − payments not yet cleared = your book balance

If that equation holds, you’re reconciled. If it doesn’t, the difference points you to what’s missing.

Step-by-step

1. Get the statement. Download the business bank statement for the month. Note its ending balance and its date.
2. Line up your records. Open your bookkeeping for the same period.
3. Tick off the matches. Go through the statement and mark each transaction that also appears in your records. Most will match exactly.
4. Find the singles. Anything on the statement but not in your books, or in your books but not on the statement, gets set aside as an exception.
5. Sort the exceptions:
6. On the statement but not in your books → you forgot to record it (a fee, interest, an auto-charge). Add it now.
7. In your books but not on the statement → it hasn’t cleared yet (an outstanding check or a deposit in transit). Leave it recorded but mark it outstanding.
8. Do the math. Apply the one-line equation. Take the bank’s ending balance, add deposits that haven’t cleared, subtract payments that haven’t cleared, and check it equals your book balance.
9. If it balances, you’re done. If it doesn’t, the gap is your clue — hunt the transaction(s) that add up to the difference.

A worked example that balances

Marco runs a small landscaping supply shop. Reconciling March:

• Bank statement ending balance: $9,250
• His book balance (what his spreadsheet says he has): $9,400

They don’t match — a $150 gap. Time to reconcile.

Ticking through, he finds two exceptions:

First he works out what the bank side says his balance should be:

Bank $9,250 + deposit in transit $380 − outstanding check $220 = $9,410

So his books should show $9,410. They show $9,400 — a $10 gap. He records the $10 bank fee he’d forgotten, which brings his books to $9,390. Now he’s $20 the other way, so he keeps looking and finds he entered a $20 fuel charge twice. Deleting the duplicate removes a $20 expense and lifts his book balance to $9,410 — exactly matching the bank side.

Bank side $9,410 = adjusted book balance $9,410. ✓

Reconciled. In the process he caught a missed fee and a duplicate entry — two errors that would have quietly thrown off his profit for the month.

The usual reasons it won’t balance

When your account won’t reconcile, the difference almost always traces to one of these:

• A transaction hasn’t cleared — recorded by you, not yet by the bank (or the reverse). Normal; mark it outstanding.
• A duplicate entry — the same transaction logged twice. The gap equals the transaction amount.
• A typo — $45 entered as $54. The gap is often a “round” or telltale number.
• An unrecorded bank item — a fee, interest, or an auto-charge you never logged.
• A transfer miscounted — money moved between your own accounts recorded as income or an expense. It’s neither.

A useful trick: if the difference is evenly divisible by 9, you likely transposed two digits somewhere (45 vs 54). If it matches a specific transaction’s amount, you probably double-entered or missed that exact item.

Make it a two-minute monthly habit

Reconciling is only painful when you do it for a whole neglected year at once. Done monthly, right after the statement closes, it’s a couple of minutes and it keeps every other number in your books honest.

Our Small Business Bookkeeping Spreadsheet has a dedicated Reconciliation tab that lines your recorded transactions up against your statement, tracks outstanding and in-transit items, and shows whether you balance — plus an Income Tracker, Expense Tracker, Cash Flow tab, and Dashboard so the reconciled numbers flow straight into your profit and tax summary. 836+ formulas do the arithmetic; you just tick and check.

Reconciliation is step five of the full monthly routine — see the monthly bookkeeping checklist for where it fits with everything else.

Featured on ReadySheetGo: Small Business Bookkeeping Spreadsheet Template — 9 tabs including a dedicated Reconciliation tab, 836+ auto-calculating formulas, 25 IRS Schedule C categories, and a tax-ready summary. Works in Microsoft Excel and Google Sheets. $17.99 (currently $10.79 with the LAUNCH40 sale). Instant digital download.