Join Curt Frye for an in-depth discussion in this video Filtering a PivotTable using slicers, part of Excel 2016: Pivot Tables in Depth.
- When you filter a pivot table Excel indicates that the field is filtered by placing a filter icon in the body of the pivot table and also in the pivot table field list. Unfortunately, those icons are small and hard to see. And give no indication of which values are included or excluded from the filter. In Excel 2016 you can filter your pivot table by using slicers. Which graphically indicate which values are included and excluded by a filter.
I will show you how to work with slicers using the slicers Excel sample file that is a workbook you can find in the chapter three folder of your exercise files collection. To work with slicers, click any cell in your pivot table. And then on the analyze contextual tab, click the insert slicer button. That's in the filter group. The insert slicers dialog box appears. And now you can check the box next to any field for which you want to display a slicer.
In this case I'll just do room type. So I'll check the room type box and click ok. Excel creates my slicer and I can drag it like any other shape, over to the side. And you can also see that because the slicer is selected the pivot tables field task pane has been hidden for the time being. The slicer displays the field name, which is room type, and also values that are currently displayed. Those are typically a darker color verses white or another light color based on the formatting that you apply.
If I want to only display values for the Cambridge room type, I can click the Cambridge pill. That displays only Cambridge, and we have hidden Piccadilly, Oxford, and Westminster. If I want to switch to Oxford, I can click that item and it's displayed. One thing that's new, there's a new feature in Excel 2016, that allows you to select multiple items at a time without using your shift key or control key.
On the title bar of the slicer, click the multi-select button, or press alt + s, and now you can select multiple items by clicking them individually. So I can do Oxford and Cambridge. If I want to remove an item from a multi-select slicer filter, I can click it to deactivate it. So if I want to hide values for Cambridge, I can click the Cambridge item and it's removed from the pivot table.
If I want to turn off multi-select I can just click it's button, and now clicking items individually switches to that item instead of adding it to the list of values that are displayed. If I want to clear the slicer filter I can go to the title bar and click clear filter or press alt + c, and if I want to get rid of the slicer entirely, I can right-click the title bar of the slicer and click remove room type.
And of course the name to the right of your move will reflect the field that you created the slicer for. Filtering your pivot tables using slicers helps you and your audience visualize which values are included and excluded from your filter. Slicers work best for filtering fields with 20 or fewer unique values. Especially if your pivot table takes up more than half of the screen after you apply the filter.
- Identify how to format data for use in a PivotTable.
- Discover how to create a new PivotTable.
- Define how to connect to data sources.
- Evaluate and consolidate data from multiple data sources.
- Identify how to create calculated fields.
- Summarize field data.
- Review PivotTable sorting.
- Review PivotTable filters.
- Discover how to work with Excel slicers.
- Recall how to format PivotTables with styles and formats.
- Assess how to build PivotCharts.
- Assess how to run macros.
- Evaluate how to create a PivotTable using the data model.