Excel Pivot Table Slicers

Quickly filter 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.

It's easy to add a Slicer:

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

   

3. In the list of pivot table fields, add check marks for the slicer(s) you want to create

   

4. Move and resize the slicers, if necessary, so they fit on the empty areas of the worksheet
5. Click on an item in a Slicer, to filter the pivot table. Other Slicers will show related items at the top.

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.

To create the Slicer connection in the second pivot table:

• 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, add a check mark to each Slicer that you want the pivot table to connect to

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 Repair 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

Pivot Filter Pre-Printing Diagnostic

Pivot Cache Macros

Value Group Slicers

Excel Table Slicers

FAQs - Pivot Tables

Pivot Table Introduction