Bank Reconciliation Template in Excel: Build Your Own

TL;DR: A good bank reconciliation template has five sheets: Inputs (balances and dates), Statement (bank lines), Books (ledger lines), Outstanding (items carried from last month) and Summary (adjusted bank vs. adjusted book, which must equal zero). Matching formulas mark each line, conditional formatting highlights what's left, and you reuse the file every month by clearing data and keeping formulas.

Downloadable reconciliation templates are everywhere, and most of them are a single sheet with a few boxes for "add deposits in transit, subtract outstanding checks." They're fine for understanding the idea. They're not much help when you have 300 transactions and need to find which ones don't match.

We prefer to build our own, because a template that matches line by line does the real work. This guide walks through the structure we use and the formulas behind it. You can build it in about half an hour, and then use it every month.

If you'd like a refresher on the theory first, our step-by-step guide to reconciling a bank statement in Excel covers it.

Why not just use a free downloadable template?

There's nothing wrong with the free templates you'll find online; many are well made. The trouble is that most of them do the arithmetic of the summary and leave the hard part, deciding which items are outstanding, to you. You still scan two lists by eye, tick items off, and hope you didn't miss one. That's manageable at 30 transactions a month and unreliable at 300.

The template described here differs in three ways that matter:

1. It proves the statement data is complete before you start, with the self-check.
2. It matches line by line with keys, so the outstanding list is produced by formulas, not by eye.
3. It carries prior items forward explicitly, so nothing disappears between months.

If you already have a template you like, you can bolt these three features onto it. They're the parts that turn a summary page into a working tool.

Testing it on a month you know

Before relying on a new template, run it on a month you've already reconciled some other way, in accounting software or by hand. You know the right answer, so you'll quickly see whether the matching and summary formulas behave. Pay attention to repeated amounts on the same day, card payments and transfers, and checks that cleared in a later month; those are the cases that expose formula mistakes.

The structure

Sheet	Purpose
Inputs	Account, period, statement opening and closing balances, book balance
Statement	One row per bank statement transaction
Books	One row per ledger transaction for the account and period
Outstanding	Items from previous months not yet cleared
Summary	The reconciliation itself, with a zero check

Use Excel Tables (Ctrl+T) for Statement, Books and Outstanding, named Stmt, Bk and Out. Tables make formulas readable and expand automatically when you paste in more rows.

Sheet 1: Inputs

A small block of named cells:

Name	Value
Account	Business Checking ****1234
PeriodStart	2026-03-01
PeriodEnd	2026-03-31
StmtOpening	(from statement)
StmtClosing	(from statement)
BookClosing	(from your ledger at PeriodEnd)

Name each value cell (Formulas > Define Name) so the formulas elsewhere read like sentences.

Sheet 2: Statement

Columns: Date, Description, Amount (signed: money in positive, money out negative), Key, Match.

Paste the bank transactions here. If your statement is a PDF, convert it first; SheetStatement's bank statement to Excel converter gives you Date, Description and amounts, and it checks the statement balances.

Self-check (on the Statement sheet or in Summary):

=ROUND(StmtOpening + SUM(Stmt[Amount]) - StmtClosing, 2)

This must be zero before you go further. If it isn't, the statement data is incomplete. Our free balance checker will find where.

Key (with an occurrence counter so repeated identical transactions match one-to-one):

=TEXT([@Date],"yyyy-mm-dd")&"|"&TEXT([@Amount],"0.00")&"#"&COUNTIFS(INDEX([Date],1):[@Date],[@Date],INDEX([Amount],1):[@Amount],[@Amount])

Match:

=IF(COUNTIF(Bk[Key],[@Key])>0,"Matched",
 IF(COUNTIF(Out[Key],[@Key])>0,"Cleared from prior",""))

Sheet 3: Books

Columns: Date, Description, Amount (same sign convention), Reference, Key, Match.

Export the account's register from your accounting software for the period, or paste your cash book. Use the same Key formula.

Match:

=IF(COUNTIF(Stmt[Key],[@Key])>0,"Matched","")

Handling date differences

Bank dates and book dates often differ by a day or two (you record a check when you write it; the bank records it when it clears). Exact keys won't match those. Add a second pass:

NearMatch:
=IF([@Match]<>"","",
 IF(COUNTIFS(Stmt[Amount],[@Amount],Stmt[Date],">="&[@Date]-5,Stmt[Date],"<="&[@Date]+5,Stmt[Match],"")>0,"Near",""))

Review "Near" items by eye and, if they're the same transaction, overwrite the Match cell with "Matched (date diff)". Manual overrides are fine; just make them visible.

Sheet 4: Outstanding

Items recorded in the books in earlier periods that hadn't cleared the bank by last month's reconciliation: typically outstanding checks and deposits in transit.

Columns: Date, Description, Amount, Key, Origin period, Cleared this period.

Cleared this period:

=IF(COUNTIF(Stmt[Key],[@Key])>0,"Yes","No")

