10. Pivot Table Slicers
Slicers are new to Excel 2010 and allow you to perform much better manual selection filters than before
Slicers for filtering
- Slicers allow you to visually see what field entries are being filtered
- This makes them a better alternative to manual selection filters
- Unfortunately slicers are only available in Excel 2010 onwards
Useful keyboard shortcuts
ALT + J, Y, S, L: Insert slicer
CTRL + CLICK: Change filter status of selected entry without affecting other entries
ALT + C: Clear selected slicer filter
Slicers are a new addition to Power Point 2010 and 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 Y S L, but you can also use the mouse to access this command.
When we insert Slicers, we can now select the fields that we wish to slice.
I’m going to select Subscription type, Quarters and Sales person.
I’ll press OK, and this creates three Slicer windows.
I’ll move these so that they don’t appear above our Pivot table.
And I may need to adjust it a little more to the right.
There we go.
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 Ctrl and click.
So let’s say I wanted to remove Quarter four revenues from the Pivot table.
I can navigate to that button, hold Ctrl and click.
And now you can see that this column has been removed from the Pivot table.
If you would like to deselect all of the options and add entries one by one, you can simply click on a button.
So when I click on Baines, he is now the only Sales person on the Pivot table.
If I click on Batiste, Baines is removed and only Batiste is left.
If I want to add Baines and Batiste, I’ll just Ctrl and click on Baines.
And I can add additional Sales people 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 windows.
If you click on a Slicer window, you can extend it to the bottom by clicking and dragging, and this will show me more Sales person entries.
If I now scroll up to the top, I can now view all my Sales people 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 work book, this button will allow you to control multiple Pivot tables with just one Slicer, saving you 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.
And as you’d expect, all of our Sales people now return to the Pivot table.
Unfortunately, Slicers have one big drawback.
Because they were only introduced to Microsoft 2010, Slicers are not compatible with earlier versions of Excel, and so shouldn’t be circulated to individuals who have these earlier versions installed on their machine.
However, for conducting analysis on your own machine, they’re a great tool for quickly filtering Pivot tables by manual selection.