You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
I downloaded the example spreadsheet and when I opened it in Excel 2010, I received the big red “Protected view” warning: Office has detected a problem with this file. Editing it may harm your computer.
This makes me distrust the file. Well, its also possible that I just sent my e-mail address to all the spammers in the world by posting this reply.
@bbouvier, the file has macros in it, to allow the multiple selections, but nothing unsafe or unusual. The file has been downloaded over 100,000 times, and I haven’t heard from anyone regarding problems, so I’m not sure why your system flagged it.
I wrote an article about this technique on the Microsoft Excel team’s blog, and you can download a version of the file without macros there. Perhaps that will work better for you:
http://blogs.office.com/b/microsoft-excel/archive/2012/08/21/select-multiple-names-from-a-drop-down-list.aspx
Thanks for the pointer to the Microsoft Excel team’s blog. Everything worked as designed. Now, I need to figure out how to make multiple values work in conjunction with dependent validation lists. For example, if column C is “State”, and column D is “County”, the ‘=indirect’ function would make the County validation list display only the counties in the State. However, the =indirect function fails if you select two states (e.g. California and Nevada) since there is no range named ‘California, Nevada’
Debra
If data validation is used, can the cell still be used for free text?
Lori, you can change the Error message setting in the data validation dialog box. There are instructions on my website:
http://www.contextures.com/xlDataVal04.html#Error
This was super useful. Thank you so much!
Hi, I copied pasted the code, but when I tried to select another item. It showed me an error “Label not define” then highlight exitHandler in the sentence below
If Target.Count > 1 Then GoTo exitHandler
I don’t know what should I do
Hi Debra,
This was extremely useful!! Thank you!
How do I filter the column by individual results?
For instance if column A has the following cells:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
I want to use the auto-filter option so that if I filter to show only “orange”, I will get A2 and A3. If I filter to show only “grape”, I will get A2 and A4.
Right now the auto filter shows the following options:
“apple, orange, grape”
“orange, apple”
“grape”
I would really appreciate it if you can solve this problem. Thank you so much!