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:

1. Select a cell in the pivot table
2. On the Ribbon's Insert tab, in the Filter group, click Slicer.

   

3. The Insert Slicers dialog box opens, with a list of pivot fields in the selected pivot table
4. In the list of pivot table fields, add check marks for the slicer(s) you want to create, using one checkbox, or multiple checkboxes
5. 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 the 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.

Watch this video to see how to set up the pivot tables and the pivot charts, and connect them to the slicer.

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:

1. 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.

   

2. Click the File tab on the Ribbon, and click Save As
3. From the Save As Type drop down list, select Excel Workbook (xlsx), or select macro-enabled workbook (xlsm) if the file contains macros.
4. Enter a name and select a folder, and click the Save button.
5. 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:

1. Close the updated file, and then re-open it.
2. 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:

1. Open the file in Excel 2010, and click the File tab on the Ribbon.
2. 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.

   

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

   

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

   

6. 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.

1. Click Save As, and save the file in Excel 2010 format (xlsx or xlsm).
2. 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 Sample File

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.

More Tutorials

Excel Slicer Macros

Slicers, Pop Up

Pivot Filter Pre-Printing Diagnostic

Pivot Cache Macros

Value Group Slicers

Excel Table Slicers

FAQs - Pivot Tables

Pivot Table Introduction