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!

Solution

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.

Comments

[Andi Mohr]

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.

[Arhire Stefan]

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