Guide5 min read
How to check that a bank statement adds up
When you move a statement from PDF into Excel, one misread row is enough to make the totals wrong. A running balance check finds it in seconds. Here is the rule, the Excel formulas, and what usually goes wrong.
The rule
For a bank account: previous balance + money in − money out = new balance. It holds for every row that shows a balance, and for the whole statement: opening balance + all money in − all money out = closing balance.
On a credit card the balance is what you owe, so the signs swap: previous balance + purchases − payments and refunds = new balance.
Check every row in Excel
Lay the transactions out like this, with the opening balance in E1:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Opening balance | 2,450.00 | ||||
| 2 | 01 Oct | Card payment | 54.20 | 2,395.80 | OK | |
| 3 | 01 Oct | Salary | 3,200.00 | 5,595.80 | OK |
Type this into F2 and fill it down:
=IF(ABS(E1+D2-C2-E2)<0.005,"OK","Check")The ABS part allows for rounding to the cent. For the whole statement, compare =E1+SUM(D2:D500)-SUM(C2:C500) with the closing balance printed by the bank.
Why a row doesn't add up
- A misread amount: a digit lost or two digits swapped. The flagged row's amount or balance differs from the PDF.
- A missing row, often at a page break. The next balance is off by exactly that amount.
- A row counted twice, such as a “balance brought forward” line taken as a transaction.
- The wrong column: a refund in money out instead of money in. The difference is twice the amount.
- One balance for several transactions: some banks print the balance once a day. Check at the next printed balance instead of every row.
- Pending transactions listed without a balance, or added after the statement date.
How Tidysheet checks it for you
The bank statement converter runs this check on every printed balance. Rows without a balance are checked together with the next one. If a single printed balance is off but the next one follows from the correct figure, only that one row is highlighted. When a statement has no balance column, the totals are compared with the totals the bank printed. The Excel file keeps the check as a live formula, so fixing a cell turns the row back to OK.