To reconcile, paste the bank statement into a second tab, build a matching key from date and amount on both tabs, and let COUNTIF mark each row as matched or missing. Then confirm that opening balance plus credits minus debits equals the closing balance printed on the statement. The unmatched rows are your list of forgotten entries or wrong amounts.
How do you set up the two tabs?
Keep your expense sheet as it is, with Date in A, Description in B and Amount in C. Add a tab called Stmt and paste one month of statement rows there with Date in A, Narration in B and the debit amount in C. Use your own statement's layout and convert credits to a separate column rather than negative numbers, so you can sum each side.
Cash expenses will never appear on the statement. Add a Payment mode column to your own sheet and leave the cash rows out of the match.
How do you match rows with a key?
In column E of both tabs, create the key: =TEXT(A2,"yyyy-mm-dd")&"|"&C2. Each row now looks like 2026-04-06|1860. On your sheet, put in F2:
- =IF(COUNTIF(Stmt!$E$2:$E$500,E2)>0,"Matched","Not on statement")
- On the Stmt tab, put in F2: =IF(COUNTIF(Sheet1!$E$2:$E$500,E2)>0,"Matched","Not in sheet")
Replace Sheet1 with your expense tab's actual name. If the tab name has spaces, wrap it in single quotes. Filter column F on each tab for anything that is not Matched.
What do the unmatched rows usually mean?
- Not in sheet: a purchase you forgot to log, often a small UPI payment, or an automatic debit such as a subscription.
- Not on statement: a cash purchase, a card payment that has not been billed yet, or an amount you mistyped.
- A date off by a day: UPI and card payments sometimes post a day later. Match on amount and a two-day window instead: =COUNTIFS(Stmt!$C$2:$C$500,C2,Stmt!$A$2:$A$500,">="&A2-2,Stmt!$A$2:$A$500,"<="&A2+2).
- The same amount twice: COUNTIF cannot tell that one row matched twice, so look at repeated amounts by eye.
To pull the list of missing items into one place in Excel 365 or Google Sheets, use =FILTER(Stmt!$A$2:$C$500,Stmt!$F$2:$F$500="Not in sheet"). Older Excel versions can filter column F instead.
How do you check the closing balance?
Row matching catches missing entries but not an error you copied from the statement. A balance check catches both. Say the statement shows an opening balance of ₹42,000, total credits of ₹60,000 and total debits of ₹51,500. Then 42,000 + 60,000 - 51,500 = ₹50,500, which should equal the statement's closing balance.
| Line | Amount |
|---|---|
| Opening balance | ₹42,000 |
| Add: credits | ₹60,000 |
| Less: debits | ₹51,500 |
| Worked closing balance | ₹50,500 |
| Closing balance on statement | Enter and compare |
If the two differ by, say, ₹900, search the statement for a ₹900 row you did not log. A difference that is divisible by 9 often points to two digits swapped when typing, such as 540 entered as 450.
Credit card statements reconcile the same way, with one change: your sheet's card purchases should add up to the statement's total of new charges, and the bill payment you made last month appears on the bank statement, not the card one.
Can this step be skipped altogether?
Reconciliation exists because the sheet and the bank are two separate records. If your tracker imports the statement itself, the bank record becomes your record and the matching step mostly disappears. SpendixAI takes a statement as PDF, CSV or Excel, asks you to pick the account first and shows the rows for review before saving, so what you check is each row, not whether a row is missing. Cash still needs manual entry.
A quick monthly routine
- Paste the month's statement into Stmt.
- Build the keys and mark matches.
- Fix or add the unmatched rows.
- Compare the closing balance and note the date you reconciled.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


