Bank Reconciliation in Excel

Step-by-step guide with clean statement data

Key takeaways
  • The fastest reconciliations start with clean, validated statement data.
  • Convert your PDF statement to a structured Excel file, then match it to your ledger.
  • Use a simple matching workflow in Excel to identify missing, duplicated, or misposted items.
  • Close with a reconciliation summary you can save for audit and reporting.
Convert your statement to Excel

Clean data • Validated • Ready for reconciliation

How do you do a bank reconciliation in Excel?

A bank reconciliation compares your bank statement transactions against your internal records (cashbook, accounting system, or ledger). In Excel, you import both datasets, match transactions by amount and date, and then investigate any differences until the balances agree.

If your starting point is a PDF statement, the first step is to convert it into a clean spreadsheet. ExtractaLedger converts bank statement PDFs into validated Excel workbooks so you can reconcile with less manual cleanup.

What you need before you start

You will need:

  • Your bank statement for the period (PDF or exported statement)
  • Your internal records for the same period (ledger export, cashbook, or accounting transactions)
  • The opening balance (from your prior month close)

Optional, but useful:

  • A list of outstanding payments and deposits
  • Bank fees and interest for the period

Step 1: Convert your bank statement PDF into clean Excel

If your statement is already in Excel or CSV, you can skip this section.

If it is in PDF:

  1. Upload the statement to ExtractaLedger
  2. Convert to XLSX
  3. Check the Validation tab (PASS or FAIL)
  4. Review the Issues tab for flagged rows
  5. Use the Transactions tab as your statement dataset
Why this matters

Reconciliation depends on correct signs, correct balances, and no missing lines. Validation catches these problems before you start matching.

Helpful links:

Step 2: Prepare your two tables in Excel

You need two clean tables:

  1. Bank statement transactions (from the converted XLSX)
  2. Ledger transactions (your internal records)

Recommended columns for matching

Column Bank statement Ledger
Date Transaction date Posting date (or transaction date)
Description Statement description Ledger memo or payee
Amount Normalised debit or credit value Signed amount
Reference If available Reference, invoice, or receipt
Balance Optional for reconciliation Not required

If your ledger export has separate debit and credit columns, create a single Amount column to match against the statement.

Step 3: Standardise amounts and dates

Inconsistent formats cause false mismatches.

Dates

  • Convert both date columns to true Excel dates (not text)
  • Keep the same date format across both tables

Amounts

  • Ensure the bank and ledger amounts use the same sign convention
  • If your ledger uses separate debit/credit columns, unify them:
    • Debit as negative
    • Credit as positive

Step 4: Match transactions (a simple, reliable workflow)

There are many ways to match in Excel. This is a practical approach that works for most businesses.

A) Start with exact matches by amount

  1. Sort both tables by Amount
  2. Identify obvious exact matches
  3. Mark matched rows as "Cleared"

B) Add a date window for matching

For many businesses, a bank transaction may clear 1 to 3 days after the ledger entry. Use a small date window:

  • Match amounts
  • Allow date variance (for example ±3 days)
  • Confirm descriptions or references where possible

C) Handle duplicates and split payments

If amounts match but appear multiple times:

  • Check references
  • Check whether one payment was split in the ledger
  • Check whether the bank grouped fees

Step 5: Identify and resolve differences

Unmatched rows usually fall into a few categories.

Common reasons for unmatched bank rows

  • Bank fees not recorded in the ledger
  • Interest received not recorded
  • Direct debits or card payments not captured internally
  • Timing differences (outstanding items)
  • Chargebacks or reversals

Common reasons for unmatched ledger rows

  • Payments not yet cleared in the bank (outstanding cheques or EFTs)
  • Duplicate ledger entry
  • Wrong amount posted
  • Posted to the wrong period or account

Step 6: Reconcile to the correct closing balance

A simple reconciliation summary can be kept in Excel:

Item Amount
Bank statement closing balance X
Less: outstanding payments (X)
Add: outstanding deposits X
Less: fees not yet recorded (X)
Add or less: corrections X
Reconciled book balance X

Your goal is for the reconciled book balance to match your ledger cash balance for the period.

Step 7: Save your reconciliation evidence

If you are reconciling for audit or compliance, save:

  • The bank statement XLSX output
  • The ledger export
  • Your reconciliation workbook
  • A list of outstanding items at month end

Start with clean, validated data

Convert your statement to Excel

Tips to make reconciliations faster every month

  • Reconcile weekly rather than monthly if volume is high
  • Record fees and interest as you see them
  • Use consistent references on payments (invoice numbers, customer IDs)
  • Keep a standard reconciliation workbook template
  • Convert statements with validation so you do not waste time debugging a messy table

Frequently Asked Questions

Learn more

Ready to Start Your Reconciliation?

Convert your PDF statement into clean, validated Excel data.

Convert your statement to Excel See how validation works