Force Excel Slicers to Single Select Using These Crafty Tricks

You can overlay a shape that covers the title and the multi select just leaving the X showing. Lock it all down and protect the sheet. A side benefit is you can use the shape to give the headers of your slicers a unique and consistent style and add text back in for the title of you need it.

Thank you for the great tips! 😊 i have a question though for pivot tables wherein a selected data only has a few data points compared to other items. For example, a new product only has sales from Sep-Dec and so when I select it, jan-Aug is no where in the pivot table. In that case, the indeces (formula) referenced to the pivot table for the past 3 mos (Oct-Dec) becomes blank already (since Jan-Aug is blank, the data from Sep-Dec moves up the table). And so the index formula returns zero/blank. Is there a way to make pivot tables display zero for months with blank/no sales in the raw date?

With a pivot chart, you can overlay a text box across the entire chart, linked to a cell with your error message. When there is only one item selected, the cell is blank, and the text box is invisible. But if they choose more than one item, a large obnoxious message obscures the whole chart.

Thanks. Perfectly.

Hey Mynda,I'm having an issue with my Dashboard. I'm wanting to link a date slicer with multiple queries. Any direction on how to work around this would be greatly appreciated. Thank you.

Hi, is there any way to increase the space between the slicer buttons?

Great lesson, all your lessons has helped me a lot.

Thank you for this. You really are amazing and always seem to produce videos that people need to know!

@0.24 was mentioned that PivotChart would also be covered but I don't think it was

PLZ INFORM HOW TO CHANGE EXCEL SETTING FOR GO TO LINKED CELL DESTINATION BY USING MOUSE SINGLE CLICK INSTEAD OF USING KEY BOARD SHORT CUT CTRL+[ .

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

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 🙂

no matter what i do i cant create a clone from the original PT. may be its because i have filters?

My slicer is sorting all the cost centres except one which it is placing right at the end. I think it’s because all of them start with L but the one that is not working starts with N. Pls help

Is there a way that slicers can work if you have a pivot table and the data is written horizontally but you want users to see them vertically? I am not sure if you got video tutorial of this already. Thanks in advance! ❤️😉

Excellent video

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.

Very nice idea Mynda! You find innovative ways to workaround practical challenges and present them for viewers to easily understand. Thank you! 😊👍

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.

Dear Madam, how to make silicer table equal with combo box Or Data validation

I knew some of these tricks but all of them. Impressive!

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.

Good tricks. Never thought about such possibility

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

Hi is it possible to do something similar for a table that just has months and a percentage for each month. Looking to only display one month at a time as a donut chart. Thanks

Hi , tnx for nice tips 👏👏

How do u manage to get time to reply to everyone...is there a MYNDA BOT...lol

Спасибо за вашу работу! Стараюсь всегда вас смотреть.

Brilliant 👍

Thank you for all the great videos. Would love to see videos on scripting macro replacements that work across excel online and excel desktop.

Nice tips Mynda - have a great weekend

Thanks Mynda. This is very Helpful to me. Thanks Again. :) :)

Great tips 👌🏻

Great tip! Thank you for sharing!

Mynda, this is a really cool idea that transparent chart area. 👍

Fantastic tricks and tips as always. Thanks immeasurably.

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.

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.

Hi Mynda, excellent video! I eagerly await your videos every week. I love all of your tips and tricks. Your knowledge of Excel is truly phenomenal :)

Hi Mynda!Wow Really Great Tips...Thank You :)

Thanks Mynda. Those are helpful tricks.

Excellent trick Mynda. Thank you so much for sharing.

Excellent examples. Very useful. Thank you Mynda.

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.

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!

Cool idea. You could hide the line by replacing values with =na() on multi select. Also use text join on th multiselect dynamic list to show a concated list of what has been selected

Hey thank you so much I was looking for this trick from such a long time..it is indeed help...👍

Excelentes tips para titulos dinámicos!