Microsoft Office Tutorials and References
In Depth Information
Sorting and Filtering Pivot Table Data
Filtering with slicers
To add slicers to your pivot table, you follow just two steps:
1. Click one of the cells in your pivot table to select it and then click the
Insert Slicer option on the Insert Slicer button located in the Sort &
Filter group of the PivotTable Options contextual tab.
Excel opens the Insert Slicers dialog box with a list of all the fields in the
active pivot table.
2. Select the check boxes for all the fields that you want to use in filtering
the pivot table and for which you want slicers created and then click OK.
Excel then adds slicers (as graphic objects — see Chapter 10 for details)
for each pivot table field you select.
After you create slicers for the pivot table, you can use them to filter its data
simply by selecting the items you want displayed in each slicer. You select
items in a slicer by clicking them just as you do cells in a worksheet — hold
down Ctrl as you click nonconsecutive items and Shift to select a series of
Figure 9-7 shows you the sample pivot table after using slicers created for
the Gender, Dept, and Location fields to filter the data so that only salaries
for the men in the Human Resources and Administration departments in the
Boston, Chicago, and San Francisco offices display.
Because slicers are Excel graphic objects (albeit some pretty fancy ones), you
can move, resize, and delete them just as you would any other Excel graphic;
see Chapter 10 for details. To remove a slicer from your pivot table, click it to
select it and then press the Delete key.
Filtering with timelines
Excel 2013 introduces a new way to filter your data with its timeline feature.
You can think of timelines as slicers designed specifically for date fields that
enable you to filter data out of your pivot table that doesn’t fall within a
particular period, thereby allowing you to see timing of trends in your data.
To create a timeline for your pivot table, select a cell in your pivot table and
then click the Insert Timeline button in the Filter group on the Analyze
contextual tab under the PivotTable Tools tab on the Ribbon. Excel then displays
an Insert Timelines dialog box displaying a list of pivot table fields that you
can use in creating the new timeline. After selecting the check box for the
date field you want to use in this dialog box, click OK.