Intermediate Excel · Interactive lesson 8 of 12
PivotTable fundamentals
A PivotTable answers "how does this break down by category" without a single manual formula — drag a field into Rows, another into Values, and Excel does the grouping and the math.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialHow to Create a PivotTable in ExcelSkill check
Try these first:
- Build a PivotTable summarizing a number by category.
- Explain why a text column sums to 0 instead of adding up.
- Group a date field into months.
- Switch a value field from Sum to Average or Count.
All familiar? Skip ahead to I9 — Pivot Charts & Slicers.
Targeted teach
What you're learning
- Rows, columns, values, filters
- Rows and columns group the data; Values does the math on each group; Filters hides groups without changing the underlying data.
- Count vs. sum
- Drop a text field into Values and Excel silently switches to Count — a common "why is this all zeros" moment.
- Grouping dates
- Right-click a date field in a PivotTable to group it into months, quarters, or years instead of listing every single day.
- Number formatting
- A PivotTable's number formatting doesn't inherit from the source data — set it once inside the pivot, on the Value Field itself.
- Refresh behavior
- A PivotTable is a snapshot — new source rows won't appear until you refresh it.
- Changing summary calculations
- Sum, Average, Count, Max, Min — all live in the same Value Field Settings dialog.
Where to find it in Excel
Insert›PivotTable
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Row Labels | Sum of Amount | ||||||
| 2 | East | 3,250 | ||||||
| 3 | North | 6,900 | ||||||
| 4 | South | 5,400 | ||||||
| 5 | West | 4,880 | ||||||
| 6 | Grand Total | 20,430 | ||||||
| 7 | ||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Click any cell in your data, then open the Insert tab.
- 2PivotTable › From Table/Range creates the report on a new sheet.
- 3Drag fields into Rows, Columns, Values and Filters.
- 4Click the field in Values › Value Field Settings to change Sum to Count or Average.
- 5PivotTable Analyze › Refresh updates the report after the data changes.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
Choose how to group the sales data, and which summary calculation to use.
Independent variation
Summarize real data three ways
Take any list of transactions you have and build three PivotTables from it: one grouped by category, one grouped by month, and one showing Count instead of Sum. Note which grouping actually answers the question you started with.
Spot a mistake or have an idea for this lesson? Send feedback