Interactive Pivots: Slicers, Grouping & Calculated Fields
Take pivot tables further — add clickable slicer buttons, group dates into months, show numbers as a percentage of the total, and build your own calculated field.
What you will learn
- Add a slicer so anyone can filter a pivot by clicking
- Group dates and numbers into ranges
- Show values as a percentage of the total and add a calculated field
From a plain summary to an interactive one
You already know a pivot table turns a long list into a tidy summary. The features in this lesson are what turn that static summary into an interactive analysis tool — the kind a manager can click around in without touching a single formula. We will add four upgrades: slicers, grouping, value settings like % of total, and a calculated field.
Start from this familiar order data, which we imagine sits on a sheet and has been turned into a pivot of Sum of Sales by Region:
| Date | Region | Product | Sales |
|---|---|---|---|
| 2026-01-05 | North | Apples | 120 |
| 2026-01-18 | South | Apples | 80 |
| 2026-02-09 | North | Bananas | 90 |
| 2026-02-20 | South | Bananas | 60 |
| 2026-03-11 | North | Apples | 100 |
Slicers: filtering by clicking, not menus
A slicer is a little box of buttons that filters a pivot when you click them. It does the same job as the filter drop-down, but it is big, visible and friendly — perfect on a dashboard, because anyone can use it without knowing pivot menus. You add one by clicking the pivot and choosing PivotTable Analyze > Insert Slicer, then ticking the field you want buttons for.
PivotTable Analyze > Insert Slicer > tick "Region"Note: Output: A small panel of buttons appears: North and South. Click North and the whole pivot instantly shows only North data; click South and it switches. Click the little clear icon to show everything again. Unlike the hidden filter drop-down, a slicer is always visible and shows at a glance which filter is active (the chosen button is highlighted).
Tip: One slicer can control several pivots at once. Right-click the slicer, choose Report Connections, and tick every pivot it should filter. Now one click filters your whole dashboard together — the professional way to make an interactive report.
Grouping: turn dates into months, numbers into bands
Raw data has one row per day, but a report usually wants totals per month or quarter. Rather than adding a helper column, you can ask the pivot to group for you. Put the Date field into Rows, right-click any date in the pivot, choose Group, and pick Months (and Years if the data spans more than one year).
Rows: Date -> right-click > Group > Months
Values: Sum of SalesNote: Output: Jan 200 Feb 150 Mar 100 Grand Total 450 The pivot rolled the five daily rows up into three months: January (120 + 80 = 200), February (90 + 60 = 150) and March (100). You never added a Month column — the pivot grouped the dates on its own.
Grouping works on numbers too. If you put a number field (say Sales) into Rows and group it, you can create bands like 0–99, 100–199 and so on — handy for counting how many orders fall into each size band.
Value settings: show a percentage instead of a total
By default the Values box shows a plain total. But the same number is often more useful as a share of the whole. Right-click a value in the pivot, choose Show Values As > % of Grand Total, and every total becomes a percentage.
right-click a value > Show Values As > % of Grand TotalNote: Output: North 68.89% South 31.11% Grand Total 100.00% The amounts (310 and 140) became their share of the 450 grand total: North is about 69% of all sales, South about 31%. The numbers did not change in the data — only how the pivot displays them. Other useful options here are Running Total (a cumulative total down the rows) and % of Column Total.
A calculated field: do maths inside the pivot
Sometimes the number you need is not in the data — it has to be calculated. A calculated field lets you write a small formula using the pivot’s own fields, and it appears as a new column. Suppose each region pays a 10% commission and you want to show it. Choose PivotTable Analyze > Fields, Items & Sets > Calculated Field, name it Commission, and enter the formula:
Name: Commission
Formula: =Sales * 0.1Note: Output: Region Sum of Sales Commission North 310 31 South 140 14 The calculated field multiplied each region’s total Sales by 0.1, giving a Commission column of 31 and 14. It lives only inside the pivot — your raw data is untouched — and it recalculates whenever the pivot refreshes.
Watch out: A calculated field works on the totals the pivot has already added up, not on each original row. For per-row maths (like a margin on every single order), add a helper column to the source data instead, then pivot that column.
| Feature | What it adds | Where to find it |
|---|---|---|
| Slicer | Clickable filter buttons | PivotTable Analyze > Insert Slicer |
| Timeline | A slider to filter by date | PivotTable Analyze > Insert Timeline |
| Grouping | Months/quarters or number bands | Right-click a row > Group |
| Show Values As | % of total, running total, rank | Right-click a value > Show Values As |
| Calculated field | A formula column inside the pivot | Fields, Items & Sets > Calculated Field |
Tip: A Timeline is a slicer made just for dates: it gives a slider you drag to pick a month or quarter range. Add one with PivotTable Analyze > Insert Timeline and pick your date field — it pairs beautifully with a date-grouped pivot on a dashboard.
Q. You want a manager to filter a dashboard pivot just by clicking, without opening any menus. Which feature fits best?
✍️ Practice
- Build a pivot of Sales by Region, then add a Region slicer and click between North and South.
- Put Date in Rows, group it by Months, then switch the values to % of Grand Total.
🏠 Homework
- Create a 12-row dated sales table. Build a pivot, group the dates into months, add a slicer for Product, switch the value to % of Grand Total, and add a calculated Commission field at 5% of sales.