The PivotTable features that turn a summary into a report — plus why your dates refuse to group.
A PivotTable answers a question about a dataset in seconds. Most frustration with them traces back to the source data rather than the PivotTable itself.
Prepare the source first
- One header row, no blanks, no merged cells
- Convert the range to a Table so new rows are included on refresh
- Real dates, not text that looks like dates
- No subtotal rows inside the data — the PivotTable creates those
Grouping
Right-click any date field and choose Group to roll up by month, quarter or year. If Group is greyed out, the column contains text or blanks. Numeric fields can be grouped into bands — useful for age, ticket value or order size.
Value display options people miss
| Show Values As | Answers |
|---|---|
| % of Grand Total | What share does each region contribute? |
| % of Parent Row Total | Within this region, how do branches compare? |
| Difference From (previous) | How did this month move against last month? |
| Running Total In | Where are we against the year so far? |
Calculated fields
Use a calculated field for a ratio that must recompute at every level — margin percentage, for instance. Do not calculate a ratio outside the PivotTable and average it, because averaging percentages produces the wrong total.
Slicers and timelines
Slicers make filters visible, which matters when a report is shared. One slicer can drive several PivotTables via Report Connections, which is the simplest way to build a dashboard that behaves consistently.
Refresh discipline
In PivotTable Options, enable 'Refresh data when opening the file' and turn off automatic column-width changes so your formatting survives.
Frequently asked questions
- Why can't I group dates in my PivotTable?
- The date column is not being read as dates. Check for text values, blanks, or dd-mm-yyyy strings imported from a system export, then convert the column to a true date type.
- How do I show percentages instead of totals?
- Right-click a value in the PivotTable, choose Show Values As, and pick the comparison you need, such as % of Grand Total or % of Parent Row Total.
Learn this properly, with a trainer
Advanced Excel with Raj Singh — classroom in Vasai or live online. Batch timings are confirmed when you enquire.
See course options & fees