Forum Discussion

Moose1075's avatar
Moose1075
Copper Contributor
Jan 23, 2022
Solved

Hierarchy slicers in Excel

Hello,   Is there a way to create a hierarchy into a slicer in excel? I know that PBI can do it...   If no is there any clever work arounds? I have multiple 4 tier hierarchies that I would like t...
excel
  • Martin_Weiss's avatar
    Martin_Weiss
    Jan 24, 2022

    Hi Moose1075 

     

    if you speak about hierarchy, you mean that you use several different slicers. And after selecting a value in one slicer, that you get only relevant values in the other slicers, right?

     

    This is already the default behaviour in Excel.

     

    If I select in my example "Internet" in the first slider, the other two sliders give only the options that are valid for this selection, the others are greyed-out:

    You can go even further: Do do a right-click on the slicer and select "Slicer settings". Here you can say to even hide items with no values:

     

     

Moose1075's avatar
Moose1075
Copper Contributor
Jan 25, 2022

Martin_Weiss 

Hey thanks for that.

Yes I was aware of that behaviour, my issue is though that as I move down the hierarchy to level 3 or 4 (or slicer 3 or 4) I will have hundreds of options and its not practical to scroll through them to see which are not "greyed out"

Martin_Weiss's avatar
Martin_Weiss
Bronze Contributor
Jan 25, 2022

Hi Moose1075 

 

ok, understood. So the only advise I can give is to activate this slicer setting:

 

At least, the usually greyed-out ones are then completely hidden.

I'm sorry to have no better advice for you.

 

Resources