Share [Social9_Share]

Slicer Formatting Tricks

Slicers 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

Slicers Formatting Tricks 1

  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!

Slicers Formatting Tricks 2

  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

Slicers Formatting Tricks 3

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

Slicers Formatting Tricks 4

  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

Slicers Formatting Tricks 7

  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

Slicers Formatting Tricks 5

  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

Slicers Formatting Tricks 6

 

4. Slicer Built in Styles

Slicers Formatting Tricks 8

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

Slicers Formatting Tricks 9

  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

Slicers Formatting Tricks 10

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

Slicers Formatting Tricks 11

  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

Slicers Formatting Tricks 12

 

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

 

 

  • a.rakesh patro

    hey last one is niceone:)

  • Sonika Singh

    Hi, I have been using slicers since sometime now, it needs a lot of time for customizing it, but, its worth spending time with the end result, one of my slicers look

  • GraH

    Careful with this if you save in .xlsb. Same for table formatting. I haven’t found the reason yet, but when I customize (pivot)table/slicers, I often get xml errors (unreadable content warning) when I open the workbook and consequentially formats are gone. Sometimes even in plain xlsx format it happens also. So I tend to stick to standard themes.
    At work we still are using version 2010… :-(, but then very sad.

    But it is great stuff when it works, like in your entry for Chandoo’s visualisation context. Loved it!

    • Thanks GarH! I have not found a problem with customization but i’ll try fiddling around with binary workbooks as well!

      Now its time that you update to 2013 / 2016. Even PowerBI has made some good changes in the newer versions 🙂

      • GraH

        You’re welcome, Chandeep.
        BTW: I have 2016 at home. Have asked to be part of the test panel for newer office versions @ work, but IT is not responsive. It took me 2 years before power-pivot add-on was finally distributed. Hence the very unhappy smiley. Why is it still called a smiley?
        About the xml errors: tried to find some answers on the net, but remain unsuccessful for the the moment.

Chandeep

Popular Posts

© Goodly