5 Excel VBA Macros Every Accountant Should Know (With Free Code)

VBA isn't just for developers — it's the most underused tool in every accountant's Excel installation. While your colleagues spend hours on repetitive formatting, matching, and consolidation, a few lines of VBA code can do the same work in seconds.

The five macros below are ones I've seen save real accountants real time — anywhere from 3 to 10 hours per week. Each one includes actual working VBA code you can paste into Excel right now. Open the VBA Editor (Alt + F11), insert a new module, and you're running.

No programming experience required. If you can write an Excel formula, you can use these macros.

Macro 1: Auto-Format Bank Statement Imports

Every accountant knows the drill: you export a CSV from the bank, open it in Excel, and spend 15 minutes cleaning it up. Column widths are wrong, dates aren't formatted, amounts are plain text, and there are no borders or headers. This macro does all of that formatting in one click.

Sub FormatBankImport()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Dim lastCol As Long: lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Dim rng As Range: Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

    ' Auto-fit columns and format header row
    rng.Columns.AutoFit
    ws.Rows(1).Font.Bold = True
    ws.Rows(1).Interior.Color = RGB(45, 90, 61)
    ws.Rows(1).Font.Color = RGB(255, 255, 255)

    ' Add borders to all data
    rng.Borders.LineStyle = xlContinuous
    rng.Borders.Weight = xlThin

    ' Format amount column (assumes Column C) as currency
    ws.Range(ws.Cells(2, 3), ws.Cells(lastRow, 3)).NumberFormat = "#,##0.00"
End Sub

What It Does

Detects the data range automatically, bolds and colors the header row, adds thin borders to all cells, auto-fits column widths, and formats the amount column as currency. Run it once after any bank CSV import and your data is immediately readable.

Want the full version that auto-detects date/amount columns across different bank formats? See our Bank Reconciliation course.

Macro 2: Highlight Duplicate Entries Across Sheets

Duplicate entries are one of the most common accounting errors — and the hardest to catch manually when data spans multiple sheets. This macro compares a column across two sheets and highlights any duplicates in yellow.

Sub HighlightDuplicatesAcrossSheets()
    Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
    Dim cell1 As Range, cell2 As Range
    Dim lastRow1 As Long: lastRow1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    Dim lastRow2 As Long: lastRow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row

    For Each cell1 In ws1.Range("A2:A" & lastRow1)
        For Each cell2 In ws2.Range("A2:A" & lastRow2)
            If cell1.Value = cell2.Value And cell1.Value <> "" Then
                cell1.Interior.Color = RGB(255, 255, 153)
                cell2.Interior.Color = RGB(255, 255, 153)
            End If
        Next cell2
    Next cell1
    MsgBox "Duplicate check complete. Duplicates highlighted in yellow."
End Sub

What It Does

Loops through Column A of both sheets, compares every value, and highlights matches in yellow on both sheets. Great for catching duplicate invoice numbers, transaction IDs, or vendor payments that were accidentally entered twice. Change "A2:A" to any column that holds your comparison values.

Macro 3: One-Click Sheet Consolidation Summary

If you manage budgets or reports across multiple departments, you probably have a workbook with 10+ sheets that need to be summed into one master view. This macro creates a consolidation summary by pulling a specific cell from every sheet into a single table.

Sub ConsolidateSummary()
    Dim ws As Worksheet, summary As Worksheet
    Dim outputRow As Long: outputRow = 2

    ' Create or clear Summary sheet
    On Error Resume Next
    Set summary = Sheets("Summary")
    If summary Is Nothing Then Set summary = Sheets.Add(After:=Sheets(Sheets.Count))
    On Error GoTo 0
    summary.Name = "Summary"
    summary.Cells.Clear
    summary.Range("A1").Value = "Sheet Name"
    summary.Range("B1").Value = "Total"
    summary.Rows(1).Font.Bold = True

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            summary.Cells(outputRow, 1).Value = ws.Name
            summary.Cells(outputRow, 2).Value = ws.Range("B10").Value ' Adjust cell ref
            outputRow = outputRow + 1
        End If
    Next ws
    summary.Columns.AutoFit
End Sub

What It Does

Creates a "Summary" sheet (or clears it if it exists), then loops through every other sheet and pulls the value from cell B10 into a two-column table. Change B10 to whatever cell holds your total — for instance, D25 if that's where each department's net income sits.

