Getting Fancy with your Excel Slicers

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
  • Header
  • 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.

39 thoughts on “Getting Fancy with your Excel Slicers

  1. Dave

    Really excellent format and layout ideas Mike. Along with your dashboard tools these will really help improve the quality feel of my dashboards! Thank you!

  2. Bob Phillips

    Oddly enough, I was talking to Roger Govier at the UK MVP Open Day in Dublin last week on this very topic. Roger gave me another tip for copying styles, namely to copy the sheet using the custom style to another workbook. That other workbook then inherits the style, and the copied sheet can be deleted, the style is now tied to the workbook not the sheet. Templates are a more controlled way perhaps, but this is a good way to get a style not in a template.

  3. Grant

    Amazing – thanks Rob. Bob’s suggestion works great too! I did what he suggests, copying the worksheet into my Book.xltm template – thereby keeping them available in every new book I create.

    Thanks!

  4. Bob Phillips

    Mike, I tried to recreate that nice tabbed effect, and it was all fine except that I couldn’t get the slicer to ‘Send to Back’, those options were grayed out, and so the top of the pivot got whited. Is there something I need to do to allow this.

  5. Datpig

    Bob: I cheated a little by using another shape to hide the slicer behind. Download the sample file at the end of the article to see what I mean.

    By the way, copying a sheet to inherit the styles is a fantastic tip. I didn’t know that. Thanks to Roger.

  6. AlexJ

    Mike,
    On “Reusing Custom Styles”, I have found that I can copy just the slicer from one workbook to another. That establishes the Custom Style in the new book for application to local slicers. Later I can delete the copied slicer.

  7. plotka

    My wife and i have been very joyful that Edward could do his studies with the precious recommendations he discovered out of your web pages. It is now and again perplexing to just be giving away tips and hints which some others might have been selling. And now we keep in mind we have got the website owner to appreciate because of that. The illustrations you made, the straightforward web site navigation, the relationships your site give support to foster – it’s many exceptional, and it is letting our son and the family reason why this content is brilliant, and that is seriously important. Many thanks for the whole lot!

  8. Leon Kowalski

    MIke, excellent article and just what I needed. As everyone else has said, the tab idea is fabulous. Easily creating custom styles from others is also just brilliant – I was a little perturbed by the amount of options presented and had not thought to experiment until I read your article.

  9. Frank

    Love the idea of the Tabs and placing the slicer behind the pivot information.

    I am new to the slicer concept.

    I cannot see or understand how you created Slicer Boxes with Tabs I have spent many hours on this, now getting frustrated.

    Could you please assist, it’s a vast improvement over stock slicers.

    Regards
    Frank

  10. Sunil

    Thanks for the tips, Mike.

    I have a question around number formatting: the column that I wish to add to the slicer is a currency column with 2 decimal places. For e.g. $1.00, $1.05, etc. When I add this column to a slicer, the entries in the slicer window show up as 1, 1.05, 1.1, etc. i.e. the formatting reverts back to general. Would you know how to fix this?

  11. indzara

    Thanks for the article. I have not customized the slicer style until now. I was thinking of giving a new look to my latest Excel template as the number of default styles is limited. 🙂 I created a new style and then didn’t find a ‘modify’ option and searched on google. I found this article. Now I know how to modify. Thank you.

  12. Tom

    Is there a way if 10 items are part of a slicer to revmove or only show 5 of the items?

  13. SD

    How can you change the slicer properties so that the user can ONLY select one item at a time in the slicer? (no multiple selections allowed). My slicer has MMM-YYYY Dates and selecting multiple Dates, is yielding data that is not meaningful.

    Thanks

  14. Jason H

    Just found these suggestions and really liked the tab style. I followed the suggested method of using a shape to overlay a portion of the slicer which looked great in Excel. However, when I published to SharePoint 2013 I found that SP doesn’t support custom shapes. Bummer! As I was walking my dog Delta last night I came up with the idea of creating a dummy slicer with everything – all of the fonts and fill colors for all of the options set to the same color, with the header turned off too. I used that dummy slicer instead of a shape to overlay the slicer I want to show in tab style. This published to SharePoint and looks great!

  15. Kombarris

    Thanks for the great tip! One question though – What’s the recommended way for soft coding Slicer Labels? Some of the spreadsheets that I create need to support different languages. For normal cells,I do this by creating a cross reference worksheet with all the supported languages. Any ideas how I can achieve the same with Slicers?

  16. Anna Alishauskas

    I am new to slicers in Excel and this was a most excellent article- thank you. I have a question though- how do I “turn off” the header area in a slicer? Like you have done in your quasi-tabbed (and very cool) example. I am grateful for any tidbits you can throw my way 🙂

  17. Paranam Kid

    Brilliant, thanks so muh. Like other posters here, the tab style is very interesting. But I am a bit disappointed, like some of the others no doubt, that none of the questions asked here have been answered. What is the point of having a comments section if the author of the article does not participate?

  18. Rakesh kumar

    Slicer is a great button or tool in the excel which gives us to filter out particular fields with items in the field in the visual way.Since i was novice for the slicer now i have improved a lot.
    You can use it to innovate your pivot table or pivot charts in interactive way.

  19. point

    Hel?o it’s me, I am also visiting th?s site ?egularly,
    this ??b page is really pleasant and the ?sers ?re tru?y sharing
    nice thoughts.

Leave a Reply

Your email address will not be published. Required fields are marked *