Not cake, sorry. I’m talking about Excel Slicers and while they are 'sweeeet', as my 8 year old says, and a piece of cake to use, they aren't quite as tasty.
Now I know you can already filter using the PivotTable or Excel Table filter tools but Slicers are better for 2 reasons:
- They can control the filtering of multiple PivotTables/Charts (but only one Table)
- They look nicer and are more intuitive to use
Have a go yourself using the interactive workbook below.
Warning: don’t go silly and choose too many areas though, or the PivotChart might implode….. what am I thinking? That’s like a red rag to a bull, of course you’re going to try it now that I mention it but at least I warned you!
Data used in chart above is from Greater London Authority (Microsoft Azure Marketplace).
Pretty cool, eh? I'm sure you're now itching to get started with your own Slicers, so here's how:
To insert a Slicer first select a cell in the Table or PivotTable that you want the Slicer to control > then on the Insert tab of the ribbon > in the Filters group you’ll find the Slicer icon.
Tip: In Excel 2013 you can also right-click on the Field in the PivotTable Field List and choose ‘Add as Slicer’:
Connecting Slicers to Multiple PivotTables/Charts
Once you’ve inserted your Slicer you can go about choosing which PivotTables/Charts you want it to control.
To do this; right-click the Slicer and choose Report Connections. This will open the dialog box below and you can check the box for the PivotTables you want the Slicer to control:
Note: A Slicer can only control PivotTables which share the same Pivot Cache. Typically PivotTables which reference the same data source share a Pivot Cache, but not always. If the PivotTables you want to connect to don't appear in the list then you'll know the cause is separate Pivot Caches
Slicers are intuitive to use and they allow us to easily filter one or multiple items:
- Click one item to apply the filter for that area
- Click and drag through items to select more than one, or
- Click the first item > hold down SHIFT and click on the last item in your range
- Hold CTRL while clicking to select non-contiguous items
The Slicer displays the selected items in a different colour giving a visual indicator to the user:
And to remove all filters simply click the red x at the top right of the Slicer.
One of the gripes I had with Slicers in the early days was that they were a bit chunky. Since then I’ve found some of the formatting tricks hidden deep down in the menus that allow you to make them a more manageable size.
I’ll take you through the obvious ones first and then I’ll show you the secret ones 😉
Select the outer edge of the Slicer to reveal the Slicer Tools; Options menu. Here we can access the Slicer Settings (Tip: you can also right-click the Slicer to access the Settings):
- Change the Slicer name
- Turn the Slicer header off/on, or give it a different caption
- Choose how to sort the Slicer
- Choose how the Slicer should handle items with no data. Note: in Excel 2010 you don't have the option to 'Hide items with no data'.
In the Slicer Styles group we can choose the colour and style, or create a new style (note: the colour options will be based on the Theme/Colors you have selected for the workbook in the Page Layout tab of the ribbon, mine is ‘Paper’):
The Arrange group of tools allow you to quickly align your Slicers or move them behind or in front of other objects like charts, shapes, images etc.:
Buttons and Size
The last groups are Buttons and Size; here you can change the number of columns, button height and width and the overall size of the slicer (Tip: you can also use the pull handles which are available when you click the outer edge of the Slicer to change its overall size):
I generally find anything smaller than 0.5cm for the button height is as small as you can go.
If the overall height of your Slicer is too small to display all of the values a scroll bar will be inserted:
Now, while all of the above formatting options are great, they still only allow you to make your Slicer a bit smaller and it’s usually not enough, especially in dashboard reports where spreadsheet Real Estate is at a premium. That’s where the ‘Secret’ formatting options are essential…. Ok, they’re not really secret but they’re not obvious either.
New Slicer Styles
One option is to create your own Slicer Style. You can create a style from scratch on the Slicer Tools: Options tab > in the Slicer Styles group click on the down arrow to expand the gallery > click New Slicer Style:
This will open the ‘New Slicer Style’ dialog box where you can format each of the 10 Slicer elements exactly as you want:
You can even check the ‘Set as default slicer style for this document’ box and use it over and over again, but wait right there because I have a quicker way.
Copy and Modify Slicers
I prefer to copy a Slicer that has the colours I want to use, and then I can just modify the font size and get rid of the border to make it even smaller. To copy a style right-click the style in the gallery > Duplicate.
This will open the Modify Slicer Style dialog box:
Here you can give it a new name, then from the Slicer Elements list select ‘Whole Slicer’ and click on Format > in the ‘Font’ tab change the font size; I like 9, but it will depend on the font you choose:
Then go to the Border tab and remove the border:
Note: While removing the border serves to make the Slicer appear smaller it is actually still the same size. However, now you can squeeze it into a smaller space by placing the edges of the Slicer underneath other objects, like charts, without it being noticable.
After duplicating your Slicer style you then need to apply it to your Slicers. If you have multiple Slicers you can select them all (hold down SHIFT while you select each one), then click on your new style in the Slicer Style gallery.
Ok, now that your Slicer is more compact you’ll be able to squeeze it into your report.
If you also think Slicers are cool, or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.