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

Pivottable Slicers

Introduction

Pivottables are one of the most powerful data analysis features Excel has to offer. From version to version, Microsoft have added functionality to this feature. The number of additions and their usefulness differ from version to version of course. Amongst the additions and updates for Excel 2010 are Pivottable Slicers. In this article I'll discuss how you can put slicers to work.

Content

Other sources

Microsoft Excel - Easy (and Even Fun!) Data Exploration: Introducing Excel 2010 Slicers

Microsoft Excel - Interacting with Slicers


Comments

All comments about this page:


Comment by: Melroy Beeks (11/6/2011 3:21:11 PM)

In my Micro

soft does not have a SLICER button

 


Comment by: Jan Karel Pieterse (11/8/2011 12:53:15 AM)

Hi Melroy,

You need to have Excel 2010 to be able to use Slicers in Pivot tables.

 


Comment by: Irvin Jaffe (3/7/2012 12:32:47 PM)

RE: Excel 2010. I have a slicer with 6 choices. I want to show only 4 of the 6 (2 of the 6. And, of the 4 that I want visible, I want to change the text. How do I do this?

 


Comment by: Jan Karel Pieterse (3/8/2012 2:39:30 AM)

Hi Irvin,

I think you can only achieve that by removing data from the source so that those items are not on the slicer. Also, you cannot change what is shown on the slicer, other than by changing the source data.

 


Comment by: ana (4/3/2013 1:03:44 PM)

How do I change the order of the buttons in a slicer? I have A, B, C, and D buttons and i want to arrange them like C,B,D, A.

 


Comment by: Jan Karel Pieterse (4/3/2013 1:19:22 PM)

Hi Ana,

I don't think you can change the order. I tried with an existing pivot table, but failed.

In the properties of the slicer you can set the sort order to be the same as in the data source. Perhaps that helps?

 


Comment by: Wayne (6/17/2013 7:36:25 PM)

I have a pivot table/chart that I have connected to 4 slicers. The slicers are for different levels of data on the chart: District, manager, lead-office, and office. When a district (manager, etc) is selected in a slicer, I want the other slicers to grey out members that do not belong in the selected grouping. I have one column label field in pivot that includes some calculated items. All of this works until one of the calculated items is selected in pivot, then all the slicers lose their ability to grey out non-selected groupings. When calulated items are deselected, this ability returns.

Column label field selections are done through combo boxes and VB code. This is limit choices to desired items.
Slicer fileds are all in the report filter section of pivot.
Data is MS query cannection to an Access database.
Calulated items are ratios.
There is one row label field.
Data query is very large(over 1/2 million rows).

I need slicers to keep filter ability even with calculaed items. Is there a property I can set to keep filters working on calculated items? Or another way to get the desired filtering to work? Any ideas?

 


Comment by: Jan Karel Pieterse (6/17/2013 7:55:45 PM)

Hi Wayne,

I would try to move the calculation form the pivot table to the query.

 


Comment by: Wayne (6/17/2013 8:23:47 PM)

Calculations are ratios and can't be calculated until after level is selected.

 


Comment by: Jan Karel Pieterse (6/18/2013 10:32:17 AM)

Hi Wayne,

In that case I guess you are limited to what Excel delivers, which isn't very useful in this case!

 


Comment by: Rachit (10/23/2013 12:56:46 AM)

Hello there,

Excel is not letting me refresh a pivot if there is a slicer is attached to it...is there a way to work around this?

Regards,
R

 


Comment by: Jan Karel Pieterse (10/23/2013 3:53:16 PM)

Hi Rachit,

Something else must be wrong here, because a slicer should not prevent a PT from refreshing at all.

 


Comment by: Kin (8/19/2014 11:11:37 PM)

Just uncheck the slicer and update the pivot. Common Slicer connect to multi pivot table will encounter this error

 


Comment by: Isaac (11/5/2014 9:44:56 PM)

I'm using Excel 2010 and I've created a dashboard using a pivot table and slicers. Is it possible to create a slicer that has like drop box characteristics that would enable to have 1 slicer but be able to see all of the data in that one header?

 


Comment by: Jan Karel Pieterse (11/10/2014 11:34:24 AM)

Hi Isaac,

I'm afraid you cannot change the way Slicers work.

 


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