Any way to hide Slicer Values in Excel 2010

Question

I know that Excel 2010 Slicers will visually indicate when there is no data associated with a particular slicer selection. For example, they are faded in color for 2010 if you have no values in 2010.

Is there some setting where I can hide them altogether?

If I have sales in 2007, 2009, and 2010; but not 2008 can the slicer list omit 2008 entirely or must it always be shown as greyed out.

I'm not asking for a SQL selection solution, but rather an Excel setting.

The only real answer here is @Zogmeister 's answer, which actually works in excel 2010. — eli-k, Jun 28 '16 at 04:39

score 4 · Answer 1 · answered Dec 20 '13 at 00:37

4

Actually, yes you can! If the item on the slicer is grayed out because there is no data, you can do the following:

Right click anywhere on the slicer and select "Slicer Settings" from the drop down.

On the bottom right select the box that says "Hide Items with no Data".

Click "OK"

answered Dec 20 '13 at 00:37

Ginger

41
2

This option may not be available in 2010 but it helped answer my question and I was using 2013. As an alternative, you can also go into the pivot table options, in the data tab, and change the 'Retain items deleted from the data source' option to None and that does it for me as well, not sure if that's a 2013 thing only though. – ammills01 Sep 03 '14 at 14:54

score 3 · Answer 2 · answered Jul 23 '15 at 16:40

Found a way of mimicing this in 2010 - see this article on formatting Slicers

http://datapigtechnologies.com/blog/index.php/getting-fancy-with-your-excel-slicers/

Create a custom format, and just set the items with no data to white fill & White text, and they dissapear! You should also set the "hover over" for items with no data to the same, otherwise they will appear if the user hovers ofer the item. Simples. Thanks to Datapig!

score 2 · Answer 3 · answered Jun 20 '14 at 14:24

2

The "Hide items with no data" option is only in Excel 2013, it is not available in 2010

answered Jun 20 '14 at 14:24

Dan

45
5

score 2 · Answer 4 · answered Sep 07 '11 at 06:48

2

This feature does not exist. But an alternative does exist. Place the slicer anywhere in another sheet and hide that sheet and it will still work.

answered Sep 07 '11 at 06:48

Harpreet Saini

21
2

score 1 · Answer 5 · answered May 05 '11 at 18:11

1

You cannot do this in Excel; the feature doesn't exist. You may find that page filters will suit the task better.

answered May 05 '11 at 18:11

Chris Rae

5,627
2
36
51

1

I found this useful to hide items from slicer where value = 0. I simply filtered zero out in the relevant page filter. – QHarr Jan 10 '18 at 08:15

Any way to hide Slicer Values in Excel 2010

5 Answers5