Join Curt Frye for an in-depth discussion in this video Filtering a PivotTable using slicers, part of Excel for Mac 2016: Pivot Tables in Depth.
- [Voiceover] When you filter a pivot table Excel indicates that a field is filtered by placing filter icons in the body of the pivot table and in the pivot table field list. Unfortunately those icons are small and hard to see and give no indication of which values are included in or excluded from the filter. In Excel 2016 you can filter your pivot tables by using slicers, which graphically indicate which values are included and excluded by a filter. I will show you how to use slicers in this movie and my sample workbook is the slicers Excel file, which you can find in the chapter three folder of the exercise files collection.
Like I said, a slicer is a visual indicator of the way that a filter has been applied. It shows values that have been included and excluded. To filter a pivot table by using a slicer click any cell in the body of the pivot table and then on the analyze contextual tab, click the insert slicer button. The insert slicer's dialog box appears and it provides a list of the fields that are available to you. You can create more than one slicer at a time, but in this case I'll just stay with one, and I'll select quarter.
So I'll check that box and click okay, and my slicer appears. I'm gonna close the pivot table builder dialog box or task pane, and I'll drag quarter over to the side, so we can get a better look. Right now all quarters of data are displayed and also note that quarter isn't used in the body of the pivot table at the moment, instead I'll just use it as a separate filter. Let's say that I only want to see the values for quarter one in the years 2014 and 2015.
I can create that filter by going to the slicer and clicking the control for one. And you can see that the values have been filtered, so now I only see results for January, February, and March, which is the first quarter of the year. And if I want to switch to quarter four I can click that and my pivot table filter changes. You can also select multiple fields. So let's say that I want to see the results for say quarters three and four. Four is already selected, so I'll hold down the Command key and click the control for number three.
And now I see six months, the last six months for both 2014 and 2015. And if I want to go back to quarter one I can click there. If I want to select a range of values, in this case say quarters two through four, then I can use the Shift key. I'll click the first cell, or rather the first item that I want to include, and because I want to include two, three, and four, which are all in a row, they're together, I can hold down the Shift key and click four.
Shift + clicking creates a sequence. It selects everything from the beginning to the end. And that's why I had two, three, and four selected the way I did. And again, if I want to go back to one I can just click one. If you want to remove a slicer filter then you can click the clear filter button, which is on the title bar of the slicer itself. And to get rid of the slicer hold down the Control key and click the title bar of the slicer and from the shortcut menu that appears click remove quarter.
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 values, especially if your pivot table takes up more than half of the screen after you apply the filter.
- Formatting data for use in a PivotTable
- Creating a new PivotTable
- Creating a Recommended PivotTable
- Connecting to external data sources
- Summarizing totals and other data fields
- Creating calculated fields
- Summarizing field data
- Sorting and filtering PivotTables
- Working with Excel slicers
- Formatting PivotTables styles and layouts
- Applying conditional formats