Excel Macro: Generate a Summary Automatically

Yes. In Excel for Microsoft 365 on Windows, a VBA macro can read a data table, group rows by a category, add their numeric amounts, and write the results to a summary worksheet. The example below expects categories in column A and amounts in column B, with headers in row 1; run it from the worksheet containing that data.

What an Excel summary macro can do

A summary macro automates repeated reporting work. Instead of filtering or adding each group by hand, it can scan a table, calculate totals, counts, averages, or other measures, and place the output in a consistent location. For example, a sales list with a region and a sale amount can become a compact total for each region. The same general approach works for expenses by department, hours by project, or orders by product type.

The code here produces a category-and-total summary. It reads rows beneath the header, combines matching category names without distinguishing uppercase from lowercase, and adds the amounts in column B. It skips rows with a blank category or an amount that Excel does not recognize as numeric. The macro writes values rather than formulas, so the summary is a snapshot of the source data at the time you run it. Run it again after the source changes to refresh that snapshot.

Prepare the source data

Keep the source layout simple before running the macro. Put one record on each row, use row 1 for column headings, and avoid merged cells in the data area. In this example, column A contains the grouping field and column B contains the values to add:

A1: Region; B1: Sales
A2: North; B2: 125
A3: South; B3: 80
A4: North; B4: 75

The resulting summary contains North with 200 and South with 80. The macro finds the final row by looking up column A, so each relevant data row needs a category there. It expects amounts to be actual numeric values; values stored as text may be skipped. The example does not interpret dates, remove duplicates from the source, or detect subtotals already present in the list. If the source includes pre-existing totals, those rows could be counted again.

VBA macro to total amounts by category

In the Visual Basic Editor, choose Insert > Module and paste this procedure into the new module. It creates a worksheet named Summary if one does not exist, or clears and rewrites that sheet if it does. It will not run when the active source sheet itself is named Summary.

Sub GenerateSummary()
    ' Summarize numeric values in column B by category in column A.
    Dim wsData As Worksheet
    Dim wsSummary As Worksheet
    Dim totals As Object
    Dim lastRow As Long
    Dim r As Long
    Dim outputRow As Long
    Dim category As String
    Dim amount As Double
    Dim grandTotal As Double
    Dim key As Variant

    Set wsData = ActiveSheet

    If StrComp(wsData.Name, "Summary", vbTextCompare) = 0 Then
        MsgBox "Run this macro from the source data sheet."
        Exit Sub
    End If

    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No data rows were found below the header."
        Exit Sub
    End If

    Set totals = CreateObject("Scripting.Dictionary")
    totals.CompareMode = vbTextCompare

    For r = 2 To lastRow
        category = Trim$(CStr(wsData.Cells(r, "A").Value))
        If Len(category) > 0 Then
            If IsNumeric(wsData.Cells(r, "B").Value) Then
                amount = CDbl(wsData.Cells(r, "B").Value)
                If totals.Exists(category) Then
                    totals(category) = totals(category) + amount
                Else
                    totals.Add category, amount
                End If
            End If
        End If
    Next r

    If totals.Count = 0 Then
        MsgBox "No rows with a category and numeric amount were found."
        Exit Sub
    End If

    On Error Resume Next
    Set wsSummary = wsData.Parent.Worksheets("Summary")
    On Error GoTo 0

    If wsSummary Is Nothing Then
        Set wsSummary = wsData.Parent.Worksheets.Add(After:=wsData)
        wsSummary.Name = "Summary"
    Else
        wsSummary.Cells.Clear
    End If

    wsSummary.Cells(1, 1).Value = "Category"
    wsSummary.Cells(1, 2).Value = "Total"
    outputRow = 2

    For Each key In totals.Keys
        wsSummary.Cells(outputRow, 1).Value = key
        wsSummary.Cells(outputRow, 2).Value = totals(key)
        grandTotal = grandTotal + totals(key)
        outputRow = outputRow + 1
    Next key

    wsSummary.Cells(outputRow, 1).Value = "Grand total"
    wsSummary.Cells(outputRow, 2).Value = grandTotal
    wsSummary.Range("A1:B1").Font.Bold = True
    wsSummary.Cells(outputRow, 1).Font.Bold = True
    wsSummary.Cells(outputRow, 2).Font.Bold = True
    wsSummary.Columns("A:B").AutoFit
End Sub

The example uses a late-bound Scripting.Dictionary, so you do not need to enable a separate reference in the VBA editor. The dictionary stores one running total for each category. Its text comparison setting means North, NORTH, and north are treated as the same category; the first spelling encountered is the one displayed.

Run the macro in Excel

