Bank reconciliation is the process of comparing your internal accounting records against the bank's official statement to identify and explain any differences. It's one of the most fundamental bookkeeping controls — yet it's also one of the most commonly done poorly or skipped entirely by small businesses.
Done correctly in Excel, monthly bank reconciliation for a typical small business account (100–200 transactions) takes 20–40 minutes. This guide walks through the entire process, from building the workbook structure to using Excel formulas to match transactions and identify every discrepancy.
What Is Bank Reconciliation and Why It Matters
Bank reconciliation compares two independent records of the same cash account: your bank's version (the statement) and your company's version (the general ledger or accounting software). The goal is to confirm they agree — or to explain and resolve why they don't.
Why it matters beyond compliance: bank reconciliation catches timing differences (transactions recorded by you but not yet by the bank, or vice versa), errors (transposition mistakes, duplicate entries, wrong amounts), fraud (unauthorized withdrawals, altered check amounts), and missing transactions (bank fees, interest, returned checks you didn't record).
The IRS expects that any business claiming deductions has records that tie back to bank statements. A reconciled set of accounts is the cleanest proof that your books reflect reality.
Prerequisites: What You Need Before Starting
- Bank statement CSV: Your bank statement converted to CSV format. If you have a PDF, convert it at bankstatementtocsvfile.com first.
- Accounting records: Your general ledger cash account entries for the same period, exported from QuickBooks, Xero, Wave, or your manual spreadsheet.
- Opening balance: The confirmed ending balance from the previous month's reconciliation, which becomes this month's opening balance.
- Microsoft Excel 2016 or later (for the XLOOKUP function, you need Excel 365 or Excel 2019+; older versions can use VLOOKUP).
Building the Reconciliation Workbook
Create a new Excel workbook with three sheets:
- Sheet 1: BankStatement — Paste the CSV output from your bank statement converter here.
- Sheet 2: BookRecords — Export your accounting software's transaction list for the same period and paste it here.
- Sheet 3: Reconciliation — The summary sheet where you calculate the reconciliation balance.
BankStatement Sheet Setup
Your converted CSV should have these columns in this order:
- Column A: Date (formatted as Date, not Text)
- Column B: Description
- Column C: Amount (negative for debits/withdrawals, positive for credits/deposits)
- Column D: Balance (running balance, if your statement includes it)
BookRecords Sheet Setup
Your accounting software export should be arranged with the same column structure: Date (A), Description (B), Amount (C). If your accounting software exports separate Debit and Credit columns, add a helper column D with =IF(C2<>"",C2*-1,B2) to convert to signed amounts.
Reconciliation Sheet — Balance Summary
In the Reconciliation sheet, set up this structure in cells A1:B8:
-- Reconciliation Summary (Sheet 3) -- A1: Bank Statement Ending Balance B1: [link to last balance in BankStatement] A2: Add: Deposits in Transit B2: [manual entry or SUM formula] A3: Less: Outstanding Checks B3: [manual entry or SUM formula] A4: Add/Less: Bank Errors B4: [manual entry] A5: Adjusted Bank Balance B5: =B1+B2-B3+B4 A7: Book Balance (GL Ending Balance) B7: [from your accounting records] A8: Difference (should be zero) B8: =B5-B7
When B8 equals zero, the reconciliation is complete. Any non-zero value in B8 represents an unexplained discrepancy that needs investigation.
Using XLOOKUP/VLOOKUP to Match Transactions
The most efficient way to identify unmatched transactions is using XLOOKUP to find each bank transaction in your book records (and vice versa). Add a "Match Status" column to each sheet.
On the BankStatement Sheet (Column E)
This formula searches for each bank transaction amount in your book records. If found, it returns the corresponding description from your books. If not found, it returns "NOT FOUND."
-- XLOOKUP (Excel 365 / 2019+) -- =IFERROR(XLOOKUP(C2,BookRecords!C:C,BookRecords!B:B,"NOT FOUND"),"NOT FOUND") -- VLOOKUP (Excel 2016 and older) -- =IFERROR(VLOOKUP(C2,BookRecords!C:B,2,FALSE),"NOT FOUND")
A key limitation of amount-only matching: the same amount may appear multiple times (e.g., $50.00 might match two different transactions). For higher accuracy, match on both amount AND date:
-- Match on BOTH amount (C) AND date (A) -- =IFERROR( INDEX(BookRecords!B:B, MATCH(1, (BookRecords!C:C=C2)*(BookRecords!A:A=A2), 0)), "NOT FOUND") -- Enter with Ctrl+Shift+Enter as array formula in older Excel -- -- In Excel 365, just press Enter normally --
Conditional Formatting to Flag Unmatched Items
With the Match Status column populated, set up conditional formatting to visually highlight every unmatched transaction:
- Select the entire Match Status column (E2:E1000 or similar).
- Go to Home → Conditional Formatting → New Rule.
- Select "Use a formula to determine which cells to format."
- Enter the formula:
=E2="NOT FOUND" - Set the fill color to red (or orange for less alarm). Click Format → Fill → choose red. Click OK twice.
- Now add a second rule for matched transactions. Repeat steps 2–5 but use formula
=E2<>"NOT FOUND"and set fill to light green.
The result: a quick visual scan shows red rows (bank transactions not found in your books) and green rows (matched). Red rows are your reconciling items to investigate.
Common Discrepancies and How to Find Them
1. Outstanding Checks
Checks you wrote that appear in your books but haven't cleared the bank yet. In your BookRecords sheet, filter for check payments, then cross-reference against the BankStatement sheet. Any check in your books that doesn't appear in the bank statement is outstanding. Sum these amounts — they go in row B3 of your Reconciliation sheet (Outstanding Checks).
2. Deposits in Transit
Deposits you recorded in your books but not yet posted by the bank. Filter your BookRecords for deposits near the end of the statement period. Any that don't appear in the bank statement are in transit. Sum these for row B2 (Deposits in Transit).
3. Bank Fees and Service Charges
Filter the BankStatement sheet for rows containing "FEE", "CHARGE", "SERVICE", "MAINTENANCE" in the description. Any fee that doesn't appear in your BookRecords needs to be added to your books. These are the most common source of unresolved reconciliation differences for small businesses.
4. Duplicate Entries
Use this formula to find duplicate amounts in the same sheet:
=COUNTIF($C$2:$C$1000,C2)>1 -- Returns TRUE if the same amount appears more than once -- -- Apply as conditional formatting to highlight potential duplicates --
5. Transposition Errors
A $1,234.00 transaction recorded as $1,243.00 — the difference ($9.00) is always divisible by 9. If your reconciliation difference is divisible by 9, you almost certainly have a transposition error somewhere.
SUMIF Formula for Category Reconciliation
After matching transactions, you can use SUMIF to verify that your category totals are reasonable. This is particularly useful for tax preparation — checking that your "Meals & Entertainment" total, for example, aligns with the actual restaurant charges visible in your bank statement.
-- Sum all transactions in a specific category -- =SUMIF(D:D,"Travel",C:C) -- Where D is the Category column, C is the Amount column -- -- Sum all transactions matching a keyword in description -- =SUMIF(B:B,"*AMAZON*",C:C) -- Returns total of all Amazon transactions -- -- Sum transactions in a date range -- =SUMIFS(C:C,A:A,">="&DATE(2026,1,1),A:A,"<="&DATE(2026,1,31)) -- Returns total for January 2026 only --
Closing the Reconciliation
The reconciliation is complete when cell B8 on your Reconciliation sheet equals zero. Before finalizing:
- Document every reconciling item (outstanding checks, deposits in transit) with amount and description
- Record any book adjustments needed (bank fees not in your records, interest income, NSF charges)
- Save the workbook with the statement month in the filename (e.g., "Bank_Rec_Chase_2026-05.xlsx")
- Print or save as PDF for your records — tax authorities and lenders may request reconciliation documentation
Start with a Clean CSV — Convert Your Statement Free
You need your bank statement in CSV format before you can reconcile in Excel. Convert any PDF statement in 30 seconds — free, no signup.
Convert Bank Statement Free →Frequently Asked Questions
What is the bank reconciliation formula in Excel?
The core formula: Bank Statement Ending Balance + Deposits in Transit − Outstanding Checks ± Bank Errors = Adjusted Bank Balance. This should equal your Book Balance (general ledger cash account). In Excel: =BankEndingBalance+DepositsInTransit-OutstandingChecks+BankErrors. When Adjusted Bank Balance minus Book Balance equals zero, the reconciliation is complete.
Should I use XLOOKUP or VLOOKUP for bank reconciliation?
Use XLOOKUP if you have Excel 365 or Excel 2019+. It's cleaner: =IFERROR(XLOOKUP(C2,BookRecords!C:C,BookRecords!B:B,"NOT FOUND"),"NOT FOUND"). For Excel 2016 and earlier, use VLOOKUP: =IFERROR(VLOOKUP(C2,BookRecords!C:B,2,FALSE),"NOT FOUND"). For best accuracy, match on both amount and date using an INDEX/MATCH array formula.
What are the most common bank reconciliation discrepancies?
The five most common: (1) Outstanding checks — issued but not yet cleared; (2) Deposits in transit — recorded by you but not yet posted by the bank; (3) Bank fees — service charges, NSF fees, wire fees not yet in your books; (4) Interest income — bank interest not yet recorded; (5) Duplicate entries — same transaction entered twice in either your records or the bank feed.
How do I use SUMIF for category reconciliation?
Use =SUMIF(D:D,"Travel",C:C) where D is your Category column and C is Amount. This sums all transactions tagged Travel. For keyword-based matching without a category column, use =SUMIF(B:B,"*AMAZON*",C:C) to sum all transactions where the description contains "AMAZON". The asterisk (*) is a wildcard that matches any surrounding text.
Do I need to convert my bank statement to CSV before reconciling in Excel?
Yes — you cannot run Excel formulas against a PDF. You need the transactions in a structured spreadsheet format. Convert your bank statement PDF at bankstatementtocsvfile.com to get a clean CSV with Date, Description, and Amount columns. Then paste it into your reconciliation workbook as described in this guide.
How long does Excel bank reconciliation take?
With a properly set up workbook and converted CSV data, a typical small business monthly reconciliation (100–200 transactions) takes 20–40 minutes. If you're spending more than an hour, the bottleneck is usually data quality: misformatted dates preventing XLOOKUP matches, description mismatches, or amount sign inconsistencies between your bank CSV and accounting software export.