Why Excel Is Still the Go-To for Bank Reconciliation
Despite the rise of cloud accounting software, millions of accountants, bookkeepers, and small business owners still reconcile their bank accounts in Excel. The reason is flexibility — Excel lets you build exactly the reconciliation workflow that fits your books, use formulas you already know, and avoid monthly software subscription fees.
The traditional bottleneck has always been getting the bank statement data into Excel. Banks issue PDFs, not spreadsheets. Manually typing transactions from a PDF is slow, error-prone, and completely kills any efficiency gain from using Excel formulas. Bank Statement Engine solves this — convert your PDF to Excel in seconds, then apply your reconciliation formulas to clean, structured data.
How to Auto Reconcile in Excel — Step by Step
1
Download PDF
Download your bank statement PDF from internet banking
2
Convert to Excel
Upload here — get a clean .xlsx with Date, Description, Debit, Credit, Balance
3
Paste into Workbook
Paste bank transactions into "Bank Statement" sheet in your reconciliation workbook
4
Apply VLOOKUP
Use VLOOKUP to match each bank row against your "Ledger" sheet by reference or amount
5
Flag Exceptions
Use conditional formatting to highlight unmatched transactions in red
6
Clear & Reconcile
Investigate red rows, add missing entries, confirm closing balances match
The VLOOKUP Formula for Bank Reconciliation
The core of Excel bank reconciliation is the VLOOKUP function. Here is the exact formula to use, with an explanation of each part:
=IFERROR(VLOOKUP(C2, Ledger!$C:$D, 2, 0), "NOT FOUND")
- C2 — The transaction reference/description from your bank statement (the lookup value)
- Ledger!$C:$D — Your ledger sheet, columns C (reference) to D (amount). Use $ signs to lock the range.
- 2 — Return the 2nd column of the range (the amount from your ledger)
- 0 — Exact match only (never use 1/TRUE for financial data)
- IFERROR(..., "NOT FOUND") — If no match found, show "NOT FOUND" instead of an error
In column E next to your bank statement data, add a reconciliation check:
=IF(D2=E2, "MATCHED", IF(E2="NOT FOUND", "MISSING IN LEDGER", "AMOUNT MISMATCH"))
This gives you three clear statuses: MATCHED (all good), MISSING IN LEDGER (transaction exists in bank but not in your books), or AMOUNT MISMATCH (transaction exists but amounts differ).
Using SUMIF When References Don't Match
VLOOKUP fails when your bank reference numbers don't match your ledger references — which is common with UPI payments, NEFT, and RTGS. In these cases, use SUMIF to match by date and amount instead:
=SUMIF(Ledger!$A:$A, A2, Ledger!$D:$D)
This sums all ledger amounts on the same date as the bank transaction. If the result equals your bank debit/credit, the transaction is reconciled.
Setting Up Your Excel Reconciliation Template
A well-structured bank reconciliation workbook has three sheets:
Sheet 1: Bank Statement
Paste the converted bank statement data here. Columns: A = Date, B = Description, C = Reference, D = Debit, E = Credit, F = Balance. Add column G for VLOOKUP result, column H for status (MATCHED/MISSING/MISMATCH), column I for "Cleared" checkbox.
Sheet 2: Ledger
Your internal books of accounts. Matching columns: A = Date, B = Description, C = Reference, D = Amount, E = Type (DR/CR). This is the data source your VLOOKUP formula searches.
Sheet 3: Reconciliation Summary
A summary showing: Opening balance per bank, Opening balance per books, Total debits (bank), Total credits (bank), Unreconciled items, Closing balance per bank, Closing balance per books, Difference (should be zero when fully reconciled).
Opening Balance (Bank) =BankStatement!F2
+ Total Credits (Bank) =SUM(BankStatement!E:E)
- Total Debits (Bank) =SUM(BankStatement!D:D)
= Closing Balance (Bank) =BankStatement!F[last row]
Difference (should = 0) =ClosingBank - ClosingLedger
Power Query: The Advanced Approach
For larger volumes of transactions (500+ per month), Power Query is more powerful than VLOOKUP. Here is the approach:
- Go to Data → Get Data → From Table/Range and load your bank statement as a Power Query table
- Load your ledger as a second Power Query table
- In Power Query Editor, click Home → Merge Queries
- Join the two tables on Reference Number (or Date + Amount for fuzzy matching)
- Expand the merged table to show matched/unmatched rows
- Load the result back to Excel as a new sheet — your reconciliation report
Power Query handles messy bank descriptions far better than VLOOKUP, and the merge refreshes automatically when you update your data.
Comparison: Excel Methods for Bank Reconciliation
Frequently Asked Questions
How do I do automatic bank reconciliation in Excel?
Convert your bank statement PDF to Excel using Bank Statement Engine, paste the transactions into your reconciliation workbook, and use VLOOKUP to match each bank row against your ledger. Use IFERROR to catch unmatched items and conditional formatting to highlight exceptions.
What Excel formula is used for bank reconciliation?
VLOOKUP is the most common: =IFERROR(VLOOKUP(C2, Ledger!$C:$D, 2, 0), "NOT FOUND"). Use SUMIF when matching by date and amount instead of reference number. Power Query Merge is the best approach for high volumes.
What is a bank reconciliation template in Excel?
A pre-built workbook with Bank Statement, Ledger, and Reconciliation Summary sheets. It includes VLOOKUP formulas that automatically match transactions and show a net difference (which should be zero when fully reconciled).
Can I automate bank reconciliation without accounting software?
Yes. Convert your bank statement PDF to Excel, set up VLOOKUP or Power Query matching formulas, and reconcile entirely within Excel or Google Sheets. No paid software needed.
What is the VLOOKUP trick for matching bank transactions?
=IFERROR(VLOOKUP(C2, Ledger!$C:$D, 2, 0), "") in a new column. If it returns a value matching your bank amount, the transaction is reconciled. If blank, it is missing from your ledger.
How long does it take to reconcile in Excel?
With converted bank data and VLOOKUP matching set up, about 20–30 minutes for 200 monthly transactions. Manual PDF entry alone takes 2–3 hours — the converter eliminates that entirely.
Does the bank statement converter work with Google Sheets?
Yes. Download the Excel file, then import it into Google Sheets. All VLOOKUP and SUMIF formulas work identically in Google Sheets.
What columns does the Excel output include?
Date, Description/Narration, Reference Number, Debit Amount, Credit Amount, and Running Balance — exactly the columns needed for bank reconciliation.
Can I use Power Query for bank reconciliation in Excel?
Yes. Load your converted bank statement and ledger as Power Query tables, then use Merge Queries to join them. This is faster and more powerful than VLOOKUP for high-volume reconciliation.
Which banks does the PDF converter support?
10,000+ banks worldwide — HDFC, ICICI, SBI, Barclays, HSBC, Chase, Wells Fargo, ANZ, Westpac, and thousands more.
Ready to Reconcile Your Bank Statement in Excel?
Convert your bank statement PDF to Excel — free, instant, no signup required
Convert PDF to Excel Now →