Pivot Tables Deep Dive: Grouping, Slicers, Pivot Charts
A dedicated tour of Pivot Tables beyond the basics — grouping dates, adding calculated fields, using Slicers, and pairing with Pivot Charts.
Learning objectives
- Build and refresh a Pivot Table from an Excel Table.
- Group dates by month/quarter/year.
- Add a calculated field and connect a Slicer.
Step-by-step
1. Source data hygiene
Turn source data into an Excel Table first (Ctrl+T). When you add rows, the Pivot picks them up on refresh — no more re-selecting the range.
2. Layout
Drag fields into Rows, Columns, Values, Filters. Change Value Field Settings to switch between Sum, Average, Count, and % of Column Total.
3. Grouping dates
Right-click a date row → Group → pick Months and Years. You now have a monthly/annual summary from raw daily data.
4. Slicers and Pivot Charts
PivotTable Analyze → Insert Slicer for a big-button filter. Insert Pivot Chart for a chart that stays in sync with filters and drilldowns.
5. Calculated fields
PivotTable Analyze → Fields, Items & Sets → Calculated Field. Add fields like Margin = Revenue - Cost without touching source data.
Key takeaways
- Build and refresh a Pivot Table from an Excel Table.
- Group dates by month/quarter/year.
- Add a calculated field and connect a Slicer.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced