A reconciliation checks sheet is a small tab that compares your workbook with the statement and shows PASS or FAIL for each test: row count, total debits, total credits, closing balance, balance continuity and uncategorised rows. It tells you in ten seconds whether the month's data is complete, so you never build reports on a missing row.
What does the Data sheet need to look like?
Assume a sheet called Data, with one statement per month in this layout, rows 2 to 500:
- Column A: date. Column B: narration.
- Column C: debit amount (money out). Column D: credit amount (money in).
- Column E: balance after the transaction, as printed on the statement.
- Column F: your category.
- Column G: a helper used for the continuity check, described below.
On a new sheet called Checks, use rows 2 to 8 in this order: opening balance (row 2), debit total (3), credit total (4), closing balance (5), row count (6), continuity (7), uncategorised (8). Type the numbers printed on the statement in column C, including the transaction count if your statement shows one. Column B holds the formulas below, which read your data.
Which checks should the sheet run?
| Check | Formula in the Checks sheet | Passes when |
|---|---|---|
| Debit total | =ROUND(SUM(Data!$C$2:$C$500),2) | Equals the statement's total debits |
| Credit total | =ROUND(SUM(Data!$D$2:$D$500),2) | Equals the statement's total credits |
| Closing balance | =C2+SUM(Data!$D$2:$D$500)-SUM(Data!$C$2:$C$500) | Equals the printed closing balance, with C2 as opening balance |
| Row count | =COUNTA(Data!$A$2:$A$500) | Equals the count you tallied or the one printed |
| Balance continuity | =COUNTIF(Data!$G$3:$G$500,"<>0") | Is 0, meaning no broken links |
| Uncategorised | =COUNTIFS(Data!$A$2:$A$500,"<>",Data!$F$2:$F$500,"") | Is 0 |
Beside each, in column D, put the result: for totals, =IF(ROUND(B3-C3,2)=0,"PASS","FAIL"). For the two counts that must be zero, =IF(B7=0,"PASS","FAIL"). In a summary cell, =IF(COUNTIF(D2:D8,"FAIL")=0,"ALL CLEAR","CHECK"). The ROUND matters: decimals in paise can leave tiny differences that would otherwise show a false FAIL.
How does the balance continuity check work?
Each row's balance should equal the previous balance plus the credit minus the debit. In G3, enter:
=IF(E3="",0,ROUND(E2+D3-C3-E3,2))
Fill it down to row 500. Anything other than zero marks the row where a link broke, which means the row above or below is missing, duplicated or mistyped. This check assumes the rows are in the same order as the statement, oldest first. If your export lists newest first, sort by date before checking, or reverse the sign logic.
What does a FAIL look like in practice?
Say the statement shows total debits of ₹71,350 and your Data sheet adds up to ₹69,850. The difference is ₹1,500, so one or more debits are missing or entered as credits. Filter column G for non-zero values; the first one points at the gap, and a ₹1,500 debit is probably sitting just before it. Add the row, and the checks turn to PASS. A duplicate shows the reverse: the sum is too high by exactly one amount.
Some statements do not print totals at all. In that case use the balances: the last row's balance is the closing balance. Add a check that compares it with the arithmetic result, with the closing figure from the table above in B5:
=IF(ROUND(INDEX(Data!$E$2:$E$500,COUNTA(Data!$A$2:$A$500))-B5,2)=0,"PASS","FAIL")
INDEX picks the balance on the last filled row. If a PDF was converted to a sheet, also watch for page headers repeated in the middle of the data; they show up as rows with no amount and a text date, and COUNTA will count them.
When should I run the checks?
- After pasting each month's statement, before categorising anything.
- After any manual clean-up that sorts or deletes rows.
- Before you send the sheet to a CA or use it for a yearly review.
If you are doing these checks every month across several accounts, SpendixAI covers part of this: you pick the account, review rows before saving, and possible duplicate imports are flagged. The arithmetic habit stays useful either way.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


