Advanced Excel · Interactive lesson 5 of 12
Advanced PivotTables
The Basics and Intermediate tracks built a PivotTable. This is about controlling what it shows — grouped by quarter instead of by date, as a percent of total instead of a raw sum, filtered to just the top performers.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialAdvanced PivotTables in ExcelSkill check
Try these first:
- Group a column of dates into quarters inside a PivotTable.
- Change "Sum of Revenue" to show as a percent of the grand total instead.
- Filter a PivotTable down to only its top 3 rows by value.
- Explain why a PivotTable's numbers don't move until you refresh it.
All familiar? Skip ahead to A6 — Power Query Fundamentals.
Concept teach
What you're learning
- Date grouping
- Right-click any date field in the Rows area → Group → Quarters (or Months, Years) — turns 18 individual dates into a handful of clean buckets.
- Show values as
- Beyond a raw sum: "% of Column Total" turns each row into a share of the whole, useful the moment someone asks "what fraction of revenue is that?" instead of "how much?"
- Calculated fields
- A field computed from other fields in the pivot itself — Gross Margin = Revenue − Cost — without adding a column to the source data.
- Top N filters
- Value Filters → Top 10 — but the number and metric are both yours to set; "top 3 by revenue" is just as valid.
- Pivot cache refresh
- A PivotTable reads a cached snapshot, not the live source — which is why editing the source data does nothing until you hit Refresh.
Where to find it in Excel
PivotTable Analyze›Group Field · Fields, Items & Sets · Refresh
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Row Labels | Sum of Sales | % of Total | |||||
| 2 | Qtr1 | 15,900 | 22.4% | |||||
| 3 | Qtr2 | 18,240 | 25.7% | |||||
| 4 | Qtr3 | 17,610 | 24.8% | |||||
| 5 | Qtr4 | 19,300 | 27.2% | |||||
| 6 | Grand Total | 71,050 | 100.0% | |||||
| 7 | ||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Group Field turns dates into months, quarters and years.
- 2Field Settings › Show Values As gives % of Total, Running Total and more.
- 3Fields, Items & Sets › Calculated Field adds a field built from a formula.
- 4Refresh (Alt+F5) reloads the pivot cache after the source changes.
- 5The Row Labels arrow › Value Filters › Top 10 shows only the top N.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Eighteen transactions across two quarters. Group by quarter or by region, switch how values are shown, and filter to the top performers.
Independent extension
Summarize your own data
Build a PivotTable on real sales or activity data: summarize by month or quarter, show one measure as a percent of total, and add a Top N filter to surface just the standouts. Then add a calculated field — margin, or rate — and check it against a formula built outside the pivot.
Spot a mistake or have an idea for this lesson? Send feedback