Excel: How to hide/remove scrollbars from Pivot Table Slicers?

Question

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):

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:

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!

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

Answer 2

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