Save a copy of the workbook before running any macro. VBA changes cannot be reversed with Ctrl+Z.
Save the working file as an Excel Macro-Enabled Workbook (*.xlsm), so the VBA project can be retained.
Open the source worksheet and confirm that its category and amount columns match the example.
Open the Visual Basic Editor. On Windows, use Developer > Visual Basic or press Alt+F11. In the editor, choose Insert > Module, paste the code, and close or switch back to Excel.
Choose Developer > Macros, select GenerateSummary, and choose Run. Check the Summary sheet and compare its totals with a few source rows.

If the Developer tab is hidden on Windows, enable it through File > Options > Customize Ribbon and select Developer. On Mac, the Developer tab can be enabled through Excel > Settings > Ribbon & Toolbar; the Visual Basic Editor is available from Developer > Visual Basic. Menu names and keyboard behavior can vary slightly by Mac keyboard and Excel release. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use the desktop Excel app for this procedure.

How the macro builds its output

The procedure starts from ActiveSheet, so the sheet selected when you run it is the source. It checks column A for the last used row, then processes records from row 2 onward. For each nonblank category with a numeric amount, it either adds the amount to an existing category total or creates a new total. After reading the data, it finds or creates the Summary worksheet, writes headings and results, adds a grand total, and adjusts the column widths.

The summary’s category order follows the order in which categories first appear in the source; the code does not sort the output alphabetically. The grand total is calculated by adding the grouped totals, so it should match the sum of all amounts accepted by the macro. In the sample rows, North contributes 125 + 75 = 200 and South contributes 80, making the grand total 280. Checking a small example like this is a useful way to confirm that the input columns and inclusion rules are right.

Adapt the code to a different report

For a different data layout, update the column references consistently. For instance, if categories are in column C and amounts are in column F, change the last-row lookup to column C, read categories from column C, and read amounts from column F. Keep the header row and first data row assumptions in mind: the example always begins processing at row 2. If your headings occupy more than one row, adjust that starting row and the last-row checks.

Other common changes include writing a count instead of a sum, grouping by a second field, or applying a number format to the totals. Each change affects the calculation or output logic, so test it against a small hand-calculated sample first. If categories contain leading or trailing spaces, Trim$ removes those spaces before grouping. It does not standardize punctuation or internal spaces, so “North-East” and “North East” remain separate categories. Also inspect source cells for Excel error values: clean or handle errors explicitly if they may appear in the category or amount columns.

Macro, formula, or PivotTable?

A macro is useful when the same report must be rebuilt regularly, needs a custom sequence of steps, or should be generated with one command. It is less suitable when readers need to see calculations update immediately as source values change, or when workbook users cannot run macros because of policy settings. The code above writes fixed values, not live links to the source.

A PivotTable is often the better no-code choice for exploring and rearranging summaries. Select the source data and choose Insert > PivotTable, then place the category field in Rows and the amount field in Values. Confirm that the Values field is summarized by Sum rather than Count when that is the intended calculation. A PivotTable can be refreshed after source changes, while a macro can be tailored to produce a specific layout automatically. Formulas are another option for a small, fixed report; they can update with the source but may require more setup to list categories and calculate each total. Choose based on whether you value repeatable automation, interactive analysis, or live worksheet calculations.

Safety and common problems

Macros can change workbook contents, so keep a backup and review the code before running it. This procedure clears every cell on the worksheet named Summary before writing new output. Do not store unrelated notes or formulas on that sheet unless you are willing to lose them. It does not alter the source rows, but it can overwrite an existing Summary report. The macro also cannot be undone with Ctrl+Z; restoring the saved copy is the straightforward recovery option.

No summary appears: Confirm that you ran the macro from the data sheet, that row 1 is a header, and that categories are in column A with numeric amounts in column B.
Some rows are missing: Check for blank categories, numbers stored as text, error values, or data below a gap in column A. The final-row check depends on that column.
Different labels combine: The dictionary ignores capitalization differences, and the code trims outside spaces. Edit its comparison setting or normalization logic if that grouping is not appropriate.
Excel blocks the macro: Use a trusted workbook and follow your organization’s macro policy. Do not enable macros in a file from an unknown or untrusted source just to obtain a summary.

Frequently Asked Questions

Can Excel generate a summary automatically?
Yes. VBA can generate one when you run a macro, and a PivotTable can summarize selected data without VBA. A macro is useful for a repeatable custom output; a PivotTable is convenient when you want to explore fields interactively.

Can I run an Excel macro in a browser?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run this code.

Will the summary update when the source data changes?
Not by itself. This macro writes values when run, so rerun it after edits. A PivotTable must be refreshed, while formulas can recalculate as their referenced cells change.

Can the macro summarize more than one category column?
Yes, but the grouping logic must be changed to use a combined key or a nested grouping structure. For example, grouping by both region and product requires treating each region-product pair as a distinct group rather than using region alone.

Why does the macro clear my Summary sheet?
It clears the existing sheet named Summary so that old results do not remain beside new ones. Keep other material on a different sheet, or change the code to write to a new uniquely named sheet instead.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.