How to Reconcile Bank Statements in Excel (Step-by-Step)

Bank reconciliation in Excel is still the default workflow for thousands of bookkeepers. It works — but it is slow, error-prone, and painful to repeat every month. This guide walks you through the complete process: from downloading your bank CSV to producing a clean reconciled report.

What You Need Before You Start

• Your bank statement exported as CSV (most banks: Sign in → Accounts → Download Activity → CSV)
• Your ledger or POS report for the same date range, also as CSV or XLSX
• Excel 2016 or later (XLOOKUP is available in Microsoft 365 — VLOOKUP works in any version)
• About 30–90 minutes depending on transaction volume

Step 1: Clean and Standardise Both Files

Open your bank CSV in Excel. Before you do anything else, standardise three things across both files:

1. Date format — select the date column, press Ctrl+1, and force a consistent format (YYYY-MM-DD works best for sorting). Bank exports use MM/DD/YYYY; some POS systems use YYYY-MM-DD or DD/MM/YYYY. Make them match.
2. Amount sign convention — most bank exports use negative values for debits and positive for credits. Some credit card exports (Amex, Discover, Capital One) use the reversed convention. Check your first few rows.
3. Column names — rename headers to short, clean names: Date, Amount, Description. This makes your VLOOKUP formulas readable.

Step 2: Set Up Your Reconciliation Sheet

Create a new sheet called "Recon". Paste your bank transactions in columns A–C (Date, Amount, Description) starting at row 2. Paste your ledger transactions in columns E–G (Date, Amount, Description). Add a header row with labels in row 1.

Step 3: Match Transactions with VLOOKUP (or XLOOKUP)

In column D (next to your bank data), add a match formula. The goal is to find the corresponding ledger row for each bank row. Matching on Amount alone is unreliable — use Amount + Date rounded to ±1 day.

A simple Amount-only match formula in D2:

This returns the matched amount or "UNMATCHED". For more precision, use a helper column that concatenates Date and Amount (e.g. =TEXT(A2,"YYYY-MM-DD")&"|"&B2) in both sheets, then VLOOKUP on that combined key.

Step 4: Flag Unmatched Rows with Conditional Formatting

Select column D, go to Home → Conditional Formatting → Highlight Cell Rules → Equal To → type "UNMATCHED" → choose red fill. Now all unmatched bank transactions are instantly visible. Do the same on the ledger side: add a VLOOKUP in column H that matches back to your bank data.

Step 5: Investigate Every Unmatched Row

Each UNMATCHED row needs a decision. Common causes:

• Timing differences — the bank posted a transaction one day earlier or later than the ledger recorded it. Check ±1 day.
• Description mismatch — the bank says "AMZN Mktp US" and the ledger says "Amazon". The amounts match; the names do not. These are true matches — mark them manually.
• Missing transaction — a transaction is in the bank but not in the ledger (or vice versa). These are genuine exceptions that need a journal entry.
• Bank fees — monthly maintenance fees, wire fees, and NSF charges rarely appear in a POS ledger. Add them.
• Duplicate entries — the same transaction entered twice in the ledger.

Step 6: Reconcile the Closing Balance

Once all rows are matched or investigated, verify your closing balance:

If this matches your bank statement's ending balance, you are reconciled. If it does not, you have a data entry error or a missing transaction — start by checking the difference amount against your unmatched rows.

Common Excel Reconciliation Errors (and Fixes)

• "UNMATCHED" on everything — usually a column mapping error. Check that both Amount columns are numbers, not text. Select the column, Data → Text to Columns → Finish to force numeric conversion.
• Off by $0.01 — rounding error. Excel's floating-point arithmetic can produce tiny rounding differences. Use ROUND(amount,2) in your VLOOKUP key.
• Dates not matching — one file has American dates (MM/DD/YYYY) and the other has ISO dates (YYYY-MM-DD). Standardise before matching.
• Duplicate matches — VLOOKUP always returns the first match. If you have two transactions of the same amount on the same date, add a sequence number to your match key.

How Long Should Excel Reconciliation Take?

For a single account with fewer than 200 transactions: 30–45 minutes. For a client with 500+ transactions per month across multiple accounts: 2–4 hours. High-volume accounts with complex descriptions (e.g. Square POS batches, PayPal) take longer because the description matching is unreliable.