Sign in or start a free trial to avail of this feature.
10. Pivot Table Slicers
Slicers are new to Excel 2010 and allow you to perform much better manual selection filters than before
Inserting Slicers (00:04)
Slicers are an intuitive method of filtering a Pivot Table using manual selection. Unlike filters, slicers let you see what entries are included and excluded on the Pivot Table, reducing the likelihood of errors. To insert a slicer, we use the shortcut Alt, J, T, S, F. We then select the fields that we want to use to create slicers. In our case, we create slicers for subscription type, quarters and salesperson. This creates three slicers, one for each field.
Using Slicers (01:02)
Each slicer contains a list of the possible values for its field. The Pivot Table automatically filters to show only data for the values selected in each slicer. By default, all the values in each field are selected. We can deselect one entry from a slicer by holding Control and clicking it. Selecting an entry from a slicer without holding Control will select only that value.
Slicers are easy to modify. For example, you may want to resize a slicer to ensure you can see all the entries without scrolling. This is easily done by clicking and dragging. You can remove the filter from a slicer by clicking the icon in the top right corner or by selecting the slicer and pressing Alt + C.
In the previous lesson, we learned how to apply filters to a pivot table.
In this lesson, we'll learn how to filter a pivot table using slicers.
Slicers are a great way of filtering by manual selection, particularly if we have a limited number of entries in our labels.
We'll insert a slicer by accessing the command in the ribbon with alt, J, T, S, F. You can also use the mouse to access this command.
When we insert slicers, we need to select the fields that we wish to slice. We'll select subscription type, quarters, and salesperson.
We'll press okay.
And this creates three slicer windows.
We'll move these, so they don't appear above our pivot table.
By default, all the entries in each field are selected as shown by the blue background on each button. To remove one entry from the list, we press control, and click. Let's say we want to remove Quarter Four revenues from the pivot table. We'll navigate to that button, hold control, and click.
Now we can see that this column has been removed from the pivot table.
If we would like to deselect all of the options, and add entries one by one, we can simply click on a button. When we click on Baines, he is now the only salesperson on the pivot table. If we click on Batiste, Baines is removed, and only Batiste is left. If we want to add Baines and Batiste, we'll just control, and click on Baines.
We can add additional salespeople in this way, such as Hitchcock and Levy.
Unlike the filters we covered in the previous lesson, slicers allow you to view exactly what entries are excluded from your pivot table, reducing the likelihood of errors. When I'm using slicers, I like to see the full menu of entries at all times, and not to have any scroll bars appear in the slicer window. If we click on a slicer window, we can extend it to the bottom by clicking and dragging.
This shows us more salesperson entries.
If we scroll up to the top, we can now view all the salespeople on one screen. Slicers have a number of additional options available in the ribbon. The most important of these is pivot table connections. If you have more than one pivot table in your workbook, this button will allow you to control multiple pivot tables with just one slicer. This saves a lot of time when applying multiple filters.
To remove a slicer filter, we can press this icon in the top right hand corner, or press alt C, which removes the filter from the selected window. As expected, all of our salespeople now return to the pivot table.
Let's stop the lesson here.
In the next lesson, we'll learn how to build charts from a pivot table.