Excel Slicers are great, but they’re a bit on the chunky side and that can be a pain when you’re building reports like Dashboards where space is limited. Unfortunately, the Excel Slicer Formatting available on the Slicer contextual tool tab is limited because you can’t adjust the font size (see below):
I’m going to show you how we can uncover the font settings and make them much smaller.
Watch the Excel Slicer Formatting video
Download Workbook
Contains step by step written instructions with screenshots that you can use as a reference.
Enter your email address below to download the sample workbook.
Excel Slicer Formatting Step by Step
The trick is to create your own custom Slicer style.
Step 1: Select a Slicer to reveal the contextual Slicer Tools; Options tab
Step 2: In the Slicer Styles gallery choose a style that’s close to what you want. Trust me, this will save you time. Right-click the style you like > Duplicate:
Step 3: In the Modify Slicer Style dialog box that opens, give your style a name. Mine is called ‘Compact’. Then select ‘Whole Slicer’ in the Elements list and click ‘Format’:
Step 4: In the Font tab alter the font size as desired. This will allow you to make the button height smaller.
Step 5: Go to the Border tab and set the border to ‘None’. This will enable you to overlap the Slicers slightly, as you won't be constrained by the borders:
Step 6: After you click OK in the Format dialog box you’ll be taken back to the Modify Slicer Style dialog (image below). You can also select the other elements and modify them as required, but generally I’m too busy for fiddling about with that.
Be sure to check the ‘Set as default slicer style for this document’ box:
Your Slicer style will be the first in the gallery:
Now all you need to do is apply it to the Slicers already in your workbook. Tip: Select one Slicer and then press CTRL+A to select all the Slicers. Now you can apply the formatting with one click.
Note: Pressing CTRL+A with at least one Slicer selected will select all objects, so if you have images or shapes in the worksheet CTRL+A will also select them.
Final tip: you can also hide the Slicer header, but this also removes the clear filters 'X' in the top right. You can remove filters by selecting all items in the Slicer, but this may be inconvenient in Slicers with lots of items.
To hide the Slicer header right-click > Slicer Settings:
In the Slicer settings dialog box deselect 'Display Header':
Learn Excel Slicers
Are you new to Slicers? Master them with our step by step Excel Slicers tutorial, including video and workbook.
Power Query Consolidate Excel Sheets
Karin D.
How do I hide ‘(blank)’ from displaying on a slicer?
Mynda Treacy
Hi Karin,
Filter the ‘Blanks’ out of your PivotTable and then set the Slicer settings to ‘hide items with no data’.
Mynda
Randy Tucker
Hi Mynda,
Your tutorials have been tremendously helpful and well put together. I found your site by (happy) accident, and I’ve been able to use your insights in a variety of projects. Thanks for sharing your Excel knowledge and specifically for helping me understand the visualization capabilities that Excel has.
Thanks,
RT
Mynda Treacy
Thanks for taking the time to comment and your kind words, Randy! I’m delighted we’ve been able to help 🙂
RM
Hi Mynda,
Is there a way to have your custom slicer settings to be applied another file (travel across other workbooks)? It doesn’t seems like it.
Thanks,
RM
Mynda Treacy
Hi RM,
You could apply them to your default workbook so that every new file you create has access to them, but I’m not aware of any way to copy them to an existing workbook.
Mynda
Ellen Gerhard
I have been using a slicer in my dashboard for some time now, but have discovered that one of the buttons in the slicer is not formatted like the rest. It is white, where all of the other buttons are blue (as they should be). Any thoughts?
Catalin Bombea
Hi Ellen,
Is that slicer’s format a default format? Make sure you have data under that button, usually the buttons with no data are formatted differently.
Wilson Nisti
Very useful tutorial.
I am not being able to hide “blank” buttons from slices that show data from a table. It is possible to do it with pivot table slices. How can I hide the “blank” buttons on table slicers?
I use the MSOffice 365
Thanks.
Mynda Treacy
Hi Wilson,
I’m not aware of any way to hide blanks in Slicers for Tables, sorry.
Mynda
Xander Twombly
Hi Mynda,
Great tutorial, but I am unable to actually edit the font once at step 4. When the Format Slicer Element dialog comes up, both Font: and Size: have lots of entries, but they are all grayed out. This is the case regardless of which slicer element I attempt to modify (Whole Slicer, Header Row, etc).
I am also unable to modify the font or size in the Timeline control, if this gives any additional hints, while I can alter font & size for the PivotChart itself.
Using Excel 2016 on Mac.
Any thoughts on what is causing this, and if it can be modified?
Mynda Treacy
Hi Xander,
I don’t have a Mac I can test it on, but it sounds like a Mac limitation.
Mynda
Ted
Hi Mynda,
Thanks, this is great, however – there is always a “however”
With all of the “selected items” on the slicer, I make the font size “4” and most can be displayed. However, I see no way to reduce the size (width) of the slider bar on the right, which make the slicer look klutzy.
Can this be done?
Thanks,
Ted
USA, Detroit Michigan
Mynda Treacy
Hi Ted,
No, sorry you can’t change the size of the scroll bar.
Mynda
Baba
Explicit and useful presentation. Saved my skin!
Mynda Treacy
Glad I could help 🙂
John Covelli
Hi Mynda, this is another great presentation! I have a different question about slicers that I have attempted to find information about… is it possible to limit the names in a slicer to the top 100 (such as I have done in the pivot table itself)?
I have a pivot table with customer names, and I have sorted and filtered so the table only shows the top 100… I also have a pie chart with other data that is linked… I would like the slicer for the pivot table to operate the pie chart (which I know how to do) but I want the slicer to only show the names in the pivot table, all the customers… any thoughts?
John
Catalin Bombea
Hi John,
Many things can be done, but unfortunately there is no easy way to change the values displayed in slicers. Usually the way it works is good enough for most people, and it’s the normal way to go.
There can be only workarounds, like adding a rank column in your source data, and create another column with a formula to return customer name only if the rank is less than 100. You will this column as a slicer, but you will still end up with a blank entry in the list of customer names.
Jomili
I want to save the new style to use in other workbooks, but when I closed my workbook (saving changes), then opened another workbook with a slicer, my Compact style wasn’t available. How do I save it for any workbook?
Mynda Treacy
Hi Jomili,
The style is workbook specific. You can add it to a template, or your default Excel workbook.
Mynda
Peter Bartholomew
Hi Mynda
I use a colour fill as part of the Normal style to conceal/identify cells that do not form part of my spreadsheet model (grid lines are only made visible in the working ranges of the sheet). That immediately exposes the fact that the white format on tables and pivot tables is actually ‘No fill’ which causes extra work formatting those objects.
Much worse though, are slicers which in Office 2010 acquire ugly black backgrounds. In that instance I want the background to be ‘No fill’ to show the sheet colour but, last time I tried, I finished up by defining every aspect of a custom slicer from the bottom up. I did not enjoy the experience!
Any ideas?
Mynda Treacy
Hi Peter,
It looks like a bug in the customisation options for Slicers because you can set it to ‘No fill’, but it still has white fill (as you mentioned), even in Excel 2016/Office 365. However, you can format the fill the same colour as your background and this only has to be applied to the ‘Whole Slicer’ element.
Mynda
Peter Bartholomew
Thanks Mynda. That gives hope for when I eventually move from Excel 2010. At present the slicer background shows as black which is hideous. I then turn the Whole Slicer to the palest grey only to have every button, with or without data, selected or not, matching the slicer background perfectly!
jim
Yes, I spend so much time reformatting, rearranging, resizing and overlapping slicers to try to make them take up less space
If the header is to be displayed, I often give it a more meaningful title to help the less Excel-aware users (depending on slicer width, but one extreme example I have used is “Profit Centre(s) – select multiples by dragging, shift-click to extend or control-click to add/delete; click icon on right to reset”)
and as a footnote, I’ll always deselect “Show items deleted from data source” – can’t think why anyone would want this and, even more so, can’t fathom why showing them is the default
Similarly with retaining deleted items in pivots – why? (this has been a huge gotcha with grouping dates, which can’t be done if there ever was a non date and this is checked)
jim
Mynda Treacy
I hear you, Jim!