How to reconcile a bank statement in Excel (with a free template)

Reconciling a bank statement means proving that the transactions you have add up to what the bank says. If they do, nothing is missing, doubled or on the wrong side. If they don't, you want to know by how much and where. Here's how to do it in Excel, with a free template that does the arithmetic for you.

Download the free bank reconciliation template (.xlsx)

The one rule

Every bank statement follows the same equation:

opening balance + money in − money out = closing balance

The opening and closing balances are printed on the statement. Money in and money out are the totals of your transactions. If the left side matches the right to the cent, the statement reconciles.

Using the template

1. Type the statement's opening and closing balances into the two yellow cells at the top of the Reconciliation sheet.
2. Paste the transactions from row 9 down: date, description, money out and money in, each amount as a positive number in its own column.
3. If the statement prints a running balance after each transaction, paste that into the Balance column as well. It's optional, but it's what finds the exact problem row.
4. Read the Result line. It either says the statement is reconciled, or how much it's off by.

The template has room for 1,000 transactions. The "How to use" sheet repeats these steps and explains how to read the difference.

Building it yourself

If you'd rather add it to your own workbook, the formulas are short. With the opening balance in B3, the closing balance in B4, money out in column C and money in in column D:

Calculated closing:  =ROUND(B3 + SUM(D9:D1008) - SUM(C9:C1008), 2)
Difference:          =ROUND(B5 - B4, 2)

ROUND matters. Spreadsheets add decimals with tiny binary errors, and without it a perfectly reconciled statement can show a difference of 0.0000000001.

When it's off: find the row

The running balance pinpoints the problem. Each printed balance should equal the one before it, plus that row's money in, minus its money out. The template's Check column does this for every row (the first row compares against the opening balance):

=ROUND(E8 + D9 - C9 - E9, 2)

Rows that agree show 0. The first row that doesn't shows the size of the error, highlighted in red, and it's either that row or the one just before it that's wrong. Statements that print a balance only once a day leave some Balance cells empty; the check skips a row when its balance, or the one before it, is empty.

Reading the difference

• Exactly one transaction's amount: that transaction is missing, or there twice.
• Exactly twice one transaction's amount: it's on the wrong side, as money in entered as money out or the reverse.
• Divisible by 9 in cents, on figures typed by hand: two digits swapped, or a decimal point in the wrong place.

There's more on this in why your bank statement spreadsheet doesn't balance.

Credit cards

On a card statement the balance is what you owe, so purchases increase it and payments reduce it. To use the same template, enter purchases in the Money in column and payments in the Money out column. Otherwise the result will be off by twice your payments.

Or skip the setup

The statement converter runs this check on every PDF statement it reads. It compares the transactions with the statement's own opening and closing balances, says by how much they're off if they don't agree, and flags the rows it was least sure of. It runs in your browser, so the statement isn't uploaded.