Microsoft Office Tutorials and References
In Depth Information
Chapter 28: Using Slicers to Filter Pivot Tables
Using Slicers to Filter Pivot Tables
The new versions of Excel 2010 incorporate special button-style filters named
Slicers. This new tool makes it possible to filter Pivot Table data while indicating
the filtering state of the data/filter, thus enabling you to understand and visualize
which data is part of the report while you view it.
In a way the Slicer is also like a Scenario Manager
when you filter certain data
you see its impact on the PivotTable data. This is a way to understand Pivot Tables
better. Adding the Slicer makes it much easier to manipulate the data.
Refer back to the data we used in Chapter 27 on the sheet Example. The sheet Pivot
Table has an example of a PivotTable created from the data on the sheet Example.
See Figure 28.1.
Using conventional Pivot Table filters does not have the same effect as the Slicer
does. Follow these steps to create a Slicer for the above example.
To activate Slicer, click on the Slicer icon under the Insert Ribbon. The Insert
Slicer menu will appear. You can choose any or as many of the parameters/headers,
you would like to use for filtering the data. In this example, I selected Own Home
and Children. See Figure 28.2.
Figure 28.3 shows both Slicers on the sheet. One is for Own Home and the
other is for the number of Children. The Slicer filters window displays the Slicer/
filter tool alongside the filtered data. Before we use the Slicers, the data shows
both No and Yes on the Own Home and all number of children combinations
on the Children Slicer. Note how neither Slicer has its filter icon on the top left
corner of the Slicer activated. This filter icon status will change when you activate
If you wish, for example, to filter the PivotTable for Home Owners and
customers with two (2) children or less, you can do so using the Slicers.
To filter the Own Home Slicer menu: Click on the Yes button. For the Children,
click on 0 and SHIFT
Click on 2. This will select the range including 0, 1, and 2.
Another alternative is to click on 0 (or any other button), keep the CTRL key
pressed down, and click on any other options in the pivot table data you wish to
select. See Figure 28.4 for the results.
Note how both Slicers have their filter icons show that they are active. In
addition, verify that the Filter Icons are visibly active, at the right side of the
PivotTable Field List, next to the two filtered items.