Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Pivottable Slicers > Configuring Slicers

Configuring your Slicers

Slicer options on the ribbon

There is a host of settings you can apply to your slicers. The Ribbon shows most of them:

Excel 2010 ribbon for slicer editing
Ribbon (cut in two halves so it fits on this page) showing the slicer options tab.

The Slicer Tools contextual ribbon tab houses a number of groups to change your slicer's appearance and behavior. Let me discuss them here.

Slicer group

This group houses three important controls. You can change the name of a slicer here (Slicer Caption), change some settings controlling which items are listed in a slicer and how they are sorted and filtered (Slicer Settings). Also, you can set up which Pivottables connect to your slicer (I explain that below).

Slicer Styles Group

A quick way to format your slicers is by applying one of the built-in slicer styles. Like any style in Excel 2010, these adhere to the theme you've selected. This makes it easy to keep your workbook design tidy and consistent.

Like cell styles you can create your own slicer styles too, by right-clicking one of them and choosing "Duplicate", followed by right-clicking the newly duplicated style and choosing "Modify".

Buttons group

Just like you can easily change the colors of your slicers by applying a different style to the slicer it is simple to modify the button layout and appearance, just adjust the numbers in that group of the ribbon.

Buttons group
These three controls enable you to change the button layout and size

Size group

And last but not least a small group which lets you edit the button sizes.

Commands not in ribbon

Unfortunately, not everything you need is on the ribbon. The missing options are quickly accessible through the slicer's right-click menu however. Select the "Size and Properties" option to get there. Here you can control whether or not the slicer must be printed and should move/size with cells just like any other shape on a sheet. And you can turn off resizing and moving to prevent your users from messing with your layout.

Changing a Slicer's font size

If you've scurried through all slicer dialogs like I have, you'll have noticed there is no way to change the font size of a slicer.

Or is there? Of course there is, but is has been cunningly hidden!

The basic idea is that you must edit the slicer's style. But since you can only edit custom styles, the first step is to add a custom style. Pick one you like best and right-click it, then select Duplicate...:

Right-click a slicer style
Right-click a slicer style and select "Duplicate"

The second step is editing the new custom style. Right-click that style and select "Modify...":

Right-click a slicer style
Right-click a slicer style and select "Modify".

In the dialog that pops up, select "Whole Slicer" end then click the Format button:

The Modify slicer Quick style dialog
The Modify slicer Quick style dialog.

As you can see in the screenshot below, now you can edit the slicer's Font.

The Format Slicer Element dialog
Time to select a nicer font for your slicer!.

After you OK this dialog, probably nothing appears to happen to your slicer. Don't worry, all that is needed now is applying your new custom style to the slicer and you're done!

Controlling which pivots are handled by your slicers.

A very useful option of slicers is that you can tie them to more than one Pivottable and/or -chart.

The way to handle this is by selecting the slicer in question and then clicking the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab:

the Pivottable Connections button
the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab.

This brings up the following tiny dialog:

the Pivottable Connections dialog
the Pivottable Connections dialog.

As you can see, Excel has listed a couple of Pivottables in your file to tie the slicer to.

Luckily, Excel is smart enough to ensure that only Pivottables using the same cache as the selected slicer is tied to are in the list.

If you have more than one slicer on a particular field (e.g. a slicer on "City" on each sheet with a Pivottable), then you must take care when selecting which tables the slicer relates to. As soon as you check more than one check box, the slicers working on the checked Pivottables will be permanently tied together. The only way to make the slicers work independently again is by using the Undo button.

Next: Slicers and VBA



Showing last 8 comments of 223 in total (Show All Comments):


Comment by: M.Matawie (14-11-2017 05:15:08)

So used to Excel 2003, I got frustrated when using Excel 2013.
Real consolation will be the slicer. Fabulous! Made my filtering so much easier!!!


Comment by: Rita Lima (5-12-2017 02:54:07)

Hello Luke,

Is it possible configure a background color of a Slicer item (Button) based on its value?

For example, I would like show my slicer like:
0 - 30 (button green)
31 - 60 (button yellow)
60+ (button red)

Thank you,



Comment by: Jan Karel Pieterse (5-12-2017 15:57:08)

Hi Rita,

I'm afraid that cannot be done.


Comment by: f m (7-2-2019 11:01:21)

Hi there, when I try to edit the font size of my slider using your technique above of duplicating and then pressing modify, the modify button doesn't take me anywhere? It just remains on the same page?

My spreadsheet default font is rather large so the slicers are fairly useless if I can't edit them! Can't see anywhere online where this has happened to anyone else?

Using a Mac Pro - and when I press the modify button it just highlights blue and nothing happens.


Comment by: Jan Karel Pieterse (7-2-2019 12:47:09)

HI Frankie,

I'm sorry, I d not own a Mac so I have no idea how this is supposed to work on that type of machine!


Comment by: Amin (10-6-2020 13:20:00)

Thank you!


Comment by: Jessica (24-8-2020 01:45:00)

Is it possible to group slicer values into buckets/bins, without creating additional columns in the source data? I have a slicer with values or 0-100. Instead of having 101 numbers in the slicer, I would like to be able to select 0-9, 10-19, 20-29, etc.
Any ideas are appreciated!


Comment by: Jan Karel Pieterse (24-8-2020 10:22:00)

Hi Jessica,

If you group the field the slicer is attached to, the slicer will become grouped as well.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.