• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Slicer Formatting

You are here: Home / Excel PivotTables / Excel Slicer Formatting
Excel Slicer Formatting
June 25, 2018 by Mynda Treacy

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):

slicer contextual tool tab


I’m going to show you how we can uncover the font settings and make them much smaller.

Watch the Excel Slicer Formatting video

Subscribe YouTube

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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:

Excel Slicer Formatting


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’:

select 'Whole Slicer'


Step 4: In the Font tab alter the font size as desired. This will allow you to make the button height smaller.

alter font size


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:

set border to None


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:

set as default slicer style for this document


Your Slicer style will be the first in the gallery:

slicer style


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:

excel Slicer formatting options


In the Slicer settings dialog box deselect 'Display Header':

excel Slicer formatting header


Learn Excel Slicers

Are you new to Slicers? Master them with our step by step Excel Slicers tutorial, including video and workbook.

Excel Slicer Formatting

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.

Excel PivotTable Percentage Change

Excel PivotTable Percentage Change calculation is dead easy with Show Values As. Add conditional formatting, and Slicers for interactivity.


Category: Excel PivotTables
Previous Post:Power Query Consolidate Excel SheetsPower Query Consolidate Excel Sheets
Next Post:Calling VBA in an Add-In From Other VBA Modulescalling vba in an add-in from another vba module

Reader Interactions

Comments

  1. Karin D.

    August 5, 2020 at 11:57 pm

    How do I hide ‘(blank)’ from displaying on a slicer?

    Reply
    • Mynda Treacy

      August 6, 2020 at 8:46 am

      Hi Karin,

      Filter the ‘Blanks’ out of your PivotTable and then set the Slicer settings to ‘hide items with no data’.

      Mynda

      Reply
  2. Randy Tucker

    April 9, 2020 at 11:36 pm

    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

    Reply
    • Mynda Treacy

      April 10, 2020 at 9:57 am

      Thanks for taking the time to comment and your kind words, Randy! I’m delighted we’ve been able to help 🙂

      Reply
  3. RM

    August 8, 2019 at 3:57 am

    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

    Reply
    • Mynda Treacy

      August 8, 2019 at 11:55 am

      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

      Reply
  4. Ellen Gerhard

    July 4, 2019 at 5:35 am

    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?

    Reply
    • Catalin Bombea

      July 5, 2019 at 1:22 am

      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.

      Reply
  5. Wilson Nisti

    April 19, 2019 at 12:36 am

    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.

    Reply
    • Mynda Treacy

      April 21, 2019 at 2:28 pm

      Hi Wilson,

      I’m not aware of any way to hide blanks in Slicers for Tables, sorry.

      Mynda

      Reply
  6. Xander Twombly

    February 22, 2019 at 9:21 am

    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?

    Reply
    • Mynda Treacy

      February 22, 2019 at 10:22 am

      Hi Xander,

      I don’t have a Mac I can test it on, but it sounds like a Mac limitation.

      Mynda

      Reply
  7. Ted

    December 14, 2018 at 5:57 am

    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

    Reply
    • Mynda Treacy

      December 14, 2018 at 7:13 am

      Hi Ted,

      No, sorry you can’t change the size of the scroll bar.

      Mynda

      Reply
  8. Baba

    July 6, 2018 at 2:54 pm

    Explicit and useful presentation. Saved my skin!

    Reply
    • Mynda Treacy

      July 7, 2018 at 6:26 am

      Glad I could help 🙂

      Reply
  9. John Covelli

    June 27, 2018 at 12:00 am

    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

    Reply
    • Catalin Bombea

      June 28, 2018 at 7:58 pm

      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.

      Reply
  10. Jomili

    June 26, 2018 at 11:04 pm

    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?

    Reply
    • Mynda Treacy

      June 28, 2018 at 7:32 am

      Hi Jomili,

      The style is workbook specific. You can add it to a template, or your default Excel workbook.

      Mynda

      Reply
  11. Peter Bartholomew

    June 26, 2018 at 8:42 pm

    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?

    Reply
    • Mynda Treacy

      June 26, 2018 at 9:19 pm

      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

      Reply
      • Peter Bartholomew

        June 27, 2018 at 12:12 am

        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!

        Reply
  12. jim

    June 26, 2018 at 8:26 pm

    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

    Reply
    • Mynda Treacy

      June 26, 2018 at 9:03 pm

      I hear you, Jim!

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.