Auto Pivot Summary

What it does

Generates a clean summary sheet from any data range: groups by one column, sums another, counts the rows in each group. Output goes to a new sheet called Summary, with three columns (group, sum, count) and one row per group. Lighter than Excel's PivotTable feature when all you need is "total per category."

Real-world example

A finance analyst at a SaaS company runs the weekly sales review. The CRM exports a sheet of every closed deal with Sales Rep | Region | Deal Size | Close Date | Notes. Her job: produce a summary of total sales per rep, sorted descending, for the Monday team meeting.

The traditional path is a PivotTable. Insert → PivotTable → drag Sales Rep to Rows, Deal Size to Values (Sum), refresh, format, copy to a clean sheet so the formatting doesn't break when emailed. About 6 minutes the first time, then 3 minutes after that for the muscle memory. PivotTables are great, but they come with baggage: they need a PivotCache, they break in unexpected ways when the source data shape changes, and they look weird when copy-pasted into a Slack message.

With this macro, she selects the data, runs AutoPivotSummary, types 1,3 (Sales Rep, Deal Size), and 2 seconds later there's a Summary sheet with one row per rep showing the total and the count. She sorts column B descending, copy-pastes into Slack, done.

Realistic time saved per weekly run: 4 to 5 minutes. Across a year, that's 4 hours back, plus the absence of "why is the pivot showing #REF" headaches when the source CSV gets one extra column. The output is plain values, so it pastes anywhere without breaking. The bigger advantage shows up when she needs to re-run with a different filter applied to the source: the macro is idempotent, the pivot cache is not.

How to use

First time? See How to run our tools for the 30-second setup.

1. Paste your data into the workbook (or work in your own workbook side-by-side). Row 1 should be headers.
2. Select the data range, including the header row.
3. Press Alt + F8 / Option + F8, pick AutoPivotSummary, click Run.
4. Type two column numbers separated by a comma: first the column to GROUP BY, second the column to SUM. Example: 1,3.
5. A new Summary sheet appears at the end of your workbook.

FAQ

How is this different from Excel's PivotTable?

PivotTables are powerful but heavy. They require a PivotCache, a layout pane, and special handling when source data changes. This macro runs once and writes plain values to a sheet you can format, copy, email, or paste anywhere. For the "total per category" use case, it's faster and the output is more portable. For multi-dimensional analysis, use PivotTables.

Can I sum multiple columns at once?

Not in a single run. Run the macro twice with different value columns and combine the Summary sheets if you need that.

What if the value column has blanks or text?

Blanks and text values are skipped (not added to the sum). The count column tells you how many numeric values contributed to each group's sum, so you can spot data quality issues at a glance.

Can I sort the summary?

Yes, click into the Summary sheet, select column B (the sum), then Data → Sort Z to A for largest first. The output is plain values, so sort works without breaking anything.

Is the group-by case-insensitive?

Yes, Acme and ACME are summed together. If you genuinely need case-sensitive grouping (rare for category-style columns), pre-process your text first.