Grouping dates
Right-click any date in a pivot → Group. Choose years, quarters, months, days — several at once builds a hierarchy.
Grouping by Months alone pools every year together, which is right for seasonality and wrong for a trend. Select Years and Months for a proper time series.
Cannot group that selection almost always means the column contains blanks or text among the dates. Fix the source data — the pivot cannot group what is not consistently a date.
Grouping numbers and text
Right-click a numeric field → Group, then set a starting point, ending point and interval, to build bands of 0–100, 100–200 and so on.
Text fields group by selection: highlight several row labels, right-click, Group. That creates an ad-hoc category — useful, though a lookup column in the source is more maintainable.
Calculated fields
PivotTable Analyze → Fields, Items & Sets → Calculated Field adds a formula computed from other fields:
Margin = Revenue - CostA calculated field computes from the summed values, not row by row. Revenue / Units in a calculated field gives total revenue over total units — which is usually correct for a rate, but is not an average of the row-level ratios.
Where you need row-level logic, add a helper column in the source instead.
Show Values As
Right-click a value → Show Values As. This is the most underused feature in pivot tables:
| Option | Gives |
|---|---|
| % of Grand Total | Share of everything |
| % of Column / Row Total | Share within the column or row |
| % of Parent Row Total | Share within its group |
| Difference From | Change versus another item |
| % Difference From | Percentage change — previous period |
| Running Total In | Cumulative |
| Rank Largest to Smallest | Rank |
Month-over-month growth is % Difference From → previous — no formula at all. Drag the same field in twice to show value and percentage side by side.