Forum Discussion
Removing a possible user selection from a slicer
I have a column field in Excel with 4 possible values. My slicer shows all 4, as it should.
Three of the possible vales are alphabetic strings.
One of the possible values is a blank field indicated by (blank) on the slicer.
A blank is a valid value for this field in some situations, so I don't want to just ignore the rows where this field is a blank.
But I would like to eliminate it as a user selection in the slicer itself.
I thought checking the slicer setting, "Hide items with no data" would do the trick, but it doesn't appear to do so.
Any tips on how that setting is supposed to work and/or why it might not be? (Maybe it has to actually be a 'null'?? No idea....)
Hello Dan_TWE,
The slicer option “Hide items with no data” only hides values that are truly unused in the current filtered dataset. A blank cell in your source is still treated as a valid value, so Excel shows (blank) in the slicer even when that option is checked. To remove (blank) from the slicer without removing those rows from your data, you need to use a helper column (for example =IF(ISBLANK([@Field]),"Hidden",[@Field])) and use that helper field in your slicer instead, then filter out “Hidden”.
This behavior is consistent with how slicers work in Excel; slicers show all distinct values from the source field and only hide values that are not present in the filtered data context, not blanks by default.
See Microsoft documentation on using slicers to filter data:Use slicers to filter data
2 Replies
Hello Dan_TWE,
The slicer option “Hide items with no data” only hides values that are truly unused in the current filtered dataset. A blank cell in your source is still treated as a valid value, so Excel shows (blank) in the slicer even when that option is checked. To remove (blank) from the slicer without removing those rows from your data, you need to use a helper column (for example =IF(ISBLANK([@Field]),"Hidden",[@Field])) and use that helper field in your slicer instead, then filter out “Hidden”.
This behavior is consistent with how slicers work in Excel; slicers show all distinct values from the source field and only hide values that are not present in the filtered data context, not blanks by default.
See Microsoft documentation on using slicers to filter data:Use slicers to filter dataThanks. That info will help.
