Slicers in Excel: Click-to-Filter Pivot Tables

A slicer in Excel is a panel of buttons that filters a pivot table, or an Excel table, with one click. Each button is one item, such as East or West, and the highlighted buttons show exactly what the report includes. Slicers make a pivot easy for anyone to use, which is why they appear in so many dashboards. This guide shows how to add, use, connect and format them.

What does a slicer do?#

A slicer is a visible filter. A pivot's normal filter hides behind a small drop-down arrow, so readers can't see what's filtered. A slicer sits on the sheet as a box of buttons:

• Highlighted buttons are included in the report.
• Plain buttons are filtered out.
• Faded buttons have no data with the current filters.

The examples use this sales list in A1:D9, with a pivot showing Sales by Region:

Slicers for pivot tables arrived in Excel 2010 for Windows, and slicers for tables in Excel 2013. Current Microsoft 365 versions on Windows, Mac and the web all support them.

How to insert a slicer for a pivot table#

1. Click any cell inside the pivot table.
2. Go to PivotTable Analyze > Insert Slicer.
3. Tick one or more fields, for example Region and Product.
4. Click OK.

Excel adds one slicer box per field. Drag each box by its border to place it next to the pivot, and drag the corner handles to resize it.

You can slice by a field that isn't in the pivot layout at all. A pivot that shows only Sales by Region can still have a Product slicer.

How to filter with a slicer#

Click a button to show only that item. Click East in the Region slicer, and the pivot shows East's total of 9,000 (3,600 + 2,400 + 1,800 + 1,200).

To work with several items:

• Multi-Select. Click the checklist icon at the top of the slicer (Windows shortcut: Alt+S). Now each click adds or removes an item.
• Ctrl+click. On Windows, hold Ctrl and click to add single items. Click and drag to select a run of buttons next to each other.
• Clear Filter. Click the funnel icon with the red cross at the top right (Windows shortcut: Alt+C) to show all items again.

Two slicers combine with AND logic. Click East in Region and Laptop in Product, and the pivot shows only East laptops: 3,600 + 1,200 = 4,800. That is the same as a SUMIFS with two criteria, which you can try in the exercise on this page.

How to connect one slicer to several pivot tables#

One slicer can drive several pivots at once, which is the heart of a simple dashboard.

1. Right-click the slicer and choose Report Connections (on the ribbon: Slicer > Report Connections).
2. Tick every pivot table the slicer should filter.
3. Click OK.

Now clicking East updates, say, a Sales by Product pivot and a Units by Month pivot together. The pivots must come from the same source data; pivots built from different ranges don't appear in the list. If you need a month breakdown for a dashboard, see how to group dates in a pivot table.

How to add a timeline to filter by date#

A timeline is a slicer made for dates. Instead of buttons, it has a bar you click or drag across.

1. Click inside the pivot and go to PivotTable Analyze > Insert Timeline.
2. Tick a date field and click OK.
3. Use the drop-down in the top-right of the timeline to choose Years, Quarters, Months or Days.
4. Click one period, or drag across several, such as January to March.

Timelines work with pivot tables in Excel 2013 and later, and the source field must contain real dates. A column with text dates won't show up in the Insert Timeline list.

How to use a slicer with an Excel table#

Slicers aren't only for pivots. Click inside an Excel table (press Ctrl+T, Mac: ⌘T, to make one), then go to Table Design > Insert Slicer. Clicking a button hides the table rows that don't match, just like the filter buttons.

A normal SUM still adds the hidden rows. To total only what the slicer shows, use SUBTOTAL, for example =SUBTOTAL(109,D2:D9), which ignores hidden rows. Put it above the table so it stays visible.

How to format slicers and change their settings#

Click a slicer to see the Slicer tab on the ribbon. The most useful settings:

• Slicer Styles change the colours. Pick one that matches your report.
• Columns (in the Buttons group) lays the buttons out side by side. Set it to 4 for a short horizontal bar of regions.
• Slicer Settings lets you rename the header, sort items, and tick Hide items with no data so empty buttons disappear.
• Lock position. In Size and Properties, choose Don't move or size with cells, so the slicer stays put when columns change width.

To remove a slicer, click its border and press Delete.

Slicers, pivots and formulas#

Slicers make pivots friendly, but sometimes you need the same numbers in fixed cells for a report. A formula like =SUMIFS(D2:D9,A2:A9,"East",B2:B9,"Laptop") returns 4,800 without any clicking. The comparison of pivot tables vs SUMIFS explains when each fits. Once the pivot is filtered, you can show each slice as a share with percent of total or add a ratio with a calculated field. Slicers and pivot dashboards are also a core part of the Excel skills data analysts need.

Practise it#

Filtering a pivot quickly, and knowing what the filtered total should be, is a common task in analyst tests. Practise pivot tables in a live spreadsheet: add filters, nest row fields and show percent of total, with instant checks. Then try the SUMIFS exercise on this page to confirm a two-slicer answer.