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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

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

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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


 


Comments

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

 


Comment by: Shane (6/12/2017 6:07:54 PM)

I have a table as follows:

Job Start Time Complete Time
Script A 00:00:00 00:10:00
Script B 00:05:00 00:06:00
Script C 00:03:00 00:14:00
Script D 02:20:00 03:50:00

I need to be able to slice and display records by time ranges.
For example: Show me all rows where Start Time >= 00:00:00 and Complete Time <= 00:12:00
Note: these time ranges are at the discretion of the user.
Any advice would be much appreciated.
Thanks!!

 


Comment by: Jan Karel Pieterse (6/13/2017 10:12:21 AM)

Hi Shane,

I'm afraid this can only be done using filtering, slicers are not up to a task like this (yet).

 


Comment by: Nelly (7/20/2017 9:35:49 AM)

Hi all,
Please advise if there is any option to customize the list of slicer buttons/item? For example, on the slicer I have 3 buttons (Public, Commercial and blank) and I want to see only Public and Commercial. Is there any option to delete/hide a button without changing the original source?

many thanks

 


Comment by: Jon (8/11/2017 7:59:41 PM)

Hi,

I create a pivot table on excel 2013 and I'm using the slicer option.
First I have 3000 records, I'm sorting by region and I have the following results:

Mexico - 5 results
India - 48
US - 129

When I click on the 5 under Mexico, instead of showing me the 5 records from Mexico it opens the 3000 records. What did I do wrong?
Please help.

Thank you

 


Comment by: Jan Karel Pieterse (8/13/2017 5:08:31 PM)

Hi Nelly,

You can't do that without modifying the data I'm afraid.

 


Comment by: Jan Karel Pieterse (8/13/2017 5:18:56 PM)

Hi Jon,

I'm afraid this is hard to trouble-shoot without the data or the file. You can contact me through the email address at the bottom of this page if you like?

 


Comment by: Hayden (9/11/2017 7:57:10 PM)

(Working in Excel 2013)

I have 2 connected Pivot Tables, 2 traditional slicers, and 1 timeline slicer. As it currently stands, the selection in the first slicer will effectively determine what options appear in the second slicer (ie. "Hide data with no value" works correctly). However, when I use the timeline slicer to choose a certain range of time, it does not remove options in the subsequent slicers that would now have no data matching the selected time frame. IS there a way to make the slicers recognize and respond to the timeline slicer's selection?

 


Comment by: Jan Karel Pieterse (9/12/2017 9:56:16 AM)

Hi Hayden,

Hmm, I wouldn't have expected that to happen indeed. IMO this is a bug.

 


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: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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