If your tracker disagrees with your bank, build a running balance beside your transactions and compare it with the statement line by line. The first row where the two diverge is the row to investigate. A short formula in Excel or Google Sheets is enough to do it.
Set up the sheet
Use columns A to D: date, description, amount and balance. Record money in as a positive number and money out as a negative number. Put the opening balance from the statement in cell G1, and label it clearly so you know which date it belongs to.
- D2: =$G$1+C2
- D3: =D2+C3 and copy it down to the last row
- G2: =SUM($C$2:$C$500)+$G$1 for the balance the sheet expects
- G3: the closing balance printed on the bank statement, typed in by hand
- G4: =G3-G2 for the difference
Keep the ranges tight, as in $C$2:$C$500, so the sum ignores stray notes or totals further down the sheet.
A worked example in Australian dollars
Say the statement shows an opening balance of A$3,150.00 and a closing balance of A$3,401.55. Your sheet lists money in of A$4,200.00 and money out of A$3,968.45. The expected closing balance is 3,150.00 + 4,200.00 - 3,968.45 = A$3,381.55.
G4 shows 3,401.55 minus 3,381.55, which is A$20.00. The sheet says the bank has A$20.00 more than your records explain, so a money-in of A$20.00 is missing, or a money-out of A$20.00 was entered that the bank never took. Look at the statement for a A$20.00 row, perhaps a small refund or a cash deposit, and compare.
How do I find the divergent row in a long list?
Add a column E and type the bank's running balance from the statement beside each row, when your statement shows one. Then use F2: =D2-E2 and copy down. The first row where F is not zero is where your records and the bank part ways. Everything above it agrees.
| Date | Description | Amount | Your balance | Bank balance | Difference |
|---|---|---|---|---|---|
| 03 May | Supermarket | -86.40 | 3,063.60 | 3,063.60 | 0.00 |
| 04 May | Pharmacy | -23.00 | 3,040.60 | 3,040.60 | 0.00 |
| 05 May | Cash deposit | 0.00 | 3,040.60 | 3,060.60 | -20.00 |
Here the cash deposit of A$20.00 was never entered, so the amount cell shows zero and the difference jumps to 20.00 on that row. Fix that row and the rest of the column falls into line.
What else can cause a difference?
- A duplicate row, which shifts every balance below it by the same amount
- A row entered with the wrong sign, which shifts the balance by twice the amount
- A transaction dated in one month in your sheet and the next month on the statement
- A transfer between your own accounts recorded on only one side
When a sheet stops being enough
A running balance works well for one account and a few hundred rows. With several accounts, repeated imports and categories to maintain, many people prefer a tracker that keeps an opening balance per account. SpendixAI is built around India and INR, so check whether any tool you consider supports your currency and statement formats before moving anything over.
If your list is long, you can speed up the hunt by sorting a copy of the sheet by amount and comparing the largest figures first. Large amounts are the rows most likely to have been entered wrongly, and a single wrong digit in a big payment can explain a difference that looks mysterious.
Remember that the sheet only proves your records agree with the statement; it cannot tell you the statement itself is complete. If the bank file you started from omitted a day, the running balance will still show a difference, and only the printed balances will reveal where the file began.
Next steps
- Reconcile the most recent month first, since the statement is fresh.
- Work backwards one month at a time, matching each opening balance to the previous closing balance.
- Save a copy of the sheet before editing, so you can undo a mistake.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
