A pivot table answers the question you built it for. A pivot table with a slicer answers the twelve questions your manager asks afterwards, without you touching it again.
That is the whole argument for slicers, and it is worth spending ten minutes on. The part most people miss is that one slicer can drive every pivot table and pivot chart in the workbook at once — which is what turns three separate tables into something that behaves like a dashboard.
What a slicer actually is
A slicer is a filter you can see. The filter dropdowns built into a pivot table do the same job, but they hide the current state behind a funnel icon: you cannot tell at a glance whether you are looking at all regions or just Alberta. A slicer puts the choices on the sheet as buttons and highlights the active one.
That visibility matters more than the convenience. Most bad decisions made from a spreadsheet come from someone reading a filtered number as if it were the total.
Add a slicer
- Click any cell inside your pivot table.
- On the PivotTable Analyze tab, choose Insert Slicer. (In Excel 2010 the tab is called Options.)
- Tick the field you want to filter by — Region, Salesperson, Product.
- Click OK.
You get one slicer box per field you ticked. Click a button to filter; ctrl-click or use the multi-select button in the slicer header to pick several; click the clear filter icon in the corner to reset.
Drag the slicer where you want it, then use the Slicer tab to set Columns to 2 or 3 so a long list of regions becomes a compact block instead of a scrolling strip.
Connect one slicer to several pivot tables
This is the step that makes the difference, and it is one right-click.
- Right-click the slicer and choose Report Connections. (Excel 2010 calls this PivotTable Connections.)
- Tick every pivot table you want this slicer to control.
- Click OK.
Now one click on “Alberta” filters the summary table, the detail table, and the chart together. Do the same for a second slicer — say, fiscal year — and you have a two-dimensional dashboard built entirely out of features that ship in Excel.
Timelines work the same way for dates. Insert a Timeline instead of a slicer, connect it with Report Connections, and you get a draggable date-range control that beats a list of 36 month buttons.
The catch nobody warns you about
Report Connections will only list pivot tables that share the same pivot cache. If you build two pivot tables by separately inserting each one from the source data, Excel may give each its own cache, and each slicer will then only ever see its own pivot table. The dialog is not broken — the other pivot table genuinely is not connectable.
The reliable way to avoid this: build the first pivot table, then copy and paste it and change the fields on the copy. A pasted pivot table always shares the original’s cache. If you have already made the mistake, rebuilding the second pivot table by copying the first is faster than trying to merge caches.
Two habits that prevent most slicer problems:
- Base pivot tables on an Excel Table, not a range. Select your data and press ctrl-T first. A Table grows as rows are added, so refreshing picks up new data instead of silently ignoring it.
- Keep every pivot table for one report on one cache. If two tables genuinely need different source data, they belong to different reports.
Disconnecting a slicer
Same dialog, opposite action: right-click the slicer, choose Report Connections, and untick the pivot tables you no longer want it to drive.
This is worth knowing because it is how you build a report where one section deliberately ignores the global filter — a year-over-year comparison, say, that should always show all years no matter what the slicer says. Disconnect that pivot table and it stops listening.
Deleting the slicer removes the control but leaves the filter it applied still in force on the pivot table. Clear the filter first, then delete the slicer, or you will spend an afternoon wondering why your totals are wrong.
Tidying up
A few settings under Slicer Settings (right-click the slicer) that earn their keep:
| Setting | Why you want it |
|---|---|
| Hide items with no data | Stops the slicer offering choices that return an empty table |
| Visually indicate items with no data | Keeps the buttons but greys the empty ones |
| Sort ascending / descending | Alphabetical by default; switch it for custom sort orders |
| Display header | Turn it off once the field name is obvious from context |
One accessibility caveat, since it rarely gets mentioned: slicer buttons are reachable by keyboard, but screen readers announce them inconsistently. If the workbook has to be usable by someone using assistive technology, keep the pivot table’s own filter row available as well rather than relying on slicers alone.
When not to use a slicer
If the report has one pivot table and one filter, the built-in filter dropdown is less work and takes no space. Slicers pay off when there is more than one pivot table to keep in sync, or when someone other than you will do the filtering.
And if you find yourself adding six slicers, the honest answer is that you have outgrown pivot tables and want Power Query to reshape the data first so the report needs fewer controls.
Slicers, pivot tables, and Power Query are the three things we spend most of the time on in our Excel and Office 365 courses, because they are where the largest amount of manual work disappears. If you would rather work through it with someone, the full course schedule lists what is running next.
Work through the same example yourself: download the practice workbook. No sign-up required.
Common questions
- Why is my pivot table missing from Report Connections?
- Because it is built on a different pivot cache. Report Connections only lists pivot tables that share the slicer's cache. Rebuild the second pivot table by copying the first one instead of inserting a new one from the source data.
- Can one slicer control pivot tables on different worksheets?
- Yes. Report Connections lists every pivot table in the workbook that shares the cache, whatever sheet it sits on. The slicer itself lives on one sheet, so put it on the sheet people actually look at, or copy it to each sheet.
- Do slicers work on a normal range, or only on pivot tables?
- They work on any Excel Table too. Click inside the Table, then Insert, then Slicer. That filters the Table directly, with no pivot table involved.