How to Create a Pivot Table Quickly in Excel
How to create a pivot table in Excel: built-in PivotTable wizard, Recommended PivotTables, and a free macro that auto-pivots a category and value column.
2026-04-28
The "I just want a quick summary" problem
You have a flat data block. Customer in column A, amount in column B. You want a one-row-per-customer summary with the total amount per customer. Excel's PivotTable does this, but the wizard is overkill for the common case. Here are the options.
TL;DR — Key takeaways
- The full Insert > PivotTable wizard is powerful but overkill for a 2-column summary.
- Insert > Recommended PivotTables picks a layout for you, sometimes wrong.
- A VBA macro auto-builds a single-category-by-single-value pivot in one click.
- For more flexible pivots, the wizard is still the right tool.
Method 1: Full PivotTable wizard
- Select the data range.
- Insert > PivotTable.
- Pick where to place the pivot.
- In the field list, drag categories to Rows, values to Values.
- Click outside.
Powerful. Works for any pivot. About 30 seconds for a simple one.
The catch: the field list pane and drag-and-drop overhead is more than necessary for a 2-column "by-customer total" summary.
Method 2: Recommended PivotTables
- Select the data.
- Insert > Recommended PivotTables.
- Pick from suggestions.
Excel guesses based on column shapes. Often gets it right for simple 2-column data; less reliable for wider tables.
The catch: when Excel guesses wrong, you have to start over with the manual wizard.
Method 3: The free VBA macro
Download Auto Pivot Summary. Free .xlsm with one macro.
- Select a 2+ column range with category in the first column and numeric values in the others.
- Alt + F8, pick the macro, click Run.
- The macro creates a new sheet with a pivot summary.
Single-shot. No drag-and-drop.
When to use the wizard vs the macro
| Scenario | Macro | Wizard |
|---|---|---|
| 2-column "category total" | Yes | Yes |
| Multi-level row hierarchy | No | Yes |
| Multi-value summary (sum + avg + count) | No | Yes |
| Recurring weekly refresh | No | Yes (refresh-friendly) |
| Quick one-shot snapshot | Yes | Overkill |
A common scenario: weekly customer summary
Sales export: 2,000 rows of transactions. You need a one-row-per-customer summary of total spend.
Macro: select the customer + amount columns, run, summary appears.
Wizard: Insert PivotTable > drag fields > pick aggregation > done.
For this specific shape, the macro is faster. For anything more complex, the wizard is still the right tool.
Frequently asked questions
Does the macro create a real PivotTable or just a summary table?
A summary table written to a new sheet, not a real PivotTable object. Refresh requires re-running the macro. For refreshable pivots, use the wizard.
Can it do multiple aggregations (sum, average, count) at once?
No. The macro does sums of numeric columns. For multi-aggregation summaries, use the wizard or chain the macro with Quick Statistics Summary.
What if my category column has blanks?
Blanks get treated as a category (the empty string). The summary will have a row for "blank category" with the totals from those rows. Run Fill Blank Cells With Value Above on the category column first if you want to assign blanks to the previous category.
Does it work on multi-column row hierarchies (e.g., Region > Country)?
No. The macro is single-category-by-single-value. For hierarchies, use the wizard.
Can I undo it?
The macro adds a new summary sheet. To remove, right-click the tab and Delete.
What to do next
For a quick numeric summary of one column (mean, median, mode, std dev), use Quick Statistics Summary. To add a totals row to existing data, Add Totals Row to Numeric Columns.