A bank reconciliation template in Excel needs a balance block that runs from the statement balance to an adjusted balance, a list of reconciling items with a date, source and reference, formulas that total the list, and two check cells that flag any total that does not foot. Sign-off fields complete it.
Which columns does a bank reconciliation template need?
A bank reconciliation template needs one row per reconciling item and about ten columns: item number, date, side, type, description, reference, signed amount, cleared date, age and support. Each column answers a question a reviewer asks, so none is decoration. Below is the full specification.
The column specification
The table lists each column on the items sheet, what goes in it and the formula, if any. Row 2 is the first item. Cell Rec!B4 is assumed to hold the statement date. All names and values are examples.
| Column | Name | What goes in it | Formula or rule |
|---|---|---|---|
| A | Item no. | A running number | Typed once and never reused; =ROW()-1 renumbers when the list is sorted |
| B | Date | The date the item was recorded or written | Date format; must be on or before the statement date for timing items |
| C | Side | Which balance the item adjusts | Drop-down list: Bank or Book |
| D | Type | The kind of item | Drop-down list: Deposit in transit, Outstanding check, Fee, Interest, NSF, Error, Other |
| E | Description | Payee or payer, in words a reviewer understands | Free text, never "adjustment" alone |
| F | Reference | Check number, deposit slip number or statement line | Text; unique for checks |
| G | Amount | The signed effect on that side's balance | Positive adds, negative subtracts; typed from the source document |
| H | Cleared date | The date it appeared on a later statement | Blank until it clears |
| I | Age (days) | Days outstanding at the statement date | =IF(H2="",Rec!$B$4-B2,"") |
| J | Support | Whether a document backs the item | Drop-down list: Y or N |
Why the amount column is signed
One signed column beats separate debit and credit columns because it lets one SUMIFS formula total each side. A deposit in transit is entered as a positive number, an outstanding check as a negative one. The sign always describes the effect on the balance of the side named in column C, so the reader never has to decide which way an item cuts.
Keep the data in an Excel table
Format the items range as an Excel table (Ctrl+T on Windows). A table extends its range when you add a row, so totals that refer to the table do not silently skip the item you added last. A fixed range such as G2:G30 stops counting at row 30, and nobody notices when row 31 is added.
The balance block: statement balance to adjusted balance
The balance block is the top section of the sheet that carries the statement balance and the ledger balance down to two adjusted balances. It has two columns, bank and book, each with a starting figure, the total of reconciling items for that side, and an adjusted result.
The layout
Place the block above the items list on a sheet called Rec. A layout that works, with example values:
| Cell | Label | Example value | Formula |
|---|---|---|---|
| B4 | Statement date | 30 Sept | Typed date |
| B6 | Balance per bank statement | 27,846.50 | Typed from the statement |
| B7 | Net bank-side items | 101.85 | =SUMIFS(Items[Amount],Items[Side],"Bank") |
| B8 | Adjusted bank balance | 27,948.35 | =B6+B7 |
| C6 | Balance per ledger | 29,228.50 | Typed from the ledger report |
| C7 | Net book-side items | (1,280.15) | =SUMIFS(Items[Amount],Items[Side],"Book") |
| C8 | Adjusted book balance | 27,948.35 | =C6+C7 |
Only the statement balance and the ledger balance are typed. The adjusted balances are calculated, so nobody can type a convenient number there. Those two typed inputs should be shaded a distinct colour, and everything else on the block should be a formula.
Where the figures come from
Type the statement balance from the closing balance printed on the bank statement and the ledger balance from the ledger report at the same date. Do not copy a balance from last month's file or from a live screen that may have moved since the statement date. A reviewer will tie both figures to the source documents, so say where each came from in a note beside the cell. The statement date drives the age column and sets the cut-off for what belongs on the list.
For a full month of figures run through a layout like this, see a bank reconciliation example worked line by line. The formal sections of a statement, in the order a reviewer expects, are described in the standard bank reconciliation statement format.
The bank rec template's reconciling-items list and references
The reconciling-items list is the table of every timing item and book-side adjustment, one per row, each with a date, a source and a reference. It is the evidence behind the balance block. A total with no list under it is a claim, and a list with references is a proof.
What each item needs
Every row should let a stranger find the document. For a check, record the check number and payee. For a deposit, record the deposit slip number or the date and the bank's reference. For a bank fee, interest or returned item, record the statement date and the line description. The reference column is what lets you sort and spot duplicates: two rows with the same check number mean one of them is wrong.
Types that belong on the list
Use a fixed set of types so totals by type are possible:
- Deposit in transit and outstanding check, both bank-side timing items.
- Fee, interest, NSF and direct debit, book-side items that need a journal entry.
- Error, split into bank error (bank side) and book error (book side).
Resist an "Other" type for anything beyond a one-off. If a type appears twice, add it to the list. To see how long timing items may sit, and how to age them, read outstanding checks and deposits in transit.
Carry-forward and cleared items
At the next month-end, copy the uncleared rows into the new workbook. Enter the cleared date on rows that cleared, and move them to a "cleared" sheet or filter them out. Keeping cleared rows in the table with a date lets you answer "when did that check clear?" without searching old files.
Should the Excel template check its own footing?
Yes. A template should test itself, because a reconciliation whose totals cannot fail proves nothing. Two check cells are enough for most accounts: one confirms the two adjusted balances agree, and one confirms the bank statement itself foots. Each shows OK or an error that cannot be missed.
Check 1: the adjusted balances agree
In Rec!B10, enter:
=IF(ROUND(B8-C8,2)=0,"OK","DIFFERENCE "&TEXT(B8-C8,"#,##0.00"))
The ROUND matters. Floating-point arithmetic in a spreadsheet can leave a result such as 0.0000000001 that is not exactly zero, and a plain =B8=C8 test would call a reconciled sheet unreconciled. Rounding to cents removes the noise without hiding a real cent of difference.
Check 2: the statement foots
Add four typed lines to the block: opening balance, total credits, total debits and closing balance, all taken from the statement. Then:
=IF(ROUND(Open+Credits-Debits-Close,2)=0,"OK","STATEMENT DOES NOT FOOT")
If the statement does not foot, stop. A missing page or a mistyped total is the likely cause, and reconciling to a closing balance you cannot trust wastes the effort. The idea is explained further in footing and continuity checks on a bank statement.
Optional checks
Two more checks are cheap and useful. Count the rows whose Support is N, using =COUNTIFS(Items[Support],"N"), and show it beside the checks. And add conditional formatting that turns an Age cell orange above 90 days. Neither blocks the reconciliation, but each puts a problem in front of the reviewer instead of leaving it to be found.
For formulas that match statement lines to ledger lines, rather than check totals, see which Excel formula matches bank transactions best.
When a downloaded bank reconciliation template breaks: hardcoded totals
A downloaded template breaks when its totals are typed numbers rather than formulas, or when its formulas stop short of the data. Such a template looks as if it reconciles because the displayed numbers agree, but changing an input changes nothing. Test every template you did not build before using it.
A five-minute test
Run these checks on any template, in this order:
- Show formulas. Press Ctrl+` (the backtick key) to display formulas instead of values. Any total, subtotal or adjusted balance that shows a plain number is hardcoded.
- Change an input. Alter the statement balance by 100.00. The adjusted bank balance should move by 100.00 and the check should show a difference. If nothing changes, the check is not connected.
- Add an item at the end of the list. Enter a new row below the last item. The side total should change. If it does not, the range is fixed and will drop later rows.
- Look for a plug row. A line labelled "other", "difference" or "adjustment" that contains a typed number or a formula returning the difference between the two sides is a plug. It forces agreement.
- Check for hidden rows, columns and sheets. Unhide everything and read what is there.
A template that fails any of these should be fixed or dropped. The difference between a plug and a genuine item is covered in forcing a statement to balance with a plug.
Copy-paste damage
A good template can also be broken by use. Pasting values over a formula cell, deleting a row that a formula refers to, or sorting the items range without the side column can all leave a sheet that looks fine and is not. Protect the formula cells (Review, Protect Sheet) and leave only the input cells unlocked. Protection is not security, but it stops accidents.
Sign-off fields that turn the template into a workpaper
Sign-off fields turn a calculation into a workpaper by recording who prepared the reconciliation, who reviewed it, when and what they relied on. Add them in the header block: entity, account, statement date, preparer, date prepared, reviewer, date reviewed and a short note for review points.
A reviewer needs more than a signature. Useful fields, in the order they appear:
- Entity and account. Name the entity, the bank and the last four digits of the account number. A reconciliation with no account on it can be attached to the wrong file.
- Statement date and period. The cut-off date, and the first and last date covered.
- Source references. Where the statement and ledger balances came from, such as the PDF file name and the ledger report name and run date.
- Preparer and date. Name and the date the work was finished.
- Reviewer and date. A different person, with a date after the preparer's.
- Review notes. One line for each question raised and how it was answered.
- Tickmark legend. If you tick items on a printout, print the legend on the sheet.
Print the sheet to PDF once it is signed and store it with the statement. The saved copy matters more than the live workbook, which can be edited later.
When you want the same layout for other accounts, a general ledger reconciliation template in Excel generalises the balance block and the items list. And if you would rather not build the statement side of this by hand, PreciseBankFlow is opening to CPA and audit firms in small groups; you can request a spot from the Get started page.