How to Automate Bank Reconciliation in Excel (Step-by-Step VBA Guide)

If you're an accountant who spends hours every month matching bank statement lines against your general ledger in Excel, you already know the pain. You export a CSV from the bank, open your GL report in another tab, and start the tedious, error-prone process of line-by-line matching. Three hours later, your eyes are blurry, and you're still hunting for a $14.50 discrepancy.

It doesn't have to be this way. Excel VBA can automate bank reconciliation so the entire process takes seconds instead of hours. In this guide, I'll walk you through the exact 5-step approach, including a free VBA code snippet you can use right now.

Why Manual Bank Reconciliation Is Costing You More Than Time

Manual reconciliation isn't just slow — it's risky. Here's what most accountants deal with every month:

• 3-5 hours per reconciliation spent on repetitive matching
• Transposition errors from eyeballing numbers across sheets
• Different bank formats every time you switch banks or accounts
• Audit anxiety because it's hard to prove your manual process was thorough
• Month-end bottleneck that delays the close and frustrates your team

The good news? You don't need expensive reconciliation software. If you already use Excel, VBA macros can handle this for you — and you don't need to be a programmer to use them.

The 5-Step Process to Automate Bank Reconciliation in Excel

Here's the high-level approach. We'll break down the key step (matching logic) with actual VBA code below.

Free VBA Code: Simple Bank Reconciliation Matcher

Here's a working VBA snippet that handles the core matching logic from Step 3. Open the VBA Editor in Excel (Alt + F11), insert a new module, and paste this code:

Sub SimpleReconciliation()
    Dim wsBank As Worksheet, wsGL As Worksheet
    Dim bankRow As Long, glRow As Long
    Dim bankLast As Long, glLast As Long
    Dim bankAmt As Double, glAmt As Double
    Dim matched As Boolean

    Set wsBank = Sheets("BankStatement")
    Set wsGL = Sheets("GLRecords")

    bankLast = wsBank.Cells(wsBank.Rows.Count, 1).End(xlUp).Row
    glLast = wsGL.Cells(wsGL.Rows.Count, 1).End(xlUp).Row

    ' Loop through each bank transaction
    For bankRow = 2 To bankLast
        bankAmt = wsBank.Cells(bankRow, 3).Value  ' Column C = Amount
        matched = False

        ' Search for matching amount in GL
        For glRow = 2 To glLast
            glAmt = wsGL.Cells(glRow, 3).Value

            If Abs(bankAmt - glAmt) < 0.01 _
               And wsGL.Cells(glRow, 4).Value <> "MATCHED" Then
                ' Mark both as matched
                wsBank.Cells(bankRow, 4).Value = "MATCHED"
                wsGL.Cells(glRow, 4).Value = "MATCHED"
                matched = True
                Exit For
            End If
        Next glRow

        ' Highlight unmatched bank transactions
        If Not matched Then
            wsBank.Cells(bankRow, 4).Value = "UNMATCHED"
            wsBank.Rows(bankRow).Interior.Color = RGB(255, 230, 230)
        End If
    Next bankRow

    MsgBox "Reconciliation complete! Check for UNMATCHED items."
End Sub

What This Code Does

• Reads every transaction from your BankStatement sheet
• Compares each amount against every GL entry (with a $0.01 tolerance for rounding)
• Marks matched rows in both sheets with "MATCHED" in column D
• Highlights unmatched bank rows in red so they stand out
• Prevents double-matching by skipping already-matched GL entries

This basic version matches on amount only. It works well for simple reconciliations where amounts are unique enough to identify transactions.

Limitations of This Basic Approach (And How to Solve Them)

The snippet above is a solid starting point, but real-world reconciliation gets more complex:

• Duplicate amounts — multiple transactions for $50.00 will match incorrectly unless you also match on date or description
• Different bank CSV formats — Chase, Wells Fargo, and Bank of America all export differently. You need column auto-detection.
• Multi-to-one matching — sometimes three small bank entries map to one GL journal entry
• Date tolerance — bank posting dates often differ by 1-3 days from your GL dates
• Summary reporting — you need a clean output sheet that's audit-ready

Solving all of these takes the macro from 30 lines to about 200 lines — plus a pre-built template workbook with the right sheet structure, formatting, and error handling.

Start Saving Hours This Month

Bank reconciliation is one of those tasks that feels like it has to be manual — until you automate it. The VBA snippet above will get you started today. Once you see how much faster it is, you'll wonder why you didn't do this years ago.

If you want the complete, production-ready solution with all the edge cases handled — date matching, bank format auto-detection, multi-to-one matching, and a clean audit trail — grab the bundle here. It takes 15 minutes to set up and works every month after that.