When you filter a PivotTable, Excel indicates that a field has been filtered by placing dilter icons in the body of the PivotTable and in the PivotTable Field List. Unfortunately, these icons are small and somewhat hard to see and also give no indication of which values are included in or excluded from the filter. In Excel 2010, you can filter your PivotTables by using slicers, which graphically indicate which values are included and excluded by a filter. To filter a PivotTable using the values in the field, just click any cell to activate the PivotTable and then on the Options contextual tab click the Insert Slicer button to display the Insert Slicers dialog box.
Now, you can check the box next to any of the fields for which you want to create a slicer. So let's say in this case I will do it for Month. So I'll just check that box and click OK. When I do, Excel creates a slicer that has an entry for each month that's displayed in the PivotTable. When Excel created the slicer, it isn't large enough to display all the values. So I'll move my mouse-pointer over the bottom edge of the slicer and when the mouse pointer changes to an up- and down-pointing arrow, I can drag down to resize it, and now we can see all of the months.
So now let's say that I want to display only those values for the month of May. To do that, I just click the May button. When I do, Excel displays only the values for 2009 and 2010 for the month of May. Now, let's say that I only want to see the values for May and September. To select both of those months, first I select the one that I want, in this case May, and then hold down the Ctrl key and click the second month that I want to display, in this case September. After I release the left-mouse button, Excel updates the Slicer to indicate that both May and September are selected and the PivotTable updates to show the data for May and September within it.
Now let's say that I want to see all the data for January through April. Instead of Ctrl+Clicking each individual month, what I can do is Shift+Click and that will select every month in that range. So let's say that I click January and then hold down the Shift key and because I want to see January through April, I click the month of April. When I do, Excel selects each of the months in the range January through April. If you want to clear filter applied by a slicer, then you can click the Clear Filter icon, which is here at the top- right corner of the slicer, and also you can create slicers for more than one field at a time.
So let's say that in addition to month I want to filter by Company. To do that I'll click any cell within the PivotTable and then on the Options contextual tab click the Insert Slicer button again. Now, I'll select a slicer for a Company. So I'll check that box, click OK, and I get my second slicer this time for Company. So let's say that I only want to see the values for FirmB in August and September. So click FirmB and I'm interested in the months of August and September.
So I'll click August and then Ctrl+Click September to display only the values for FirmB in August and September of 2009 and 2010. With that work done, I can clear the slicers by clicking the Clear Filter button in each of them and then I can remove the slicers by right-clicking it, and then in the Shortcut menu that appears, click Remove "Company" to get rid of the Company slicer and do the same for Month, clicking Remove "Month" to get rid of that slicer.
Filtering your PivotTables 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 PivotTable takes up more than half the screen after you apply the filter.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86641 Viewers
80 Video lessons · 135961 Viewers
59 Video lessons · 54700 Viewers
52 Video lessons · 68595 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.