multi-select drop-down list

[BBarry]

I would like to know if Excel supports the ability for a drop-down list to be multi-select.  So for example, I would like a load a list of States into a drop-down list and allow the user to select multiple States, which will then display in the cell.

Next, I would like the auto-filter capability to allow me to select a single State and then show the rows that have that State as one of the selected choices in the cell.

If not via a drop-down list, is there another way Excel supports this capability?

Thanks!

[#2]

Hi @BBary

Best to my knowledge Dropdown list the solution we have at the moment to handle this scenario.. I know making name ranges it’s quite length job..

However if you have Microsoft365 (Office365) the you can trying doing the same stuff with XLOOKUP & Excel Tables which much easies with less efforts

Check out the blog might be helpful https://excelexciting.com/how-to-make-dependent-dropdown-list-with-xlookup/


Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

[#3]

@Faraz Shaikh thanks for the suggestion.  XLOOKUP doesn't help me accomplish what I was looking for, but I did learn something new with the suggestion and can see using that capability in the future.  Thanks.

[#4]

Try Data — table and Conditional formatting , maybe help you.

[#5]

@BBarry 

To a certain extent your request almost goes against the purpose of the drop-down lists (that purpose also going by the name "Data Validation")...

So let me ask you a question to see if there might be another way to accomplish whatever it is that you ultimately want to do. The question: is there a maximum number of states you would anticipate a person selecting for any one instance? If so--let's assume that there's a max of 10--would it be possible to have ten columns, in each one of which you can select the name of a single state, but the list gets shorter as each one is used in a prior selection?

So I guess what I'm really asking is: what's the larger context here? What's the larger purpose that is being served by selecting multiple states?:

There is almost always a variety of ways within Excel to accomplish a given purpose; I just want to make sure we're not limiting ourselves at the start by assuming we have to go in one direction.

[#6]

@BBarry 

I have seen a pretty close functionality where you can select multiple options from a Drop List and every option is added next to the previous selection with a comma separating  them. If this is what you want then you can use any of the static techniques (Text To Column) or Dynamic ones (Dynamic array functions) or Split by delimiter in Power Query to separate the result into multiple columns.

However, I personally think that by selecting multiple options, you are defeating the purpose of Data Validation. Further more, sorting and filtering may yield incorrect results.

If you want to read the Blog I mentioned, it is written by fellow Excel MVP Mynda Treacy, and here is the link:
https://www.myonlinetraininghub.com/select-multiple-items-from-drop-down-data-validation-list

Hope that helps

Nabil Mourad

[#7]

@mathetes 

Essentially I am trying to use Excel as a "vendor database" as opposed to building something in Access or using another vendor tool, such as Salesforce.  We are tracking a list of vendors and their solution and want to know what States they have deployed that solution in.  So Vendor A, for example, may be active in 5 different States.

The reason for attempting to capture all this information in one column is so we can filter on that column by State to see a list of vendors within a given State.  Creating separate columns to capture the States defeats that purpose.  I can repeat the vendor row for each State they are in and filter that way (what we are doing currently), but was curious if there was a different way to reduce the number of rows and repetition of data.

[#8]

@BBarry 

So here's another solution, one which will preserve the "Data Validation" purposes of the drop-down boxes.

I'm presuming that each row of data on Vendors has other info--name of a contact, corp address, whatever, so you're very legitimately wanting to avoid being redundant with all that data.

This is a simple "relational database" where the key to each of two tables is the vendor ID. One of the tables contains all that vendor specific data like contact name, corp HQ, whatever; the other is just a row for each state the vendor operates in.

And then, using the new FILTER function [for which you do need the most recent version of Excel] it's easy to create a list of the vendors and all the information on them that you want, who operate in, say, NY... and then VLOOKUP on the Vendor DB gets whatever other info you want in your new "filtered list"

To change the list in the attached file, just enter a state abbreviation in the pale yellow cell. All the rest will be done for you. And you can add other vendors and other states to your heart's content; because they're formal Excel Tables, the formulas referring to them will accommodate your additions.

But you do need the newest release of Excel.

[#9]

I have a similar need where we are asking users to choose from a drop down how data is entered into the applications they use. We are planning on taking the data into Power BI where we will transform the multiple values, just wanting the multi-select for the Excel sheet that will be the source for Power BI.