Need to consolidate full data ranges (not just one cell per sheet) with automatic column mapping? See our Multi-Sheet Consolidation course.

Macro 4: Auto-Generate Aging Reports from AR Data

Accounts receivable aging is critical for cash flow management, but building the aging buckets manually is tedious. This macro reads an AR list with invoice dates and amounts, then categorizes each invoice into standard aging buckets.

Sub GenerateAgingReport()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Dim i As Long, age As Long

    ' Add aging header in Column D
    ws.Range("D1").Value = "Aging Bucket"
    ws.Range("D1").Font.Bold = True

    For i = 2 To lastRow
        age = Date - ws.Cells(i, 2).Value ' Column B = Invoice Date
        Select Case age
            Case Is <= 30: ws.Cells(i, 4).Value = "Current"
            Case 31 To 60: ws.Cells(i, 4).Value = "31-60 Days"
            Case 61 To 90: ws.Cells(i, 4).Value = "61-90 Days"
            Case Is > 90
                ws.Cells(i, 4).Value = "90+ Days"
                ws.Cells(i, 4).Font.Color = RGB(200, 0, 0)
        End Select
    Next i
    MsgBox "Aging report generated in Column D."
End Sub

What It Does

Calculates the age of each invoice by subtracting the invoice date (Column B) from today, then writes the aging bucket (Current, 31–60, 61–90, 90+) into Column D. Invoices over 90 days are flagged in red. This assumes Column A is the customer/invoice name, Column B is the invoice date, and Column C is the amount. Adjust the column references to match your data layout.

Macro 5: Quick Variance Analysis Formatter

Budget-vs-actual variance analysis is a monthly staple. This macro takes a sheet with budget amounts in one column and actual amounts in another, calculates the variance and percentage, and color-codes unfavorable results.

Sub VarianceAnalysis()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Dim i As Long, variance As Double

    ' Headers: A=Account, B=Budget, C=Actual, D=Variance, E=Var %
    ws.Range("D1").Value = "Variance": ws.Range("E1").Value = "Var %"
    ws.Range("D1:E1").Font.Bold = True

    For i = 2 To lastRow
        variance = ws.Cells(i, 3).Value - ws.Cells(i, 2).Value ' Actual - Budget
        ws.Cells(i, 4).Value = variance
        ws.Cells(i, 4).NumberFormat = "#,##0.00"

        If ws.Cells(i, 2).Value <> 0 Then
            ws.Cells(i, 5).Value = variance / ws.Cells(i, 2).Value
            ws.Cells(i, 5).NumberFormat = "0.0%"
        End If

        ' Red highlight for unfavorable variances over 10%
        If ws.Cells(i, 2).Value <> 0 Then
            If Abs(variance / ws.Cells(i, 2).Value) > 0.1 Then
                ws.Cells(i, 4).Interior.Color = RGB(255, 220, 220)
                ws.Cells(i, 5).Interior.Color = RGB(255, 220, 220)
            End If
        End If
    Next i
End Sub

What It Does

Calculates the dollar variance (Actual minus Budget) and percentage variance for each line item. Any variance exceeding 10% in either direction gets a red background highlight so it jumps out during review. The layout assumes Column A is the account name, Column B is the budget, and Column C is the actual — a standard format for most management reports.

How to Use These Macros in Excel

If you've never used VBA before, here's the quick setup:

1. Open your Excel workbook and press Alt + F11 to open the VBA Editor
2. In the left panel, right-click your workbook name and select Insert → Module
3. Paste the code into the module window
4. Close the VBA Editor and press Alt + F8 to see your macros
5. Select the macro name and click Run

Save your workbook as .xlsm (macro-enabled workbook) to keep the macros for next time.

Start Automating Today

These five macros cover the accounting tasks I see automated most often: formatting imports, catching duplicates, consolidating sheets, aging receivables, and analyzing variances. Each one is a standalone time-saver, but together they can transform how you work in Excel.

Copy the code, try it on a test workbook, and see the difference. Once you experience VBA automation firsthand, you'll start seeing opportunities to automate everywhere — month-end close, journal entries, financial reporting, and more.

If you want the complete, production-ready versions with full error handling, multi-format support, and downloadable templates — grab the bundle here. It includes both video walkthroughs plus code you can use immediately.