Have you ever needed to apply filters to a Pivot Table? If so you’ll know how cumbersome it can be.
First of all you have to add the field to be used as the filter to the Filters section of the Pivot Table Fields Panel. This adds the Filter directly above the Pivot Table where it must remain (don’t bother trying to move it – you can’t).
Then you’ll probably need to make the columns wider to accommodate the Filter heading and values in the drop-down, which will have a knock-on effect on the Pivot Table underneath. Then you’ll need to change the colour of the cells containing the Filter so they match the colours of the Pivot Table.
In the example below I want to filter 3 Pivot Tables by Department so I need to add a Department Filter to each Pivot Table which means 3x the work. Then when I want to apply the filter I have to apply it to each Pivot Table. And don’t get me started on “Multiple Items” which is the text that you see in the Filter when you select more than one item.
So this is where Slicers come in. Slicers perform the same job as the “old school filters” but they have more functionality and are much more user-friendly.
To add a Slicer to a worksheet…select a cell in a Pivot Table and select Pivot Table Analyse > Insert Slicer and hey presto, a Slicer is added to the sheet.
But actually there’s much more to Slicers than that as you’ll find out if you watch the video below which covers 5 Slicer-related tricks. These tricks will work on Excel for Mac as well as Excel for Windows.
Multi-Pivot Table Filtering
Learn how to use a single Slicer for simultaneous filtering across multiple Pivot Tables, saving you valuable time and effort. In the screenshot below there is one Slicer which is attached to 3 Pivot Tables. This required 1/3 of the work compared to using “old school filters”
I’ll show you how to switch a Slicer’s layout from vertical to horizontal, enhancing its fit and functionality within your spreadsheet. In the screenshot below there is a horizontal Slicer and a vertical one.
Discover how to modify the Slicer’s heading text to make it more user-friendly and relevant to your specific data context.
Button and Background Colour Customization
Resize Slicer buttons and change the Slicer’s background colour to improve visual impact and readability. Note: the Windows version of Excel has more functionality than the Mac version here.
Slicers in Tables
Slicers were introduced into Excel in 2010 and initially only worked with Pivot Tables but since 2013 Slicers work on Tables too.
If you want a copy of the file that I used in the video, it’s available in the YouTube description.