Quickly filter Excel Pivot Tables with Slicers. They make it easy to change one or more pivot tables with a single click.
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.
Introduced in Excel 2010, Slicers are a powerful new way to filter pivot table data.
It's easy to add a 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.
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:
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.
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.
Slicers, combined with a pivot table's Drill to Detail feature, can produce unexpected results. See the problem in this video.
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.
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:
After you update the file, the title bar will still show [Compatibility Mode] after the file name. Follow these steps to complete the update:
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,
To try to repair the pivot table:
The final step is to save the file back into the newer format.
Then, select a cell in the pivot table, and if the repair was successful, you will be able to insert a Slicer.
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.
Last updated: July 11, 2021 11:15 AM