Why your bank statement spreadsheet doesn’t balance

You've converted a statement, added up the money in and the money out, and the total doesn't come to the closing balance printed at the bottom. Somewhere in three hundred rows there's a mistake.

Rereading the whole statement line by line is the slow way to find it. The amount you're off by usually hints at what kind of mistake it is, and the running balance column can take you straight to the row.

Start with the difference

Every statement follows the same rule.

opening balance + money in − money out = closing balance

Work out the left-hand side from your spreadsheet and subtract the closing balance printed on the statement. What's left over is the gap, and its size often tells you what to look for.

If nothing here fits, there's probably more than one mistake. Use the running balance to find the first, fix it, and look at the difference again.

Find the exact row with the running balance

Most statements print a balance after every transaction, or at least at the end of each day. That means you can check each row on its own. Every balance should equal the one before it, plus that row's money in, minus its money out.

Say your spreadsheet has Date in column A, Description in B, Debit (money out) in C, Credit (money in) in D and Balance in E. The first transaction is in row 2, and you've typed the statement's opening balance into H1. Add a check column in F. In F2, compare the first row against the opening balance.

=ROUND($H$1 + D2 - C2 - E2, 2)

In F3, compare each row against the one above it, and fill the formula down.

=ROUND(E2 + D3 - C3 - E3, 2)

Rows that are right show 0. The first row that doesn't is where things went wrong, either on that row or just before it, and the number in F is the size of the error. The rows after it go back to 0, since each one is only checked against its neighbour, so one mistake shows up in one place.

Some statements need a little preparation first. If the newest transaction is at the top, sort the rows oldest first before you add the check column. If your bank only prints a balance on the last transaction of each day, check just the rows that have one, each against the previous row with a balance, adding up the transactions in between. And if one PDF covers several accounts, say checking and savings, each account has its own opening and closing balance and needs checking separately.

Credit cards work the other way round

On a credit card statement the balance is what you owe. Purchases, fees and interest push it up, and payments and refunds bring it down.

previous balance + purchases + fees + interest − payments − credits = new balance

So if a credit card spreadsheet is off by twice the total of its payments, the payments were treated as charges. It's the same sign mix-up as in the table, applied to every payment at once.

Gaps that aren't mistakes

A statement only includes posted transactions. If you compare it with a download from online banking, pending transactions will account for some of the difference. Statement periods also rarely match calendar months, so check the dates on the statement before comparing it with anything else.

Or let the converter check for you

statement2sheets runs this check on every statement it converts. It compares the transactions with the statement's opening and closing balances, tells you how far off they are if they don't agree, and flags the rows it was least sure about so you know where to start. You can fix a row in place before you export. It all happens in your browser, so the statement is never uploaded. Try it on a statement, or if you're starting from scratch, read the ways to get a statement into Excel.