A bank reconciliation template in Excel needs four parts on one sheet: a header naming the client, account and statement date; a bank-side block; a book-side block; and a list of reconciling items that feeds both blocks through formulas. The two adjusted balances should agree, and a difference cell shows when they do not.
The bank reconciliation format in Excel, section by section
The bank reconciliation format in Excel runs top to bottom in four blocks: header, bank side, book side, and a reconciling items list. The two side blocks pull their totals from the list with formulas, so the only typed figures are the two starting balances and the items themselves. A difference cell at the bottom proves agreement.
This layout follows the order of a paper reconciliation. IRS Publication 583 walks through one for a small business: start from the bank statement balance, add deposits not yet on the statement, subtract outstanding checks, then correct the checkbook balance for items such as bank charges, until the adjusted bank statement balance equals the adjusted checkbook balance. The template puts each of those steps in its own row.
The four blocks, in order:
- Header. Client name, bank and account (last four digits only), statement ending date, preparer and reviewer with dates.
- Bank side. Balance per bank statement, plus deposits in transit, minus outstanding checks, plus or minus bank errors, ending at the adjusted bank balance.
- Book side. Balance per books, plus credits the bank recorded that the books have not, minus fees and debits the books have not recorded, plus or minus book errors, ending at the adjusted book balance.
- Reconciling items list. One row per item, each tagged with a type code that tells the formulas which block it belongs to.
Why each item moves a balance up or down is arithmetic covered in the post on the adjusted bank balance and adjusted book balance. This post covers how to lay that arithmetic out in cells.
A cell-by-cell layout with example figures
The layout below is one sheet. The client, account and every amount are fictional examples for a September 2026 statement. Rows 25 to 60 hold the reconciling items list; the formulas reference that range.
| Cell | Label in column A | Content of column B (example) |
|---|---|---|
| B1 | Client | Example Client LLC |
| B2 | Account | Harbor Federal checking XXXX-4821 |
| B3 | Statement ending date | 9/30/2026 |
| B4 | Prepared by / date | Typed initials and date |
| B5 | Reviewed by / date | Typed initials and date |
| B7 | Balance per bank statement | 48,215.60 (typed from the statement) |
| B8 | Add: deposits in transit | =SUMIF($C$25:$C$60,"DIT",$D$25:$D$60) |
| B9 | Less: outstanding checks | =SUMIF($C$25:$C$60,"OC",$D$25:$D$60) |
| B10 | Bank errors (+ or -) | =SUMIF($C$25:$C$60,"BANKERR",$D$25:$D$60) |
| B11 | Adjusted bank balance | =B7+B8-B9+B10 |
| B13 | Balance per books | 49,447.40 (typed from the ledger) |
| B14 | Add: credits not in books | =SUMIF($C$25:$C$60,"BOOKADD",$D$25:$D$60) |
| B15 | Less: debits not in books | =SUMIF($C$25:$C$60,"BOOKLESS",$D$25:$D$60) |
| B16 | Book errors (+ or -) | =SUMIF($C$25:$C$60,"BOOKERR",$D$25:$D$60) |
| B17 | Adjusted book balance | =B13+B14-B15+B16 |
| B19 | Difference | =ROUND(B11-B17,2) |
| B20 | Status | =IF(B19=0,"Reconciled","Difference to explain") |
With the example items in the next section, B8 returns 3,400.00, B9 returns 2,186.45, B14 returns 12.25 and B15 returns 30.50. Both adjusted balances come to 49,429.15, and B19 shows zero.
Which columns does an Excel reconciliation template need?
An Excel reconciliation template needs the same core columns on every transaction list: date, reference, description, amount and a type or status column. The book list and the bank list share that layout so rows can be compared side by side, and the reconciling items list adds a type code that the summary formulas read.
The reconciling items list for the example sits in rows 25 to 60:
| Column | Heading | Example row |
|---|---|---|
| A | Date | 9/29/2026 |
| B | Description | Customer deposit, not on statement |
| C | Type code | DIT |
| D | Amount | 3,400.00 |
| E | Cleared in month | Blank until it appears on a later statement |
| F | Note | Deposit slip in client folder |
The example list holds five items: a 3,400.00 deposit in transit (DIT), check 1187 for 1,250.00 and check 1190 for 936.45 (both OC), 12.25 of interest the books have not recorded (BOOKADD) and a 30.50 service fee the books have not recorded (BOOKLESS).
Keep every amount positive in column D and let the type code carry the direction. The summary block decides whether a type adds or subtracts. Mixing positive and negative amounts in one column, with a sign convention the next preparer has to guess, is a common source of wrong totals.
If you also keep the full book and bank transaction lists in the workbook, put each on its own sheet with matching columns: date, check or reference number, description, amount, and a matched column. That is where automatic matching with lookup formulas comes in, and the post on match keys for automatic Excel reconciliation explains which fields make a reliable pair.
Use fixed type codes, typed exactly the same each time. SUMIF compares text, so "OC" and "O/C" are different codes, and an item tagged with a variant drops out of every total without warning.
Excel formulas that compute the adjusted balances
The adjusted balances in an Excel bank reconciliation template come from three kinds of formula: SUMIF to total each type of reconciling item, plain addition and subtraction to build each adjusted balance, and a difference check that compares the two. Every total cell should hold a formula, never a typed number.
Microsoft's SUMIF function reference gives the syntax as SUMIF(range, criteria, [sum_range]). The range is the type code column, the criteria is the code, and the sum range is the amount column. Microsoft notes that text criteria must be enclosed in double quotation marks, which is why the codes appear as "DIT" and "OC". It also advises that the sum range be the same size and shape as the range.
The adjusted balance formulas are ordinary arithmetic. B11 adds deposits in transit to the bank balance and subtracts outstanding checks. B17 adds unrecorded credits to the book balance and subtracts unrecorded fees. Error rows are added as signed amounts, because an error can go either way.
The difference cell uses ROUND so the comparison happens in whole cents. Microsoft's ROUND function reference gives the syntax as ROUND(number, num_digits), and two digits rounds to cents. The status cell then uses IF, which Microsoft's IF function reference gives as IF(logical_test, value_if_true, [value_if_false]), to show a word a reviewer can read at a glance.
Add one check figure outside the reconciliation: a cell that totals column D for all rows, compared with the sum of B8, B9, B10, B14, B15 and B16. If an item has a missing or mistyped code, the two numbers differ, and you know an item fell out of the summary.
Should you use a free download bank reconciliation template?
A free download bank reconciliation template can save setup time, but check it before trusting it with a client's account. Open every total cell and confirm it holds a formula, confirm the ranges cover enough rows, and test it with a month you have already reconciled by hand. If it fails that test, build your own.
Things to check in any downloaded template:
- Formulas in every total. Click each subtotal and adjusted balance. A typed number in a total cell is the most common flaw.
- Signs. Enter one deposit in transit and one outstanding check and confirm each moves the bank balance in the right direction.
- Range size. Count the rows the formulas cover. A template that sums twenty rows breaks the month a client has twenty-one outstanding checks.
- Both sides. Some templates handle only bank-side items and leave no place for fees or interest the books have not recorded.
- Macros. A file that asks to enable macros runs code. Use a plain workbook unless you know what the code does.
- Hidden rows and sheets. Unhide everything and look at what is there.
A tested template is worth saving as a template file. Microsoft's guide to saving a workbook as a template describes going to File, Export, Change File Type and choosing Template, then creating new workbooks from it under File, New, Personal. Each month then starts from a clean copy instead of last month's file.
Template mistakes: hardcoded totals and overwritten formulas
The two template mistakes that cause the most trouble are hardcoded totals, where someone types a number over a formula, and formula ranges that stop short of new rows. Both break the template silently: the sheet still shows a balanced difference, but it no longer computes from the items listed below it.
A hardcoded total usually starts as a quick fix. The difference is off by a few dollars, someone types the expected figure into B9, and the sheet balances. Next month the copy still holds that typed number, and it never changes again. The fix is to lock the formula cells. Microsoft's guide to locking cells notes that cells are locked by default but locking has no effect until you protect the sheet. Unlock the input cells (B7, B13 and the item rows), then protect the sheet so formulas cannot be typed over.
Short ranges are the second mistake. The SUMIF formulas above cover rows 25 to 60. An item typed into row 61 sits outside every range and is left out of every total, though it looks like part of the list. The check figure from the formulas section catches this.
The Washington State Auditor's Best Practices for Bank Reconciliations makes the same point for any spreadsheet used to reconcile. It recommends controls such as passwords, tracked changes, audit trails and check figures so the calculations are the ones intended.
Other quiet breaks include copying a client's template to another client without clearing the header, sorting the items list without its type codes, and pasting values over the difference cell to make a review copy.
Rolling the Excel template forward to next month
Rolling a bank reconciliation template forward means starting next month from a fresh copy, typing the new statement and book balances, and carrying over only the reconciling items that have not cleared. Items that appeared on the new statement are marked cleared and left behind with the old month.
A practical sequence for the roll-forward:
- Open a new copy from the saved template, not last month's workbook.
- Fill the header with the new statement ending date.
- Check the starting point. The new statement's beginning balance should equal last month's statement ending balance. If it does not, a statement is missing or something changed, and the bank statements to Excel category covers checking statements against each other.
- Review last month's items. For each deposit in transit and outstanding check, look for it on the new statement. Note the date it cleared in column E of last month's copy.
- Carry forward the rest. Copy uncleared items into the new list with their original dates, so their age stays visible.
- Drop book-side items once posted. Fees and interest recorded in the books last month should not reappear.
- Add this month's new items and confirm the difference cell reads zero.
Keep each month's finished copy. A file per month, named with the client, account and statement date, gives a reviewer a clean trail.
Statements that arrive as PDFs have to be keyed or converted before the template can use them. Schema CPA reads a client's bank statements, ties out each month's balances against the month before, and exports the figures to Excel or CSV, which gives the template's typed cells a checked source. The pricing section of the homepage lists the plans. For the formulas that match book lines to bank lines automatically, read the auto bank reconciliation in Excel post, and for the same work inside accounting software, the QuickBooks Online reconciliation walkthrough.