How to Find Bank Reconciliation Discrepancies Fast

TL;DR: Start with the size of the difference, because it often tells you the cause: a difference equal to one transaction is a missing item, half of the difference points to a sign error, and a difference divisible by nine suggests transposed digits. Then check the opening balance, timing items, duplicates and the statement data itself, in that order. Narrow by date range rather than reading every line.

A reconciliation that's off by 312.48 is one of the more annoying problems in bookkeeping. The books and the bank disagree, the amount is small enough to feel silly and large enough to matter, and the obvious approach (reading every transaction line by line) can take an hour.

There's a better way. We use a fixed sequence that finds most discrepancies in minutes, and it starts with the number itself.

Before you start: what "the difference" means

In a bank reconciliation, you compare:

• Adjusted bank balance: statement closing balance + deposits in transit − outstanding checks (and other items recorded in the books but not yet on the statement).
• Adjusted book balance: your ledger balance + items on the statement not yet in the books (interest, for example) − bank fees not yet recorded, ± corrections.

The difference is adjusted bank minus adjusted book. When it's zero, you're done. When it isn't, the sequence below helps you find out why. If you need a refresher on the full process, see our guide to reconciling a bank statement in Excel.

Step 1: read the difference

Write down the exact difference, including the sign. Then try these tests.

Does it equal a single transaction?

Search both the statement and the ledger for that exact amount. A transaction missing from one side produces a difference equal to its amount. This is the most common cause, and the fastest to check.

In Excel: =COUNTIF(Amounts, Difference) or filter by the amount. Check both positive and negative.

Is half the difference a transaction?

If you recorded a deposit as a withdrawal (or vice versa), the error is twice the amount. A 500.00 deposit recorded as a 500.00 withdrawal produces a 1,000.00 difference. Divide by two and search for that amount.

Is it divisible by 9?

Transposed digits (typing 54.30 as 45.30, or 1,280 as 1,820) always create a difference divisible by 9. If MOD(Difference*100, 9) = 0 (working in cents), look for transpositions. It doesn't prove a transposition, but it's a strong hint.

Is it a round number?

Round differences (100.00, 1,000.00) often come from a slipped digit: 1,250.00 typed as 2,250.00, or a missing zero. Look for amounts that differ from the statement by exactly that round number.

Is it tiny?

A few cents usually means a rounding difference, a currency conversion or a misread digit in the cents. Check foreign transactions and any amounts entered manually.

Step 2: check the opening balance

If the difference existed before this period, nothing you do this month will fix it. Check:

• Does your book balance at the start of the period equal the statement's opening balance (adjusted for last month's outstanding items)?
• Was last month's reconciliation actually completed, and has anything from it been edited since?

In accounting software, edited or deleted transactions in already-reconciled periods are a classic cause. Someone "tidies up" an old entry, and every reconciliation since then is off by the same amount. Many accounting tools have reports that show changes to reconciled transactions; it's worth knowing where yours is.

If the opening balance is wrong, fix the earlier period first.

Step 3: list timing items properly

Timing differences are legitimate items that are in one set of records but not yet the other:

• Outstanding checks: written and recorded, not yet cleared.
• Deposits in transit: recorded, not yet on the statement.
• Card payments or transfers initiated near the period end.

Make sure:

1. Every outstanding item from last month either cleared this month or is still outstanding (and listed).
2. Nothing listed as outstanding actually cleared on this statement.
3. Very old outstanding items are investigated. A check outstanding for months may be lost, voided or entered twice.

Step 4: look for duplicates

Duplicates in the books are common, especially when bank feeds and manual entry overlap, or when a statement was imported twice.

In Excel, add a key column to your ledger:

=TEXT([@Date],"yyyy-mm-dd")&"|"&TEXT([@Amount],"0.00")

and a count:

=COUNTIF([Key],[@Key])

Filter for counts greater than 1. Then compare each set with the statement. Remember that genuine duplicates exist (two identical subscriptions, two identical ATM withdrawals on the same day); only remove ones that appear more times in the books than on the statement.

If you work in QuickBooks, our guide to fixing duplicate transactions after import covers software-specific cleanup.

Step 5: verify the statement data itself

If you're reconciling against converted statement data (a spreadsheet made from a PDF), make sure that data is complete before blaming the books:

