How-to
Excel bank statement template: layout and formulas
How to build a reconciling bank statement spreadsheet in Excel: six columns, the running balance formula, a closing balance check and error formatting.
6 min read · Last reviewed · by the stmtai team
You do not need to download a template. A bank statement spreadsheet that reconciles itself is six columns, one formula copied down, and one check cell. Building it from a blank workbook takes about five minutes, and you will understand every cell in it, which is not true of anything you download.
This guide gives you the layout, the exact formulas, and the conditional formatting that turns the check cell red when something is wrong. The same sheet works in Google Sheets with no changes to the formulas.
The layout
Put headers in row 1. Row 2 is reserved for the opening balance. Transactions start in row 3.
| Column | Header | What goes in it | Format |
|---|---|---|---|
| A | Date | Transaction date from the statement | Date |
| B | Description | Payee or memo, as printed | Text |
| C | In | Money coming in (deposits, refunds, interest) | Number, 2 decimals |
| D | Out | Money going out (payments, fees, withdrawals) | Number, 2 decimals |
| E | Balance | Running balance, formula | Number, 2 decimals |
| F | Category | Rent, Payroll, Fees, whatever you use | Text or drop-down |
Two rules that save you trouble later. Enter amounts as positive numbers in either C or D, never both, and never as a negative. And keep row 2 as the opening balance row with nothing but the printed opening balance in E2. The whole check depends on it.
In row 2, type "Opening balance" in B2 and the printed opening balance from the statement in E2.
The running balance formula
In E3, type:
=E2+C3-D3
Previous balance, plus this row's money in, minus this row's money out. Select E3 and copy it down as far as your transactions go. Excel adjusts the row numbers as it goes, so E4 reads =E3+C4-D4 and so on.
That is the only formula the transaction rows need. If you paste a new statement's rows underneath, copy the formula down again and the balance carries on.
The closing balance check
This is the part most downloaded templates leave out, and it is the part that matters. Put a small block to the right of the data, say in columns H and I:
| Cell | Label (column H) | Formula or entry (column I) |
|---|---|---|
| H2 / I2 | Opening balance (printed) | Type it from the statement |
| H3 / I3 | Total in | =SUM(C3:C500) |
| H4 / I4 | Total out | =SUM(D3:D500) |
| H5 / I5 | Closing balance (calculated) | =I2+I3-I4 |
| H6 / I6 | Closing balance (printed) | Type it from the statement |
| H7 / I7 | Difference | =ROUND(I5-I6,2) |
Then change E2 to =I2 so the opening balance is typed in exactly one place.
If I7 is 0.00, every transaction on the statement is in the sheet, with the right amount, on the right side. If it is anything else, something is missing, duplicated, or on the wrong side, and the size of the difference tells you which.
The 500 in the SUM ranges is just a ceiling; raise it if you keep more than 500 rows on one sheet. The ROUND matters because Excel stores decimals in binary and a column of two-decimal numbers will occasionally sum to 0.0000000001 instead of 0. Rounding to two places stops a phantom difference from showing.
Make the difference cell go red
- Click I7.
- Home > Conditional Formatting > Highlight Cells Rules > Not Equal To.
- Enter 0 and pick the light red fill.
- Click OK.
Now the cell is white when the statement reconciles and red when it does not. In Google Sheets the same thing is Format > Conditional formatting > Format cells if > Is not equal to > 0.
Finding the row that is wrong
When I7 is not zero, use the difference to narrow it down:
- Difference equals one transaction amount: that row is missing or entered twice.
- Difference equals exactly twice a transaction amount: that row is in the wrong column (an "out" typed as "in", or the reverse).
- Difference is a few cents or a round number like 9, 90 or 900: a digit was mistyped. A difference divisible by 9 is the classic sign of two digits swapped.
- Difference matches nothing: probably two errors. Fix the ones you can find and recheck.
If the statement prints a balance after every transaction, add a seventh column, G, headed "Printed balance", and type in the printed figure for a handful of rows spread through the statement. In H3 put:
=IF(G3="","",ROUND(E3-G3,2))
Copy it down. Every row where you typed a printed balance now shows the gap between your running balance and the bank's. The first row that shows a non-zero gap is at or just after the error. Type in more printed balances between the last good row and the first bad one until you have it pinned to a single line. This is how bookkeepers have found posting errors for a century; the spreadsheet just does the subtraction.
Categories and totals
Type your category list somewhere out of the way, say K2:K15, then select F3:F500 and go to Data > Data Validation > Allow: List, with the source set to that range. You get a drop-down on every row and no more "Rent" next to "rent" next to "RENT".
Total by category with SUMIF:
=SUMIF($F$3:$F$500,"Rent",$D$3:$D$500)
Put the category name in a cell and point the formula at it instead of the literal string, and you can list every category with its total in one block. For a discussion of how to assign categories consistently across hundreds of rows, see categorising bank transactions.
Getting the transactions into the sheet
The template is only as good as the rows in it, and there are three ways to fill them.
Typing is accurate for a page and miserable for a year; typing versus converting sets out where the line is. Your bank's CSV download pastes straight in but usually has a single signed Amount column instead of In and Out; put the CSV in a spare sheet and pull it across with =IF(Amount>0,Amount,0) for column C and =IF(Amount<0,-Amount,0) for column D. PDF statements need converting first, and the PDF-to-Excel guide compares the ways to do that.
If you use stmtai for the PDF step, the workbook it produces already follows this shape: In and Out as real number cells, a suggested category on each row, and a Summary sheet that shows the printed opening and closing balances alongside the calculated ones with the difference. You can paste its transaction rows into this template or just work in its file.
Mistakes that break the sheet
Numbers stored as text. If a column of amounts is left-aligned and SUM returns 0, the cells are text. Select the column, Data > Text to Columns > Finish, and Excel re-reads them as numbers.
Sorting after adding the formula. The running balance formula refers to the row above, so if you sort rows the references scramble. Sort by date first, add the formula last. If you must re-sort, delete column E, sort, then re-enter the formula in E3 and copy down.
Deleting the opening row. Everything in column E is relative to E2. If E2 goes, E3 refers to the header text instead of a number and the whole column shows a value error.
Negative numbers in the In or Out columns. The formula assumes both are positive. A refund is money in, so it goes in C, not as a negative in D.
Mixing two statements' opening balances. If you keep a year on one sheet, there is one opening balance at the top, and each month's printed closing balance should match your running balance on the last row of that month. Add a printed balance in column G at each month end and the H column check will confirm the sheet is right at every boundary, not just the last one. The bank reconciliation guide covers what to do when the books and the bank disagree for legitimate reasons, such as uncleared cheques.