Slicer Formatting Tricks

Yesterday I wrote a pretty comprehensive post on creating slicers in Pivot Tables. Taking our understanding on slicers a bit further, I today want to talk about formatting slicers.

Now even the default design of the slicers look pretty catchy but when you see the insane possibilities of customizing the looks of the slicer, default formatting becomes a no – no

In this post I am going to talk in detail about

1. Adding/Deleting elements of the Slicer
2. Slicer Options and Tools : Alignment, Default Styles, Sizes of Buttons and Slicers
3. Modifying a Slicer + Its advantages and drawbacks
4. How to copy Slicer Styles
5.  General guidelines for formatting Slicers

 

Adding Deleting Elements of a the Slicer

Take a look at this slicer. Notice 2 things

1. Although the Slicer Heading (Region) is written on the top but it is pretty evident that North, West… are Regions. So we can remove the heading
2. There are 2 buttons (North East and South West) that are not active currently, unless you want to show the unactive buttons, it is a good practice to hide them as well

Here is how you can do it!

1. Right click on the Slicer and choose Slicer Settings
2. In Slicer Settings – un-check ‘Display Header’ and check ‘Hide items with no data’
3. Note that you can also give a custom slicer heading
4. And there are also options to sort the data or sort it using custom sorting lists

 

Slicer Options and Tools

The next thing that I want to talk about are the options which are available to format the slicer. Note that the options only get highlighted when the slicer is selected

This is the Options Tab for Slicers and we are going to touch upon 4 essential features in this menu

1. Align Multiple Slicers with a Single Click : Assume that we have 2 slicers in our report: Customer and Region Slicer, now it will be a lot better if the slicers are aligned properly in the spreadsheet

1. Select both the slicers
2. In the Options Tab Click on Align
3. And the click on Align Top (or whatever alignment pattern you would like to give)

 

Agreed!! You can do it manually by dragging the slicer but why stick to approximation when you can get the exact alignment with less effort. I wrote and article on how to customize your Quick Access Toolbar to speed up alignment (it is pretty relevant to excel as well)

 

2. Slicer Sizing Options : Additionally I also want all my slicers to be of the same height. Pretty simple just adjust the height in the Options Tab.. take a look

1. Select both the Slicers
2. And specify a common height and done
3. Similarly if you want, you can change the width as well

Although you can change the size by dragging the slicer but you will need to exactly specify the height/width when you are sizing multiple slicers together

 

3. Slicer Button Options : By default the slicer is displayed vertical with buttons in a single column but if you want to arrange the buttons in multiple columns, here is how you can do it

1. Select the Slicer and go to Options
2. Change in the number of columns to 4 (depending on your need)
3. All buttons will now be arranged in 4 columns. Just resize the Slicer

 

You can also change the size of the buttons by specifying a particular height or width

 

4. Slicer Built in Styles

There are plenty of built in styles to suit your need and good part about using these styles is that they are quick and the bad part is that these style are pretty regular and not off beat!

 

How to modify the looks of the slicer!

You can further modify the looks of the slicer by formatting each element of it. Just to give you an analogy it is like converting a Standard Fiat into a Convertible Merc

Step 1) Duplicate a Standard Slicer Style

1. Select the Slicer then in the Options tab
2. Under Slicer Styles, Right click on active style and click Duplicate
3. You’ll have ‘Modify Slicer Style’ box displayed which has all the formatting options

 

Step 2) Modify the Duplicated Style

You can explore each element and format it the way you want! There are a few drawbacks while modifying the slicer looks

1. It takes a hell lot of time to make it look sexy!
2. Since there is no live preview of your formatting changes, you have to apply the changes to see the result

But if you are one of those guys who is insanely crazy about the design, I strongly recommend you customize the slicer styles

 

Else there are ways to hack modified Slicer Styles..

Let’s say for example you have liked my slicer style from a recent dashboard on Company Cost Structure Analysis and you want your slicers to look the same. Here is what you can do

1. Copy the slicer that you like, into your workbook (Simple Ctrl C and Ctrl V operation)
2. Select the slicer that you want to format
3. Now go to slicer styles and you’ll see that the style of the slicer that you just copied is now available. Click on it and boom!
4. This is how the slicer will look after you apply the new style

 

General Guidelines for Formatting Slicers

1. Match the Slicer formatting with the Dashboard/Report theme – Use colors and patters in which your dashboard is made that will provide overall look and feel consistency of your dashboard
2. Integrate Slicers Seamlessly – Have proper alignment, color & size consistency of all the slicers. This is important to make sure that your slicers look seemless in your dashboard
3. Remove unnecessary clutter from the slicers – Headlines (if not needed), Borders etc..

 

Some Dashboards where I have actively used slicers

1. Cost Structure Visualization Dashboard
2. 30 Day Challenge Dashboard (used timelines)

 

Other Formatting Tips and Tricks

1. 8 Charting Formatting Practices
2. 5 Quick Data Formatting Tips
3. Beauty Tips for your Excel Reports
4. Best formatting practices for building financial projections