|
Home > Pivot > Filters > Slicers
Excel Pivot Table Slicers
Quickly filter Microsoft Excel Pivot Tables with Slicers. They make it easy to change
one or more pivot tables with a single click. |
Video: Add Slicers to Filter Pivot Table
Data
In this video you can see the steps for adding a slicer to a pivot
table in Excel 2010, and then using slicers to filter the data. The
written instructions are below the video.
Add Slicers to Filter Pivot Table Data
Introduced in Excel 2010, Slicers are a powerful new way to filter
pivot table data.
To insert Slicer for a specific pivot table, follow these steps:
- Select a cell in the pivot table
- On the Ribbon's Insert tab, in the Filter group, click Slicer.

- The Insert Slicers dialog box opens, with a list of pivot fields in the selected pivot table
- In the list of pivot table fields, add check marks for the slicer(s)
you want to create, using one checkbox, or multiple checkboxes
- Next, click the OK button, to insert the selected Slicers onto the worksheet
In the following image, two pivot fields are selected, Genre and ActorName, so 2 Slicers will be insertted

Adjust the Slicers
Usually, after inserting Slicers, you'll need to move them, and resize them, so the Slicers are easy to use. I try to make them fit in the
empty areas of the worksheet, so they don't cover any of the pivot table data.
Move and Resize Slicers
A) To move a Slicer, follow these steps:
- First, point to the Slicer's border, or point to an empty part of the Slicer.
- When the pointer is a 4-headed arrow, drag the Slicer to a new location on the same worksheet
B) To resize a Slicer, follow these steps
- Click on the Slicer, to select it
- Point to one of the sizing handles on its border
- When the pointer is a 2-headed arrow, drag in or out, to make the Slicer smaller or larger
Change Slicer Style
Just like choosing a different pivot table style, you can quickly format a Slicer, by using one of the built-in styles
To choose a Slicer Style, follow these steps:
- First, click on an empty part of the Slicer, to select it
- On the Excel Ribbon, click on the Slicer tab (it only appears when a Slicer is selected)
- In the Slicer Styles group, click on one of the visible styles, to apply that style
- OR,
- Click the More arrow, at the bottom right of the visible styles, to see all of the available styles, separated into Light and Dark sections.
- Then, click on a style to apply it to the selected pivot table

Change Slicer Settings
There are a few Slicer Settings that you can change, after adding Slicers to a Pivot Table.
Sort Order
For Slicers that show dates, you might prefer to see the most recent dates at the top of the list.
To change the sort order for any Slicer, follow these steps:
- Right-click on the Slicer, and click Slicer Settings
- In the Item Sorting and Filtering section, select Ascending or Descending
- Click OK
Change Slicer Caption
By default, the Slicer caption shows the field names in the Pivot Table. You can change the Slicer captions, to make them shorter, or easier to understand.
For example, you could change the Slicer to show "Actor" in its caption, instead of "ActorName"
- Right-click on the Slicer, and click Slicer Settings
- In the Header section, type the Caption text, to replace the existing caption
- Click OK
Use Pivot Table Slicers
After you finish adjusting the Slicers, you can use them to filter data in the pivot table.
- Click on an item in a Slicer, to filter the pivot table.
- Note: When a filter is applied, Clear filter icon in Slicer header shows a red X
- To remove any filters on the Slicer, click the Clear Filter button
- Other
Slicers will show related items at the top.
- For example, whan a movie genre is selected, the ActorName slicer shows some actors at the top, in dark font. Below that, there are actor names in lighter font, because they aren't in movies from the selected genre.

Filter Multiple Pivot Tables With One Slicer
In this video, you'll see the steps for connecting multiple pivot tables to a slicer, so they can all be filtered with a single click. The written instructions are below.
NOTE: If you're using Excel 2010, follow the steps in this video.
Connect Another Pivot Table
If you create multiple pivot tables from the same pivot cache, you can connect them to the same slicers, and filter all the pivot tables at the same time.
1) First, follow the steps in the previous section, to create a slicer for the first pivot table.
2) Next, to create the Slicer connection in the second pivot table, follow these steps:
- Select a cell in the second pivot table
- On the Excel Ribbon’s Analyze tab, in the Filter group, click Filter Connection
- In the Filter Connections window (shown below), add a check mark to each Slicer that you want the pivot table to connect to
NOTE: Repeat these steps for any addtional pivot table that you want to connect to the Slicer.

Use the Multi-Connected Slicers
Both pivot tables are now connected to the Slicer. If you select an item in a slicer, both pivot tables will be filtered.
For example, in the screen shot below, both pivot tables are showing East region sales, for Desk and Pen orders.