For checks, a date-insensitive match on amount and check number is more reliable, since the clearing date is unpredictable. If you have check numbers, make the key "CHK"&[@Reference]&"|"&TEXT([@Amount],"0.00") on both sides.

Sheet 5: Summary

This is the reconciliation:

Line	Formula
Statement closing balance	=StmtClosing
+ Deposits in transit	=SUMIFS(Bk[Amount],Bk[Match],"",Bk[Amount],">0")+SUMIFS(Out[Amount],Out[Cleared this period],"No",Out[Amount],">0")
− Outstanding checks/payments	=SUMIFS(Bk[Amount],Bk[Match],"",Bk[Amount],"<0")+SUMIFS(Out[Amount],Out[Cleared this period],"No",Out[Amount],"<0")
= Adjusted bank balance	sum of the above
Book closing balance	=BookClosing
+ Bank items not in books (interest, deposits)	=SUMIFS(Stmt[Amount],Stmt[Match],"",Stmt[Amount],">0")
− Bank items not in books (fees, payments)	=SUMIFS(Stmt[Amount],Stmt[Match],"",Stmt[Amount],"<0")
= Adjusted book balance	sum of the above
Difference	=ROUND(AdjBank-AdjBook,2)

Note the signs: outstanding payments are negative amounts, so adding them reduces the bank balance, which is what we want.

When the difference is zero, you're reconciled. In practice, you'll usually also record the "bank items not in books" in your ledger (fees, interest) so next month starts clean.

Conditional formatting

• Statement and Books: highlight rows where Match is blank (amber).
• Summary: Difference cell green if zero, red otherwise.
• Statement self-check: red if non-zero.

Using the template each month

1. Save a copy with the new period in the file name.
2. Move last month's unmatched book items (outstanding checks and deposits in transit) into the Outstanding table, along with any still-uncleared items already there.
3. Clear Statement and Books, keeping the table structure and formulas.
4. Update Inputs with the new period and balances.
5. Paste the new statement and ledger data.
6. Check the statement self-check is zero.
7. Work the amber rows until the Difference is zero.
8. Save and file it with the statement PDF.

Adding a reconciliation sign-off block

If anyone else relies on your reconciliations, an accountant, a business partner, an auditor, add a small sign-off block to the Summary sheet:

Field	Value
Prepared by	name
Prepared on	date
Reviewed by	name
Reviewed on	date
Notes	anything unusual

It looks bureaucratic for a two-person business, and it isn't. When someone asks months later whether March was reconciled, the answer is in the file, with a name and a date. For businesses with more than one person handling money, having someone other than the preparer review the reconciliation is also a basic control: the person who records transactions shouldn't be the only person who checks them.

Extending the template for several accounts

For businesses with several bank accounts, keep one file per account per month, and add an index workbook that pulls each file's Difference cell into one table. Alternatively, put every account in one workbook with an Account column on each table and an Account selector on the Summary sheet. Either works; the first is simpler to understand, the second easier to review. What matters is that every account is reconciled every month, and that you can see at a glance which ones aren't.

Speeding up the paste step

The slowest part of using the template is often getting the data in. Two shortcuts help:

1. Keep source formats consistent. If the statement data always comes from the same converter with the same columns, and the ledger export always uses the same report, pasting becomes a two-second job. Save the ledger report settings in your accounting software as a memorized or custom report.
2. Use Power Query to load both from files in a folder, then refresh. Our Power Query guide explains the folder approach.

With both in place, the monthly routine is: drop two files in a folder, refresh, review amber rows. The thinking goes into the unmatched list, which is where it should go.

Keeping the template trustworthy

Templates degrade. Someone types over a formula, a table loses a row, a named range points at the wrong cell. Twice a year, test the template with a month you've already reconciled: paste the data in and confirm you get the same result. Lock formula cells (Review > Protect Sheet, leaving the input cells editable) so they can't be overwritten by accident. And keep a master copy of the blank template somewhere separate, so you can always start fresh.

A worked example

March, business checking:

• StmtOpening: 10,250.00, StmtClosing: 11,482.35. Self-check: zero.
• BookClosing: 10,275.10.
• Outstanding from February: check 1031 for −640.00, and a deposit in transit of 1,200.00.

After pasting:

• 112 statement lines; 107 match book lines; 5 unmatched.
• The February deposit in transit (1,200.00) cleared on March 2 and shows "Cleared from prior."
• Check 1031 still hasn't cleared, so it stays outstanding.
• Unmatched statement lines: a bank fee of −25.00, interest of 2.15, a direct debit of −89.90 for a software subscription nobody recorded, and a deposit of 820.00.
• Unmatched book lines: check 1047 for −310.00 (written March 30) and a deposit of 450.00 recorded March 31 but banked April 1.

Summary:

• Adjusted bank: 11,482.35 + 450.00 − 310.00 − 640.00 = 10,982.35
• Adjusted book: 10,275.10 + 2.15 − 25.00 − 89.90 + 820.00 = 10,982.35

