Force Excel Slicers to Single Select Using These Crafty Tricks - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Rico
Excellent video. I use Power Pivot quite a lot and hate having to deal with this issue. Obviously, it's simple enough to amend a measure to return blank based on HASONEFILTER(), but when you have many slicers, it becomes annoying. It always surprises me how/why the Excel team created such an obvious and great feature in the slicer, but didn't think through the very basic use cases of a slicer. It's effectively a fancy looking list box, and when you go into the properties window of a list box in VBA, you have the very simple property -multi select-, which can be set to single or multiple (or extended). When creating the slicer, it's like they didn't do a business case for it!
Don't get me started on the Timeline filter either! That thing is awful.
reply
Excellent video. I use Power Pivot quite a lot and hate having to deal with this issue. Obviously, it's simple enough to amend a measure to return blank based on HASONEFILTER(), but when you have many slicers, it becomes annoying. It always surprises me how/why the Excel team created such an obvious and great feature in the slicer, but didn't think through the very basic use cases of a slicer. It's effectively a fancy looking list box, and when you go into the properties window of a list box in VBA, you have the very simple property -multi select-, which can be set to single or multiple (or extended). When creating the slicer, it's like they didn't do a business case for it!
Don't get me started on the Timeline filter either! That thing is awful.
reply
Historian
I am having a hard time with slicers. When I create a slicer it shows me all the unique options for that column in the table, which is normally fine, but I want it to restrict the options according to a filter. For example, I have a Slicer that lists all the customer names, and I have the Pivot Table filtered by a title -Customer A,- but the slicer is showing every name regardless of the title. I only want to see buttons for names that have the filtered title. If anyone can help I would greatly appreciate it! Thank you.
reply
I am having a hard time with slicers. When I create a slicer it shows me all the unique options for that column in the table, which is normally fine, but I want it to restrict the options according to a filter. For example, I have a Slicer that lists all the customer names, and I have the Pivot Table filtered by a title -Customer A,- but the slicer is showing every name regardless of the title. I only want to see buttons for names that have the filtered title. If anyone can help I would greatly appreciate it! Thank you.
reply
Steve
Hi Mynda, thank you for sharing these very nice tricks! -
I found another very simple trick to force users to single select:
- The slicer is a shape - put another shape in the color of the header over the multi select item and the filter delete item
- Protect the sheet, allow autofilter and ready - the user can't click the multi-select or filter deselect items!
What do you think about this solution?
Have a nice day, Steve from Germany -
reply
Hi Mynda, thank you for sharing these very nice tricks! -
I found another very simple trick to force users to single select:
- The slicer is a shape - put another shape in the color of the header over the multi select item and the filter delete item
- Protect the sheet, allow autofilter and ready - the user can't click the multi-select or filter deselect items!
What do you think about this solution?
Have a nice day, Steve from Germany -
reply
360
This is fantastic! Thanks for sharing! One thing, at the end when showing the other way to display a message in red, you say you can't hide the line, but you showed us exactly how to do that earlier in the video by multiplying the sum of sales value in the dummy pivot table by the Isblank() function (Boolean value or 0 or 1). I tried that and it worked great. Thanks so much for doing this!
reply
This is fantastic! Thanks for sharing! One thing, at the end when showing the other way to display a message in red, you say you can't hide the line, but you showed us exactly how to do that earlier in the video by multiplying the sum of sales value in the dummy pivot table by the Isblank() function (Boolean value or 0 or 1). I tried that and it worked great. Thanks so much for doing this!
reply
Donte
Hello Mynda, awesome video! I have a question, is there a way to show values only when a slicer is selected? For example, when building a dashboard, sometimes it's not ideal for adding data value to a stacked bar chart with a large data set. It will get messy and hard to read. But when I select a slicer, the chart becomes more focused, and chart value appears. Thank you.
reply
Hello Mynda, awesome video! I have a question, is there a way to show values only when a slicer is selected? For example, when building a dashboard, sometimes it's not ideal for adding data value to a stacked bar chart with a large data set. It will get messy and hard to read. But when I select a slicer, the chart becomes more focused, and chart value appears. Thank you.
reply
Jonathan
Idea: with only 1 category selected in the slicer, copy J50:J51 and paste into J52.(i.e. a second copy of the pivot table below the first). Since two pivot tables can not overlap Excel will generate an error if more than one category is selected and block anything other than one selection.
reply
Idea: with only 1 category selected in the slicer, copy J50:J51 and paste into J52.(i.e. a second copy of the pivot table below the first). Since two pivot tables can not overlap Excel will generate an error if more than one category is selected and block anything other than one selection.
reply
Andrian
Hi Mynda, thanks for your video. It's really helpful. Just want to ask something, can I set my slicer to select more than 1 item in excel 2013? The only way i found is using CTRL + left click. Maybe I'm missing somethin. Thank you in advance
reply
Hi Mynda, thanks for your video. It's really helpful. Just want to ask something, can I set my slicer to select more than 1 item in excel 2013? The only way i found is using CTRL + left click. Maybe I'm missing somethin. Thank you in advance
reply
Mike
When I try to insert any slices on any tables, a box comes up asking for -Existing Connections- asking to select a connection or this workbook's Data Model and there is nothing to select to proceed. Your thoughts please.
reply
When I try to insert any slices on any tables, a box comes up asking for -Existing Connections- asking to select a connection or this workbook's Data Model and there is nothing to select to proceed. Your thoughts please.
reply
Calvin
I love watching your videos as there are always snippets of productive techniques demonstrated that boosts our creativity. Great way to get the best out of pivot features and and regular graphs.
reply
I love watching your videos as there are always snippets of productive techniques demonstrated that boosts our creativity. Great way to get the best out of pivot features and and regular graphs.
reply
Avenew
hi, it s interesting to see but for me it makes only the sheet more complicate to read, I m not sure that it brings more info but if you open the sheet you have to understand and you loose time
reply
hi, it s interesting to see but for me it makes only the sheet more complicate to read, I m not sure that it brings more info but if you open the sheet you have to understand and you loose time
reply
Add a review, comment
Other channel videos
