How to Do a Bank Reconciliation in Excel (2026 Step-by-Step + Free Template)

You can do a bank reconciliation in Excel in five steps: import the bank statement, import the general ledger, use XLOOKUP (or VLOOKUP) to match by amount and date, highlight unmatched rows with conditional formatting, and post adjusting entries for any bank-only items. This guide gives you the exact formulas, a free template, and the point at which Excel breaks down and you should switch to dedicated software.

What you need before you start

• Your bank statement exported as CSV or XLSX (every bank supports this — log in, choose the date range, export).
• Your general ledger cash account exported from QuickBooks, Xero, Sage, or your accounting system, for the same date range.
• Excel 365 or Excel 2021+ (for XLOOKUP; older versions can use VLOOKUP).
• 10–60 minutes, depending on transaction volume.

Step 1: Set up your workbook

Create three sheets:

• Bank — paste your bank statement here. Standardise columns: Date, Description, Amount (one signed column, deposits positive, withdrawals negative).
• GL — paste your ledger cash account. Same columns: Date, Description, Amount.
• Recon — your reconciliation summary.

Step 2: Add a Match column with XLOOKUP

On the Bank sheet, add a column Match in column D. Use this formula in D2 and copy down:

=IFERROR(XLOOKUP(C2, GL!C:C, GL!A:A, "UNMATCHED", 0), "UNMATCHED")

This finds the bank row's amount in the GL's amount column and returns the matching GL date. Anything that returns UNMATCHED needs investigation.

Mirror it on the GL sheet:

=IFERROR(XLOOKUP(C2, Bank!C:C, Bank!A:A, "UNMATCHED", 0), "UNMATCHED")

Step 3: Highlight unmatched rows

Select column D on each sheet → Home → Conditional Formatting → New Rule → 'Format only cells that contain' → Cell value = "UNMATCHED" → fill with red. Now your eye goes straight to the problem rows.

Step 4: Build the reconciliation summary

On the Recon sheet:

Bank statement balance       =SUM(Bank!C:C)
Less: outstanding cheques    =-SUMIFS(GL!C:C, GL!D:D, "UNMATCHED", GL!C:C, "<0")
Add: deposits in transit     =SUMIFS(GL!C:C, GL!D:D, "UNMATCHED", GL!C:C, ">0")
= Adjusted bank balance

Book balance                 =SUM(GL!C:C)
Add: bank-only credits       =SUMIFS(Bank!C:C, Bank!D:D, "UNMATCHED", Bank!C:C, ">0")
Less: bank-only debits       =-SUMIFS(Bank!C:C, Bank!D:D, "UNMATCHED", Bank!C:C, "<0")
= Adjusted book balance

The two adjusted balances must equal. If they don't, you have an error to find.

Step 5: Post the adjusting journal entries

Anything that appeared on the bank but not in the books — bank fees, interest income, NSF cheques, direct debits — needs a journal entry in your accounting system. Outstanding cheques and deposits in transit do not need entries (they're already in the books) and will clear in a future period.

Where Excel breaks down

Excel reconciliation works for under 200 transactions per month and a single account. It falls apart when:

• You have many-to-one matches — e.g. a single deposit on the bank corresponds to three sales receipts in the GL. XLOOKUP can't handle this.
• Descriptions don't match exactly — "AMZN MKTP US" on bank vs "Amazon Marketplace" in books. You need fuzzy matching.
• You have multiple bank accounts. Now you're maintaining a workbook per account, per month — file management nightmare.
• You need an audit trail. Excel doesn't tell you who changed what when.
• You reconcile for clients. Sharing workbooks via email is slow and error-prone.

At those points, switch to a tool like BankReconPro — it handles all five edge cases out of the box, and the cheapest plan ($29/mo) costs less than one hour of bookkeeper time.