To find an unexplained difference in a reconciliation, do not scan every line. Shrink the problem first: compare totals by month, then by week, then by day until the difference sits in a small group of rows. Then test that group against a short list of usual suspects, such as a missing entry, swapped digits, an entry posted on the wrong side, or a duplicate.
Step 1: Is the opening balance right?
Before anything else, confirm that both sides start from the same figure. A difference that appears in the first month and never changes usually comes from an opening balance, not from any transaction. If the closing balance of the last reconciled period does not match this period's opening balance, stop and fix that first.
Step 2: Which month holds the difference?
Build a small table with one row per month and compare the two sources. With the first of each month in A2, bank amounts on a tab called Bank (date in A, amount in B) and your records on a tab called Book:
=SUMIFS(Bank!$B$2:$B$1000, Bank!$A$2:$A$1000, ">="&A2, Bank!$A$2:$A$1000, "<"&EDATE(A2,1)) - SUMIFS(Book!$B$2:$B$1000, Book!$A$2:$A$1000, ">="&A2, Book!$A$2:$A$1000, "<"&EDATE(A2,1))
Fill it down. For a made-up year, you might see 0 for April, May and July, and 4,500 for June. The difference lives in June. Repeat the same idea with the first day of each week, then each day, until the gap sits in a handful of rows.
Step 3: What kind of mistake is it?
Take the difference and run it through these tests:
| Test | What to look for | Typical cause |
|---|---|---|
| Exact amount | A single entry equal to the difference | One missing or extra entry |
| Half amount | An entry equal to half the difference | Posted as a credit instead of a debit, or the reverse |
| Divisible by 9 | Difference divides evenly by 9 | Two adjacent digits swapped, such as 540 typed as 450 |
| Small and round | Under ₹100 or a few paise | Rounding, a bank charge, interest credit |
| Repeats a known item | Same amount and date appears twice | Duplicate entry or double import |
The 9 test works because swapping two adjacent digits changes a number by a multiple of 9. For example, 540 minus 450 is 90, which is 9 times 10. To test a difference in D2: =MOD(ROUND(ABS(D2)*100,0),9)=0 returns TRUE when it passes. One difference in nine passes by coincidence, so treat TRUE as a hint, not a verdict.
How do you spot a half-amount error?
If the difference is ₹3,600, look for an entry of ₹1,800. A ₹1,800 receipt typed as a payment (or the reverse) moves the total by twice the amount, ₹3,600, so the culprit is half of the gap. In a sheet, filter your amount column for 1,800 or use =COUNTIFS($B$2:$B$1000, ABS(D2)/2) to count candidates.
How do you find a duplicate?
In a helper column next to your records:
=COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2, $C$2:$C$1000, C2)
Anything above 1 is a repeated date, amount and description. Not every repeat is wrong: two chai-stall UPI payments of ₹40 on the same day are real. But a ₹32,500 payment repeated on one date deserves a second look.
When should you ask for help?
- The difference is large, say a meaningful share of the period's total, and the narrowing steps above do not isolate it.
- It involves GST, tax deducted at source or other statutory items, where confirming with your CA is safer than guessing.
- You suspect a bank error or an unrecognised debit. Contact the bank with the exact date and amount.
Write down what you tested and ruled out as you go. When the answer finally appears, you will have a short log that makes the next reconciliation faster.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


