During last week’s BI Boot Camp, I demonstrated how to customize Excel Slicers to better fit into the color scheme and theme of a dashboard. The process of fully customizing a slicer is a bit of a drag. But once you have the hang of it, you’ll never use the default slicers again. In this post, I’ll give you a few ideas for your slicers and show you how the customization works.
First, let’s go through a few examples of some fancy slicers.
Tabs: With a little customization and a few tricks, I made this slicer look like tabs behind my pivot table.
Clean Dashboard Look: I customized this slicer so that any values with no data are kept subdued with no buttons around them. The modern color scheme gives it a clean dashboard feel.
Chalk Board: I went silly with this slicer by making it look like chalk board. Note that Excel allows the customization of font type, font size, and even strikethrough for values with no data.
Old School Web Feel: Believe it or not, this is a slicer. Again, this is just a silly example that demonstrates how you can apply various font effects to create a slicer that fits almost any environment.
Parchment Paper: Dost thou know who made this stupid slicer? I shall beat him about the ears for one fortnight.
How to Customize your Slicers
Slicers have three major types of customization:
- customize button size and layout
- customize behavior and captions
- customize styles
To customize button size and layout, simply click on the target slicer and select the Slicer Tools Options tab. Here, you’ll find property settings to specify how big you want your buttons to be, how many columns of buttons you want in your slicer, and how tall and wide you need the slicer to be.
To customize behavior and captions, right-click your slicer and select the Slicer Settings option.
This will open a dialog box where you can choose a name for your slicer, select whether to show the header or not, specify how the values in the slicer are sorted, and define how the slicer interacts with the data and other slicers.
To change the look and feel of the slicer, you’ll have to get into some style customizations.
After you’ve created your slicer, go to the Slicer Tools Options tab and expand the Slicer Styles gallery. Click the New Slicer Style button at the bottom of the gallery.
Excel will activate a new dialog box.
Here, you’ll first need to name your style.
Next, the idea is to select each slicer element and then format that element by clicking the format button. Simply choose the formatting for the selected element and then move on to the next element.
Sound easy enough right?
Well not for some of us.
I have I to admit, I initially had trouble with understanding which element affected which part of the slicer.
In the list of Slicer Elements, you’ll see:
- Whole Slicer
- Selected Item with Data
- Selected Item with no Data
- Unselected Item with Data
- Unselected Item with no Data
- Hovered Selected Item with Data
- Hovered Selected Item with no Data
- Hovered Unselected Item with Data
- Hovered Unselected Item with no Data
Whole Slicer and Header are fairly self-explanatory, but what does the other junk mean?
Well the other options refer to the values within the slicer. Some values have data associated with them while others do not. The elements listed lets you define what each value (values “with Data” and values “with no Data”) look like when selected, unselected, and hovered over. Here’s a nifty visual to help understand. Note how each type of value has a different formatting.
Once you finish going through all the elements, you can select your newly created style in the Slicer Styles Gallery.
You can also right-click on your custom style to Modify, Duplicate, and Delete it.
Reusing your Custom Styles
Custom styles are saved at the workbook level. This means that while your custom style is saved and travels with your workbook, any new workbook will not have your styles included. The workaround for this limitation is save your workbook as an Excel template. To do so, choose to save the workbook with your custom slicers as an Excel Template (*.xltx) file. Once you’ve got that saved, you can open your template file to start a new workbook with your custom style included.
There you go – another fancy tip for you.
Feel free to download my sample file if you’re interested.