Excel 2010 introduced a new filter interface for Pivot Tables called Slicers. This feature improved the filtering process for Pivot Tables and made filtered selections more visible.
Slicers abilities have been expanded in Excel 2013 – but that’s the subject of a future blog post.
If you select multiple items in a filter in a PivotTable it is not obvious what the filters being applied are. See cell B2 in example below.
This page filter for State has two states chosen to display. Looking at the Pivot Table you can’t tell which states. You can see it in the filter selection (below) but not on the report.
Enter Slicers. A Slicer performs a filter on a Pivot Table. It is a graphic object that can be moved anywhere on the sheet and it also displays the applied filters.
(The file I used for the screen shots can be downloaded at the end of the post.)
To add a Slicer simply click a cell in the Pivot Table and click the Options Ribbon tab and click the Insert Slicer icon drop down and choose Insert Slicer.
You then choose the field to link to the Slicer and click OK. You can choose multiple fields.
A Slicer is added to the sheet. You can move and re-size the Slicer. The Slicer below is performing the same State filter as before and it’s obvious what filters are in place.
You can hold down the Ctrl or Shift keys to select multiple items.
The symbol in the top right of the Slicer clears all the filters.
When you select the Slicer its Options Ribbon tab is displayed. This is where you can change the colours and sizes of the Slicers.
Filter Multiple Pivot Tables
The other advantage of Slicers is they can be linked to more than one Pivot Table. Normal Pivot Table filters won’t allow you to do that. One Slicer can filter multiple Pivot Table reports.
To link the Slicer to another Pivot Table right click the Slicer and choose PivotTable Connections.
Then select the other Pivot Table(s) to filter and Click OK.
Below are two Pivot Table reports filtered by the same Slicer.