Removing Greyed-Out Slicer Options in Excel

How to tell the Slicer to forget

Sometimes Slicers seem to have a long memory and list entries that are no longer in the current data set. There is a setting to fix this.

In the image below I have a Pivot on the same page as its data source plus a Slicer for the State column.


If I change the two TAS entries in the rows 7 and 15 to SA and then refresh the Pivot, this is what happens.

So even though TAS is no longer in the data set, it is still shown in the Slicer. Excel remembers!

To force Excel to forget you need to change a Slicer setting.

Right click the Slicer.

Select the last option, Slicer Settings.

In the dialog that opens untick the bottom, right option as per the image below and click OK.

Then the greyed-out option should disappear, as below.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply to frank Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “Removing Greyed-Out Slicer Options in Excel

  1. Thank you for this post. Very useful.
    However, I need further assistance.
    In my case all the greyed options have data but I don’t want these options to be seen or clicked on as the data are not relevant. However, I do not want to delete those data from the table with the data source.
    Is there a way to hide these ?

    • Hello
      No easy way to do that.
      Maybe add another column and use a formula to display the codes you want, then for the codes you don’t want display a blank or N/A or something else.
      It will still display a blank or N/A button on the Slicer.
      Regards
      Neale