📎 This article includes 1 downloadable practice file ↓
In this article
The beginner course built a basic PivotTable. This lesson turns it into a report people can use in a meeting.
Group dates
Put Date in Rows. Right-click any date > Group > select Months and Quarters (and Years if data spans more than one). Note that pivot quarters are calendar quarters (Jan-Mar = Qtr1); for Indian FY quarters, add an FY Quarter column to the data (lesson 6) and use that instead.
Show values as
Right-click a value > Show Values As:
- % of Grand Total: each region’s share of all sales.
- % of Column Total: share within each month.
- Difference From (previous month): month-on-month change.
- Running Total In (Date): year-to-date.
Slicers and timelines
PivotTable Analyze > Insert Slicer (Region, Category) and Insert Timeline (Date). Right-click a slicer > Report Connections to make one slicer filter several pivots at once: the start of a dashboard (lesson 12).
Calculated fields
PivotTable Analyze > Fields, Items & Sets > Calculated Field: e.g. GST = Amount * 0.18. Calculated fields always work on sums, so averages and ratios can surprise you; for anything complex, add the column to the source data instead.
Always check a pivot
A pivot built on a fixed range misses new rows; a filter left on hides data. Check one or two numbers with SUMIFS, exactly what the practice file does.
Practice
Download this lesson’s workbook below. Build the pivot yourself, then type the same numbers as formulas to prove your pivot is right. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Lesson 9 practice workbookSales register + 7 pivot check numbers.⬇ XLSX · 18 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.