PivotTables: Summarize Thousands of Rows in Seconds¶

A PivotTable is Excel's most powerful analysis tool. It turns a long raw dataset into an interactive summary — no formulas required.

A PivotTable summarizing sales by region and category

What It Does¶

Raw data (10,000 rows)              PivotTable (instant summary)
| Date | Region | Product | Sales |     | Region | Total Sales |
| ...  | North  | Pen     | 12    |  →  | North  | 145,200     |
| ...  | South  | Pad     | 25    |     | South  |  98,400     |
| ...  | (10k more rows)        |     | East   | 110,750     |

Creating One¶

1.Click any cell in your data.
2.Insert → PivotTable → OK (a new sheet appears).
3.Drag fields into the four zones.

The Four Zones¶

Zone	Role	Example
Rows	Categories down the side	Region
Columns	Categories across the top	Product
Values	The numbers to aggregate	Sum of Sales
Filters	Page-level filter	Year

Drag Region → Rows
Drag Sales  → Values  (becomes "Sum of Sales")
→ instant total sales per region

Changing the Calculation¶

Click a field in Values → Value Field Settings to switch between:

Sum · Count · Average · Max · Min · Product · % of Total

Tip

Use "Show Values As → % of Grand Total" to instantly turn totals into percentages — perfect for "what share did each region contribute?"

Refreshing¶

PivotTables don't auto-update when source data changes. After editing data:

PivotTable Analyze → Refresh   (or right-click → Refresh)

Caution

Build PivotTables on an Excel Table (Chapter 2), not a plain range. The table auto-expands, so new rows are included on refresh without re-selecting the source.