Opening balance + sum of statement transactions = closing balance

If that's not true, the problem is in the conversion, not the books. A missing page, a skipped line at a page break or a misread digit on a scan are the usual culprits. Our balance checker runs this check, and SheetStatement flags the exact row where the running balance diverges when you convert a statement with the bank statement to Excel converter.

Step 6: narrow down by date

If the quick tests didn't find it, don't read every line. Split the period.

1. Pick the middle date of the period.
2. Compare the book balance and the statement's balance on that date (using the statement's running or daily balance, adjusted for timing items).
3. If they agree at mid-month, the problem is in the second half. If not, it's in the first half.
4. Repeat with the half that contains the problem.

After four or five splits, you're looking at a few days of transactions. This is binary search, and it's dramatically faster than reading 300 lines.

In Excel, if both your ledger and statement data have running balances, you can do this with formulas: compute the cumulative sum for each by date and find the first date where they diverge.

=MINIFS(Dates, Diff, "<>0")

where Diff is the daily difference between the two cumulative balances.

Reconciling several accounts at once

When a business has several bank accounts, discrepancies sometimes come in pairs: one account is 500.00 high and another is 500.00 low. That's almost always a transaction posted to the wrong account in the books, often a transfer recorded against the wrong side, or a deposit entered in checking that actually landed in savings. If you reconcile all accounts for the same month together and look at the differences side by side, those pairs jump out. Reconciling each account in isolation, weeks apart, hides them.

A simple summary table helps: one row per account, columns for adjusted bank balance, adjusted book balance and difference. If the differences across accounts sum to zero, look for a misposting between them before anything else.

Step 7: match line by line (only now)

If you're still stuck, do a proper line-by-line match for the narrowed range. Use a matching key on both sides (date + amount, with an occurrence counter for duplicates) and look at the unmatched items. Our credit card reconciliation guide shows the formulas in detail; they work the same way for bank accounts.

Discrepancies that keep changing

Sometimes the difference isn't stable: it's 312.48 today and 287.10 tomorrow. That's a clue in itself. A moving difference means something in the books is changing while you work, usually because:

• A bank feed is still pulling in or matching transactions in the period.
• Someone else is entering or editing transactions in the same account.
• Automatic rules are categorizing or matching items as they arrive.
• You're comparing against a statement while the ledger report uses a different date (for example, it runs to today rather than to the statement date).

Before investigating further, freeze the inputs. Run the ledger report to the exact statement end date, pause any automatic matching if your software allows it, and ask colleagues not to touch the account for an hour. A difference that holds still can be found. One that moves can't.

When the books are right and the bank is wrong

It happens, though rarely. A bank might post a deposit to the wrong account, process a check for the wrong amount, or charge a fee that shouldn't apply. If you've gone through the sequence above and the books match your source documents (deposit slips, invoices, check images) but not the statement, contact the bank with specifics: the date, the amount, what you expected and what the statement shows. Keep your evidence together.

Don't adjust the books to match a bank error. Record it as a reconciling item ("bank error, reported on [date]") and carry it until the bank corrects it. When the correction appears on a later statement, the reconciling item clears.

Bank account agreements usually set time limits for reporting errors, which is another good reason to reconcile every month rather than once a year.

Documenting what you found

When you resolve a discrepancy, write one line about it in the reconciliation file: what the difference was, what caused it, and what you changed. "270.00: check 1047 entered as 1,250.00, actual 1,520.00; corrected." It takes seconds and does two things. It gives the next person (or an auditor) a clear trail, and over time it shows you patterns. If half your notes say "bank fee not recorded," you know to add a monthly step for fees. If they keep saying "duplicate from feed," your cutoff process needs work.

Common causes, ranked by how often we see them

1. A transaction missing from the books (often a bank fee, interest or an automatic payment).
2. A duplicate in the books from overlapping imports or manual entry.
3. A sign error: deposit entered as a payment, or the reverse.
4. A timing item listed incorrectly (cleared but still listed, or missing from the list).
5. An opening balance problem from a changed prior period.
6. A transposition or typing error in a manual entry.
7. A transaction posted to the wrong bank account in the books.
8. Incomplete statement data (missing page or line from conversion).

