Removing Slicer Option with Data

[i3 Data Hound]

Hey All,

I'm building a dashboard with Pivot Tables and slicers; however for a few of the slicers it is providing options that I do not want to be available. I see that it is very easy to hide/remove options where there is no data associated with it, however the selections that I would like to remove do in-fact have data associated with it. Is there any way to have a slicer only provide the options that I want?

[dflak]

Make an Excel table with a list of those values you want Excluded. For example suppose you want to exclude specific vendors from the data. Now in your source data for the pivot table add a helper column Called Exclude

Then enter in a formula to check if the vendor in the raw data is on the exclusion list. This formula would look something like

=ISNUMBER(MATCH([@Vendor ID],Table_Exclude_Vendor[Vendor ID],0))

This will evaluate to True if the vendor is on the list and False otherwise.

Now build a second Pivot table with this column as either the filter or row and attach a slicer to it. Then link that slicer to your main pivot table to and set it to False.

Now the main pivot table will have these vendors pre-filtered out. They will not appear in the main table and if you set the slicers on the main table the right way, they won't even show up there.

If you want to see this in action, include a sample workbook.