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.

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 205 in total (Show All Comments):

 


Comment by: Giedrius (12/12/2016 12:03:10 PM)

Hello,

I have a slicer for years (2016, 2017) but in the list of available selections I also get "<2016-01-01" and ">2017-12-31".
How do I get rid of them? I just need the years. Thank you.

 


Comment by: Jan Karel Pieterse (12/13/2016 6:59:54 AM)

HI Giedrius,

One way is by adding a calculated column to your sourcedata in which you calculate the Year.

 


Comment by: Whitney (12/15/2016 4:00:06 PM)

I have inserted a slicer, the resource the slicer is using has multi colored fonts (for categorization purposes). When you double click on the value to induce the slicer information it does not transfer the font color from the original resource. Is there a setting or a way that this can be done?

 


Comment by: Jan Karel Pieterse (12/15/2016 4:02:49 PM)

Hi Whitney,

I'm afraid the font color of the source data cannot be reflected on a slicer. Only the number formatting (like currency symbols) will come through.

 


Comment by: Peter (1/31/2017 2:16:12 PM)

Hi.
I can change the font in the slicers. But the "bottums" do not change size. Is there a way to change them as well. The reason why I change the fonts is to have the whole slicer to take less space when printed.

Regards

Peter

 


Comment by: Jan Karel Pieterse (1/31/2017 2:28:57 PM)

Hi Peter,

You can change the slicer directly from the ribbon and set the button height from there.

 


Comment by: Josh (2/23/2017 5:18:35 PM)

Hi,
I have a slicer that shows different account owners and their department spend. Is it possible to set a passcode similar to one in access, so that when each individual account owner receives this file they can only access their respectful department information?

 


Comment by: Jan Karel Pieterse (2/24/2017 3:05:51 PM)

Hi Josh,

In Excel the only way to do that is to send out the file with ONLY the information the user is allowed to see.

This does not apply if the data is tied to e.g. a SQL Server database with proper user rights and permissions set up.

 


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].