Difference: zero. The template has done its job, but the unmatched list still needs attention, because "reconciled" doesn't mean "nothing to do."

The fee, the interest and the subscription simply need recording in the books. The 820.00 deposit is more interesting. Nobody expected it. Searching the Books sheet for 820.00 finds a customer payment on March 12 that matched a statement deposit the same day. Searching the Statement sheet finds two 820.00 deposits from the same customer, on March 12 and March 13. The customer paid the same invoice twice. That's a conversation with the customer (refund or credit against the next invoice), and an entry in the books recording the overpayment. A matching template surfaces exactly this kind of thing; a one-box "add deposits, subtract checks" template would have hidden it inside a total.

Common template mistakes

No self-check on the statement. You'll spend an hour reconciling incomplete data.

Matching on amount only. Fails when amounts repeat. Use date + amount + occurrence.

No Outstanding sheet. Prior-month items get forgotten or double counted.

Overwriting formulas. Use explicit override cells or text, and keep them visible.

One template per year. Keep one file per month. It's your audit trail.

Older versions of Excel

The formulas above work in Excel 2010 and later, since they use COUNTIF, COUNTIFS, SUMIFS and structured references, all of which have been around for years. The only thing that can trip up older versions is the occurrence counter's expanding range, INDEX([Date],1):[@Date], which works in tables but can be slow on very large sheets. If performance suffers, add a helper column with a running count, or sort by date and amount first and use a simpler counter that compares each row with the one above. Google Sheets supports equivalent functions, though structured table references need converting to ordinary ranges.

When to use software instead

If your accounting software has a reconciliation tool, use it for the official reconciliation; it ties into the ledger and locks reconciled items. The Excel template is still useful for accounts outside the software, for investigating stubborn differences, for catch-up projects, and for businesses that keep their books in spreadsheets. For cards, the same structure works with a few tweaks; see our credit card reconciliation guide.

Our take

A one-page "add this, subtract that" template explains reconciliation. A line-matching template actually does it. Build the five sheets once, add the self-check and the occurrence-counter key, and every month becomes paste, check, fix the amber rows, save.

Troubleshooting the template

The difference cell shows a tiny amount like 0.00000001. Wrap the difference formula in ROUND(…,2). Floating point arithmetic in spreadsheets produces these tiny remainders, and an unrounded check will flag a reconciliation that's actually fine.

Formulas stop at row 500. If you used fixed ranges, a busy month overflows them. Convert the data areas to Excel Tables so formulas follow the data automatically.

Last month's outstanding items vanish. If you copy the template and clear everything, you lose the carry-forward list. Keep the Outstanding tab separate from the data you clear, and copy uncleared items forward deliberately.

Someone overwrote a formula. Protect the formula cells (Review > Protect Sheet, leaving the data entry ranges editable). It's a two-minute job that prevents a lot of confusion.

Two people edit at once. Use a shared workbook in OneDrive or SharePoint with co-authoring, or agree that only one person reconciles each account.

Edge cases the template should handle

• A month with no activity. The template should still produce a reconciliation: opening equals closing, and the books agree.
• Negative bank balances. Overdrawn accounts make the bank balance negative; check that your sign logic still works.
• Interest and fees only on the bank side. These belong in "items not in books" and should be recorded afterward.
• A transfer in transit between two of your accounts. It's outstanding on one account's reconciliation and in transit on the other's.

A second worked example

A treasurer's template showed a difference of 0.01 for three months running. Each time, she adjusted for it. On the fourth month, she rounded the bank data column and the difference disappeared: a converted statement had amounts like 45.004 from a currency conversion line. Rounding at import, not adjusting at the end, was the real fix, and she reversed the three small adjustments.

Mini checklist when setting up the template

1. Data areas as named Tables.
2. Difference cell rounded.
3. Outstanding tab kept between months.
4. Formula cells protected.
5. Sign-off block with names and dates.

FAQ

What should a bank reconciliation template include?

At minimum: statement and book balances, statement transactions, book transactions, a list of outstanding items from prior periods, and a summary comparing adjusted bank and adjusted book balances, with a check that the difference is zero.

How do I match transactions in Excel?

Build a key from the date and amount (plus an occurrence counter for repeated amounts) on both sheets, then use COUNTIF to see whether each key exists on the other side.

What about checks that clear weeks later?

Keep them on an Outstanding sheet and match them by check number and amount rather than date. Each month, mark which ones cleared.

Why does my template show a difference when everything looks matched?

Check manual overrides, the statement self-check, and the outstanding list. A wrong override or an item counted both as outstanding and as matched is a common cause.

Should I keep one file for the year?

We recommend one file per month, saved with the statement. It creates a clear audit trail and keeps each reconciliation independent.

Is Excel reconciliation enough if I use accounting software?

Use the software's reconciliation for the official record. Excel is useful for investigating differences, accounts outside the software, and catch-up work.