Video: Change All Pivot Charts
With One Filter
This video shows how you can use a single Report Filter, connected
to a slicer, to update multiple pivot charts. The slicers are stored
on a different worksheet, so they don't take up room on your Excel
dashboard sheet.
In this video, I show how to set up the pivot tables and the pivot
charts, and connect them to the slicer. There are written steps on my Pivot Table blog, and the sample file is in the download section, at the bottom of this page.
Pivot Table Slicers On Demand
Click a pivot table report filter field, to see the matching Slicer. Next, select criteria, then click the Get Data button. That runs an advanced filter macro, that pulls the matching data. Thanks to Excel expert, AlexJ, who created and shared this example!
There are details on my Contextures Blog, and you can get AlexJ's sample file in the download section, further down this page.

Video: Problem With Drill to Detail and Slicers
Slicers, combined with a pivot table's Drill to Detail feature, can
produce unexpected results. See the problem in this video.
Video: Problems With Adding Slicers
Update older Excel files, if you want to use Slicers with the pivot
tables in those files. Occasionally, there is a problem updating a
pivot table, so you can try to repair it, by following the steps in
this video. Written steps are below the video.
Problems With Adding Slicers
Usually it's easy to add a Slicer to a pivot table, but for files
that were created in older versions of Excel, you might need to update
the file before adding Slicers.
To use Slicers in a pivot table created in Excel 2003, follow these
steps to update the file to a newer format:
- Open the file (xls format) in Excel 2010. The file name will appear
in the title bar of the Excel window, and it will show [Compatibility
Mode] after the file name.

- Click the File tab on the Ribbon, and click Save As
- From the Save As Type drop down list, select Excel Workbook (xlsx),
or select macro-enabled workbook (xlsm) if the file contains macros.
- Enter a name and select a folder, and click the Save button.
- The file is updated to the new format, and you can see that extension
in the file name in the title bar.
After you update the file, the title bar will still show [Compatibility
Mode] after the file name. Follow these steps to complete the update:
- Close the updated file, and then re-open it.
- The [Compatibility Mode] after the file name will have disappeared,
and you should be able to select a pivot table cell, and add a Slicer.
Update Files Won't Allow Slicers
After following these steps, most files will allow you to add Slicers
to the pivot tables. However, the occasional file might not show an
enabled Slicer button, even after updating.

If you encounter that problem, there is probably some corruption
in the old pivot table,
- You can build a new pivot table from the source data, and delete
the old one.
- Or, you can try to repair the pivot table, by following the steps
below.
To try to repair the pivot table:
- Open the file in Excel 2010, and click the File tab on the Ribbon.
- Click Save As, and save the file in Excel 97-2003 format (xls).
Click Continue if a Compatibility Checker appears, listing the features
that might be affected.

- Close the file, and re-open it.
- If an unreadable content message appears, click Yes, to open the
file.

- If a "Repairs to PivotTable report" message appears, click Close, after reading the repairs
information.

- The title bar will show the .xls file format, and [Repaired] and
[Compatibiltiy Mode]
The final step is to save the file back into the newer format.
- Click Save As, and save the file in Excel 2010 format (xlsx or
xlsm).
- Close the file, and re-open it. The title bar should show the
file name, without [Repaired] or [Compatibiltiy Mode].
Then, select a cell in the pivot table, and if the repair was successful,
you will be able to insert a Slicer.
Get the Excel Files ✅
- Pivot Slicers: If you need sample data to test with these pivot table Slicer videos, click here to get a zipped sample file with Region Sales data. It also has a pivot table with two Slicers set up. The zipped file is in xlsx format, and does not contain macros.
- Change All Charts: Change the filter at the top of a dashboard sheet, and all the connected pivot charts change. This technique uses Excel slicers, and no macros, and you can download the Change All Charts sample file to see how it works.
- Pivot Slicers On Demand: In this On Demand Slicers sample file by AlexJ, you can click a pivot table report filter field, to see its matching Slicer. The zipped file is in xlsm format, and contains macros.
Get Monthly Excel Tips!
Be sure to get my monthly newsletter, with quick Excel tips, links, news, and a bit of fun. Just add your email, then click Sign Up.
More Tutorials
Excel Slicer Macros
Slicers, Pop Up
Pivot Table Filter Dashboard
Pivot Cache Macros
Value Group Slicers
Excel Table Slicers
FAQs - Pivot Tables
Pivot Table Introduction