Most people check a converted or typed-up statement the same way: opening balance plus deposits minus withdrawals, compared with the closing balance. If it agrees, the data is called correct. It is a good check, and it is not enough. Two errors that cancel each other pass it every time, and even when it fails it only tells you that something is wrong, not where. The bank statement running balance, the balance printed on each line, tests every row against a number the bank wrote down independently. This guide shows, with a made-up statement that adds up, exactly what that catches and how to check it yourself in Excel.
The short answer: why the bank statement running balance beats a totals check
A totals check compares sums, so any two errors whose amounts cancel leave it intact: a dropped $64.00 withdrawal and a duplicated $64.00 withdrawal, or a deposit and a withdrawal of the same size with both signs flipped. A running balance check compares each row with the balance printed on the statement: previous balance plus money in, minus money out, must equal this row’s balance. An error breaks that equation at the row where it happens, so the check finds the row, not only the fact.
| Error in the data | Row count | Credit and debit totals | Opening + net = closing | Running balance, row by row |
|---|---|---|---|---|
| One row missing | Yes | Yes | Yes | Yes, at the row after the gap |
| One row duplicated | Yes | Yes | Yes | Yes, at the duplicate |
| A row missing and a row of the same amount duplicated | No | No | No | Yes, at both rows |
| One sign flipped | No | Yes | Yes | Yes, at that row |
| Two equal amounts, opposite directions, both signs flipped | No | No | No | Yes, at both rows |
| Two digits transposed in one amount | No | Yes | Yes | Yes, at that row |
| Wrong date or wrong description | No | No | No | No |
The last row is the honest limit. No arithmetic check can tell you that a payment dated the 12th should say the 21st, or that the payee is wrong. Arithmetic proves the amounts; reading the flagged rows, and spot-checking the rest against the PDF, covers what arithmetic cannot.
Doing this by hand works for one statement. A conversion that tests each row against the balance printed above it runs the same check on every statement, and puts the failing row next to its line on the PDF.
What a running balance on a bank statement is
A running balance is the account balance immediately after each transaction, printed on the same line. On a checking or savings statement it usually sits in a column headed Balance, at the right. The first line follows the opening (or beginning) balance, and the last line equals the closing (or ending) balance. Each line is the line above plus money in, minus money out.
Not every statement has one. Some banks print a balance only on the last transaction of each day, some print a separate daily balance table at the end of the statement, and credit card statements usually print no running balance at all, only the summary box. The method below works with each of these, with less precision as the balances get further apart.
Running balance versus available balance
The balance online banking shows today is often the available balance, which subtracts pending items and holds. The running balance on a statement is the ledger balance after each posted transaction. They are different numbers for good reasons, so check a statement only against its own printed balances.
A worked example: the statement
Here is a made-up September statement for a small business checking account. It has ten transactions over two pages, and every figure adds up. In the statement’s own words, money out is a debit and money in is a credit.
| Page | Date | Description | Credits (in) | Debits (out) | Balance |
|---|---|---|---|---|---|
| 1 | 09/02 | Deposit, Harbor Lane Catering | 1,850.00 | 6,060.55 | |
| 1 | 09/03 | Check 1042 | 312.40 | 5,748.15 | |
| 1 | 09/05 | Riverside Office Supply | 86.25 | 5,661.90 | |
| 1 | 09/08 | Elm Street Garage, parking | 64.00 | 5,597.90 | |
| 2 | 09/10 | Deposit, Sam Rivera, invoice 1188 | 725.00 | 6,322.90 | |
| 2 | 09/12 | City Power & Light | 148.37 | 6,174.53 | |
| 2 | 09/15 | Transfer to payroll | 2,100.00 | 4,074.53 | |
| 2 | 09/22 | Elm Street Garage, parking | 64.00 | 4,010.53 | |
| 2 | 09/26 | Deposit, Jane Doe Design | 980.00 | 4,990.53 | |
| 2 | 09/30 | Monthly service fee | 15.00 | 4,975.53 |
The summary box agrees: 10 transactions, total credits $3,555.00, total debits $2,790.02, and $4,210.55 + $3,555.00 − $2,790.02 = $4,975.53, the closing balance.
The converted copy passes every totals check
Now suppose the statement was converted to a spreadsheet, by hand, by copy and paste, or by software, and two things went wrong. The last row of page 1 (09/08, $64.00) was read again at the top of page 2, a classic page-break duplicate. And the 09/22 parking charge, also $64.00, was lost, say because its line ran into the row above. The spreadsheet still has ten rows.
| Check | Statement says | Converted data gives | Result |
|---|---|---|---|
| Transaction count | 10 | 10 | Pass |
| Total credits | $3,555.00 | $3,555.00 | Pass |
| Total debits | $2,790.02 | $2,790.02 | Pass |
| Opening + credits − debits | $4,975.53 | $4,975.53 | Pass |
Every check passes, and the data is wrong. Imported into QuickBooks, it would even reconcile to a $0.00 difference if every imported row were ticked, because the ending balance agrees. The September 8 parking charge is in the books twice and the September 22 charge not at all. In this example the net effect is zero; with a different pair of amounts, or a pair that lands either side of a month end, it is not.
How likely is this?
Rarer than a single dropped row, but not rare enough to ignore. Recurring payments of the same amount (parking, subscriptions, rent, loan payments, transfers between your own accounts) are exactly the rows that repeat on a statement, and page breaks are exactly where rows are lost and duplicated. A busy account has both.
The running balance breaks at two specific rows
Check each converted row against the balance printed on the row above it: expected balance = previous printed balance + money in − money out. Difference = printed balance − expected balance. A correct row gives a difference of zero.
| Date | Description | In | Out | Printed balance | Expected | Difference |
|---|---|---|---|---|---|---|
| 09/02 | Deposit, Harbor Lane Catering | 1,850.00 | 6,060.55 | 6,060.55 | 0.00 | |
| 09/03 | Check 1042 | 312.40 | 5,748.15 | 5,748.15 | 0.00 | |
| 09/05 | Riverside Office Supply | 86.25 | 5,661.90 | 5,661.90 | 0.00 | |
| 09/08 | Elm Street Garage, parking | 64.00 | 5,597.90 | 5,597.90 | 0.00 | |
| 09/08 | Elm Street Garage, parking | 64.00 | 5,597.90 | 5,533.90 | +64.00 | |
| 09/10 | Deposit, Sam Rivera, invoice 1188 | 725.00 | 6,322.90 | 6,322.90 | 0.00 | |
| 09/12 | City Power & Light | 148.37 | 6,174.53 | 6,174.53 | 0.00 | |
| 09/15 | Transfer to payroll | 2,100.00 | 4,074.53 | 4,074.53 | 0.00 | |
| 09/26 | Deposit, Jane Doe Design | 980.00 | 4,990.53 | 5,054.53 | −64.00 | |
| 09/30 | Monthly service fee | 15.00 | 4,975.53 | 4,975.53 | 0.00 |
Two rows fail, and each failure says what happened. On the second 09/08 row, the printed balance did not move at all, yet the row claims $64.00 went out. A row whose amount the bank’s balance never felt is not a real transaction: it is a duplicate, or a line such as “balance forward” read as a transaction. On the 09/26 row, the balance fell $64.00 further than the rows in between explain, so a $64.00 debit is missing between 09/15 and 09/26. Open the PDF at that spot and the 09/22 parking charge is there.
Notice that checking against the printed balance above, rather than a total carried down from the opening balance, keeps each error to its own row. With a carried total, the difference would appear at the duplicate and stay on every row until the missing charge, which shows the span but blurs the second row. Both views are useful; the row-by-row one is the sharper.
Reading the difference on a failed row
| The difference is… | Most likely | What to check on the PDF |
|---|---|---|
| Equal to the row’s own amount, and the printed balance did not change | Duplicate row, or a balance-forward line read as a transaction | The row above, and the top of the page |
| Equal to a debit or credit that is not on the row | A row missing just above | Between this row and the previous one, especially across a page break |
| Twice the row’s amount | Sign flipped: money in read as money out, or the reverse | Which column the amount is in, and any CR or minus sign |
| Divisible by 9, on one row only | Two digits transposed in the amount | The amount, digit by digit |
| On this row, then the opposite on the next row | The printed balance itself was misread, not the amount | The balance on this row |
| Equal to the net of a whole page | A page missing | The page numbers, and whether every page reached the sheet |
Two quick illustrations from the same statement. If the City Power & Light payment of $148.37 is read as $184.37, the 09/12 row expects $6,138.53 against a printed $6,174.53: a difference of +36.00, divisible by 9, on that one row. If the Riverside Office Supply charge of $86.25 is read as money in, the 09/05 row expects $5,834.40 against a printed $5,661.90: a difference of −172.50, exactly twice the amount. The totals check would also fail in both cases, but only the running balance names the row.
Page breaks: where the running balance earns its keep
Most extraction errors happen at page boundaries. Statements repeat headers, print page subtotals, and often carry the balance to the next page on a “balance forward” line. A first row taken for a header disappears; a balance forward line read as a deposit of $5,597.90 adds $5,597.90 that never existed. The running balance flags both on the first row of the new page.
- Check across the page break, not page by page. The first row of page 2 must be checked against the last printed balance on page 1, otherwise a row dropped right at the break slips through.
- Keep the statement’s own row order. Several transactions on one day are printed in the bank’s posting order, and the balances follow that order. Sorting the sheet by date or amount before checking creates false failures.
- Where a statement prints page subtotals, check each page’s rows against them too. They catch a lost row even on statements without a balance column.
How to check the running balance in Excel by hand
You need the rows in statement order, with the printed balance copied in beside each one. Lay the sheet out like this: A Date, B Description, C Money in, D Money out, E Printed balance. Put the opening balance in E2, and the first transaction in row 3.
- 1In F3, the expected balance: =E2+C3-D3 (the printed balance above, plus money in, minus money out). If your sheet has one signed Amount column in C instead, use =E2+C3.
- 2In G3, the difference: =ROUND(E3-F3,2). The ROUND stops floating-point dust such as 0.0000001 from showing up as a failure.
- 3In H3, a flag: =IF(G3<>0,"CHECK","").
- 4Fill F3:H3 down to the last transaction.
- 5Find the first failure with =MATCH(TRUE,INDEX(G3:G1000<>0,0),0), which returns its position counting from row 3, or simply filter column H for CHECK.
- 6Fix that row against the PDF, then look at the next flag. Each flagged row is its own problem when you check against the printed balance above.
Two adjustments for awkward statements. If the bank prints a balance only on the last row of each day, check against a carried total instead: put the opening balance in F2, use =F2+C3-D3 in F3, and in G3 use =IF(E3="","",ROUND(E3-F3,2)) so only rows with a printed balance are compared. The first non-zero difference marks the first day with a problem, and every later change in the difference marks the next one. If the statement prints a daily balance table instead of a balance column, compare your carried total at the end of each day with that table in the same way.
Debit and credit mean the bank’s view here
On a bank statement, debit means money out of your account and credit means money in. That is the opposite of how the same account looks in your own ledger, where the bank is an asset and a deposit is a debit. For this check, use the statement’s meaning, or name the columns Money in and Money out to avoid the question.
For one statement this is quick once the rows are in a sheet; copying the printed balances in is the slow part. This site also has a running balance checker that runs in your browser, with nothing uploaded, if you would rather paste the rows than build the formulas.
When there is no running balance
Credit card statements are the common case. Without a balance per row, the strongest test left is the summary: previous balance plus purchases, fees and interest, minus payments and credits, equals the new balance, and the section totals match what you extracted. That catches a single missing or doubled charge, but it cannot point to the row, and it is exactly the kind of check a cancelling pair passes.
- Count the rows in each section (payments, purchases, fees, interest) against the statement.
- Look for amounts that appear more than once and confirm each against the PDF. In Excel, =COUNTIF(C:C,C3)>1 next to each row marks repeated amounts.
- Check the first and last row of every page by eye, since that is where rows are lost and repeated.
- Check continuity between statements: each statement’s opening balance must equal the previous statement’s closing balance.
Run both checks, in this order
- 1Continuity: the opening balance equals the previous statement’s closing balance. If not, a statement is missing, and no row check will fix that.
- 2Count and totals: quick, and they confirm nothing large is missing.
- 3Running balance, row by row: finds cancelling errors and names the rows.
- 4Read every flagged row against the PDF, and spot-check dates and descriptions, which no arithmetic covers.
Then fix the rows themselves. A balancing entry that makes the total agree leaves the duplicate and the gap in the books, and after this example, you know a total that agrees does not prove much on its own.