PivotTables for Advanced Analysis
Calculated fields, grouping, and slicers — going beyond a basic PivotTable summary.
Compatibility: All Excel versions (Slicers for PivotTables: Excel 2010+)
What You'll Learn
- How to add a Calculated Field to a PivotTable
- How to group dates into months, quarters, or years
- How Slicers provide a more visual way to filter a PivotTable
Building on PivotTable basics, this lesson covers three features that turn a simple summary into a genuine analysis tool.
Calculated Fields
PivotTable Analyze > Fields, Items & Sets > Calculated Field lets you add a new field computed from existing ones — for example, a Profit Margin field calculated from existing Revenue and Cost fields, without needing that column to already exist in your raw data.
Grouping
Right-click a date field in a PivotTable and choose Group to roll individual dates up into Months, Quarters, or Years automatically — turning hundreds of individual transaction dates into a clean monthly summary with one click.
Slicers
Insert > Slicer adds a clickable, visual filter button for a field — letting anyone viewing the PivotTable filter it by clicking a button (like a region name) instead of digging into a dropdown menu. Multiple PivotTables can even share the same slicer, so one click filters several summaries at once.
Common Mistakes
- Building a Calculated Field that references another calculated field, which PivotTables don't support directly
- Forgetting a PivotTable still needs a manual refresh after the underlying data changes, even with slicers connected
Practice Exercise
Using the sales transaction data from the PivotTables Basics exercise, add a Calculated Field for Profit (Revenue minus Cost, if you add a Cost column), group any date field by month, and insert a Slicer for Region.
Key Takeaways
- Calculated Fields add new computed columns directly inside a PivotTable
- Grouping rolls individual dates up into months, quarters, or years
- Slicers give a clickable, visual way to filter one or more PivotTables at once