A worked example

Reconciliation for March is off by 270.00. Book balance is higher than the adjusted bank balance.

1. Single transaction? Search for 270.00 on both sides. Nothing.
2. Half? Search for 135.00. There's a 135.00 supplier refund on the statement, and the books show it correctly as money in. Not a sign error.
3. Divisible by 9? 27000 cents ÷ 9 = 3000. Yes. Look for transpositions.
4. We sort the books' March entries by amount and compare with the statement. A check for 1,520.00 on the statement is in the books as 1,250.00. The difference is exactly 270.00, and it's a transposition (5 and 2 swapped).
5. Correct the entry. Difference: zero.

Total time: about five minutes, mostly in step 4. Without the divisibility test, we'd have been reading lines.

Preventing discrepancies

• Reconcile monthly. Small differences are easy to find in one month and miserable to find across twelve.
• Import instead of typing. Converted statement data avoids transpositions entirely.
• Lock reconciled periods if your software supports it, or at least restrict who can edit them.
• Decide on a cutoff between bank feeds and file imports, so they never overlap.
• Record bank fees and interest promptly. They're small and easy to forget.

A quick decision tree

If you only remember one thing from this guide, make it this order:

1. Difference equals a transaction? Find the missing item.
2. Half equals a transaction? Fix the sign.
3. Divisible by nine? Look for transposed digits.
4. Opening balance right? If not, go back a month.
5. Timing items right? Update the list.
6. Duplicates? Remove the extras.
7. Statement data complete? Fix the conversion.
8. Still stuck? Split the period by date until it's small.

Our take

The difference amount is a clue, so read it before you read anything else. Most discrepancies fall to the single-transaction, half, divide-by-nine or round-number tests. If not, check the opening balance, timing items and duplicates, verify the statement data, and narrow by date. Line-by-line matching is the last resort, not the first.

More edge cases

The difference changes every time you check. Someone is editing transactions while you reconcile, or a bank feed is still bringing in new lines. Work from a fixed copy of the statement data and pause edits.

The difference is a whole statement's movement. A statement was imported twice or skipped. Compare the number of transactions in the books for the period with the statement.

The difference is in the cents. Rounding from currency conversions or a converter producing extra decimal places. Round imported amounts to two decimals.

Two errors cancel out partially. A difference of 37.00 may be one error of 100.00 and another of 63.00. When the usual tricks don't find it, match transaction by transaction instead of searching for the difference.

A mini checklist when you're stuck

1. Statement data complete and verified against its balances.
2. Opening balance agrees with the statement.
3. Last month's outstanding items checked.
4. Difference divided by 9, halved, and searched.
5. Duplicates checked by date, amount and description.
6. Line-by-line match as a last resort.

A second worked example

A difference of 186.30 wouldn't go away. It wasn't divisible by 9, no transaction matched it or its double. The bookkeeper sorted both sides by amount and compared them side by side. The books had a 93.15 payment recorded as income instead of expense: a wrong sign, which normally shows as twice the transaction. 93.15 × 2 = 186.30. She'd halved the difference earlier, but searched only the bank side, not the books. Searching both sides would have found it in a minute.

FAQ

Why won't my bank reconciliation balance?

Common causes are a missing transaction, a duplicate, a sign error, incorrectly listed outstanding items, an opening balance changed by edits to prior periods, a typing error, or incomplete statement data.

What does it mean if the difference is divisible by 9?

Transposed digits, such as 54 typed as 45, always produce a difference divisible by 9. It's a strong hint to look for a transposition, though it isn't proof.

Why should I divide the difference by two?

If a deposit was recorded as a withdrawal or the reverse, the difference is twice the transaction amount. Halving it gives you the amount to search for.

How do I find a discrepancy in a long statement?

Use binary search: compare balances at mid-period, then narrow to the half where they disagree, and repeat until only a few days remain.

Could the bank statement itself be wrong?

Bank errors are uncommon but possible. More often, a converted or retyped copy of the statement is incomplete. Verify that the opening balance plus transactions equals the closing balance before assuming the bank is wrong.

How do I stop discrepancies coming back?

Reconcile monthly, import statement data instead of retyping, restrict edits to reconciled periods, and avoid overlapping bank feeds and file imports.