To verify an imported statement, build your own balance: opening balance plus the signed amounts so far, row by row. Put it beside the bank's balance column, subtract one from the other, and look for the first row where the gap is not zero. That row is where an amount was misread, missed or duplicated.
What do you need on the sheet?
Set up columns like this. Amounts must already be real numbers, with money out as negative and money in as positive.
- A: Date, B: Narration, C: Signed amount.
- D: Balance as printed by the bank, imported as numbers too.
- E: Your balance, built by formula.
- F: Difference between the two.
- H1: Opening balance, the balance before the first row on the statement.
No opening figure printed? Work it out from the first row: bank balance after row one, minus that row's signed amount. If row one is a salary credit of ₹40,000 and the bank balance after it is ₹65,000, the opening balance was ₹25,000.
How are the formulas written?
- E2: =$H$1+C2
- E3: =E2+C3, filled down the column.
- F2: =ROUND(E2-D2,2). ROUND removes tiny decimal noise, so the gap shows only real differences.
- Alternative for E2, filled down: =$H$1+SUM($C$2:C2). It is slower on long sheets but does not depend on the row above.
How do you find the first row where they diverge?
Scrolling hundreds of rows to spot a non-zero is tedious. Let a formula do it:
- =MATCH(TRUE,INDEX(F2:F500<>0,0),0) returns the position of the first non-zero difference. The INDEX wrapper lets it work without special array entry in older versions of Excel.
- =INDEX(B2:B500,MATCH(TRUE,INDEX(F2:F500<>0,0),0)) then shows the narration on that row.
- If there is no mismatch, MATCH returns #N/A, which here is good news. Wrap it in IFERROR(...,"All rows agree") if you like.
Worked example with hypothetical figures
| Row | Narration | Imported amount | Bank balance | My balance | Gap |
|---|---|---|---|---|---|
| 1 | Salary credit | 40,000 | 65,000 | 65,000 | 0 |
| 2 | Rent | -18,000 | 47,000 | 47,000 | 0 |
| 3 | Groceries | -2,350 | 44,650 | 44,650 | 0 |
| 4 | Electricity | -1,080 | 42,850 | 43,570 | 720 |
| 5 | UPI payment | -500 | 42,350 | 43,070 | 720 |
The gap first appears on row 4. The bank's balance fell from 44,650 to 42,850, a drop of 1,800, but the sheet recorded 1,080. Two digits were swapped in the import. Row 5 keeps the same 720 gap because the error simply carries forward. Fix row 4 to -1,800 and every later gap returns to zero.
Why might the balances differ for innocent reasons?
- Statement order: some exports list the latest entry first. Sort by date ascending, keeping same-day rows in their original order, before building the running balance.
- Same-day transactions: banks may update the balance after the last entry of the day only, so a gap that vanishes at the end of the day is not a mistake.
- Missing page: a PDF conversion may have dropped a page. The first gap will be a round figure equal to a whole row you can then find in the original.
- Duplicate row: an amount appears twice. The gap equals one transaction.
What do you do once you find the first gap?
Open the original statement at that row and compare the printed amount with your imported one. Fix the cell, and watch every gap below it fall to zero. If a later gap remains, you have a second error, so repeat the search. Note each fix in a column beside the data.
The size of the gap is a clue. A gap equal to one whole transaction suggests a missing or doubled row. A gap that is a multiple of 9, like the 720 above, often points to two swapped digits, since transposing neighbouring digits always changes a number by a multiple of 9.
Checklist
- Amounts numeric and signed.
- Opening balance confirmed from row one.
- Gap column rounded to two decimals.
- First mismatch found with MATCH, then fixed at source.
If you do this every month, SpendixAI lets you review imported rows before saving them and flags possible duplicate imports, which covers part of the same checking; keep the bank's own balance as the final reference.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

