How to check a converted bank statement against its balances
Updated November 18, 2026 · Ruslan Vorobev, founder of Statement to Excel
A converted statement can look perfect and still be wrong: a row lost at a page break, a withdrawal that came through as a deposit, a 3 read as an 8 on a scan. The statement itself gives you the check you need, because the bank prints the opening and closing balance.
The one equation
Opening balance + deposits − withdrawals = closing balance.
With all amounts in one column (deposits positive, withdrawals negative), that is simply: opening balance + the sum of the amounts = closing balance. If the statement prints a running balance, check it on every row too: each row's balance should equal the previous balance plus that row's amount.
Run the check in Excel
- Put the opening balance in row 1 (in the Balance column, say D1) and the transactions below it, with amounts in column C.
- Expected closing balance:
=D1+SUM(C2:C500). Compare it with the closing balance printed on the statement. They should match to the cent. - If the statement has a running balance, put
=ROUND(D1+C2-D2,2)in E2 and fill it down. Every row should show 0. The first row that doesn't is where the problem starts.
Read the difference to find the row
When the totals don't match, the size of the difference usually points to the cause:
| The difference is | Likely cause | Where to look |
|---|---|---|
| The amount of one transaction | That row is missing, or appears twice | Page breaks and repeated header rows |
| Twice the amount of one transaction | That row has the wrong sign | Refunds, reversals and transfers |
| A multiple of 9, for example 9.00 or 0.27 | Two digits swapped, for example 54.21 entered as 45.21 | Rows typed in by hand |
| A single digit in one place, for example 5.00 or 0.60 | One digit misread, such as a 3 read as an 8 | Scanned pages |
Why check before importing
Once rows are in QuickBooks or Xero, a missing transaction shows up weeks later as a bank reconciliation that won't close. Fixing it at the spreadsheet stage takes minutes.
How Statement to Excel does it
For every statement we add the transactions to the opening balance and compare the result with the closing balance, to the cent, and check the running balance row by row when the statement prints one. Pages that don't add up are re-read automatically from the page image. If a statement still doesn't reconcile, you see by how much it is off and which rows to check, before you download. Statement to Excel: the first 10 pages are free.
Have a PDF statement to convert?
Excel, QuickBooks or Xero CSV in about 30 seconds, checked against the statement's balances. The first 10 pages are free, no signup.
Convert a statement