Concept
1. Insert a slicer
Click the pivot → PivotTable Analyze → Insert Slicer → tick Region (and Category) → OK.
- Click a button to filter; Ctrl + click for several; or turn on Multi-Select (the ☰✓ icon).
- Clear Filter icon (funnel with ×) or Alt + C.
- Greyed buttons = no data for the current selection of other slicers.
Slicers are much easier for managers than the filter dropdowns — and they show what is filtered.
2. Format slicers
Slicer tab: Columns = 3 (buttons side by side), button height/width, a style. Slicer Settings: rename the caption, sort, Hide items with no data.
3. Timeline — a slicer for dates
PivotTable Analyze → Insert Timeline → Date. Drag across the bar to pick a range; the top-right dropdown switches between Years, Quarters, Months, Days.
Timelines need a real date field. (They use calendar quarters, so for FY reporting a FY slicer from a helper column is often clearer.)
4. One slicer → many pivots
Build a small dashboard: Pivot 1 = Sales by Region, Pivot 2 = Sales by Product, Pivot 3 = Sales by Month — all from tblSales.
Then: click the Region slicer → Slicer tab → Report Connections (or right-click → Report Connections) → tick all three pivots → OK.
Now clicking "North" filters every pivot (and every pivot chart, Lesson 6) at once. Same for timelines: Timeline → Report Connections.
Rule: a slicer can connect only to pivots built on the same source (same Table / same Data Model). Pivots created from a copy-pasted range or a different table won't appear in the list.
5. Tips for clean dashboards
- Create the second and third pivots by copy-pasting the first pivot and rearranging fields — they share the same source automatically.
- Put slicers at the top or left, aligned (Shape Format → Align).
- Turn off "Autofit column widths on update" (PivotTable Options) so the layout doesn't jump when filtering.
- Protect layout: lock slicer position with Slicer → Size & Properties → Position → "Don't move or size with cells".
6. Slicers on Tables too
Click inside a normal Excel Table → Table Design → Insert Slicer. Filters the table rows — handy for data entry sheets. (A Table slicer can't be connected to pivots.)
Common mistakes
Pivot missing from Report Connections because it uses a different source. Forgetting slicers keep filtering when you send the file (people see partial numbers — clear filters before sharing or show the selection). Autofit making the dashboard jump around.