1

Slicers are a fantastic tool new to Excel from 2010, allowing you to quickly switch your selections when analysing data using a pivot table.

Sometimes there are too many options to fit in the slicer window so you get a vertical scrollbar (ignore the numbers in this image):

slicer showing scrollbar

Often, I will have a pivot table with a static report filter selection - ie a filter that I do not want the user to change, eg Year = 2013:

pivot table report filter

This selection then means certain values in my slicer are irrelevant (using the example above, perhaps a salesperson called Smith left before 2013) so I don't want to show them in the slicer (but I do want to retain them in my pivot table data source).

What I'd like to do is suppress the vertical scrollbar in the slicer so that the irrelevant options are not presented to the user. Can this be done, either by a slicer setting or VBA?

At the moment my rather inelegant solution is to overlay a white rectangle... the downside is the 'clear filter' button is then awkwardly pushed out to the top right. If there's a better way to remove the scrollbar (or redundant option) I'd love to hear it!

Improve this question

2 Answers 2

Reset to default
1

It would appear that this cannot be done. Therefore the best solution would seem to be the one I'd already gone for (mentioned in the question):

my rather inelegant solution is to overlay a white rectangle... the downside is the 'clear filter' button is then awkwardly pushed out to the top right.

Thanks to Jan Karel Pieterse on LinkedIn for this confirmation.

Improve this answer
-1

Right click on slicer, go to Slicer Settings, Check "Hide items with no data"

Improve this answer
2
  • This doesn't answer the question - the items I need to hide do have data, and I want them to appear in the report, so this gets me nowhere.
    – Andi Mohr
    Commented Nov 24, 2017 at 12:51
  • Sorry, missed the part were you still had the data in the pivot table. Only option i see is to add a field to your data to mark the irrelevant (leaving date) options and then keep those filtered out. A picture of your slicer would be useful since i cannot understand what the irrelevant options are.And maybe you could use another slicer with a selected list of values that you determine to populate your actual slicer trough VBA
    – Arhire Stefan
    Commented Mar 13, 2018 at 12:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .