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.
_________________
Hi Debra,
I apologise now if I’m duplicating this query, but I have a quick question about multiple selection with a comma separated and pivot tables? I used your coding to allow users to choose the tools they used to carry out research, which also allowed them to make multiple choices that would separate the items with a comma, and it works a treat.
The only problem is, when I try to add this data to a pivot table, it treats all items in the list as different variables. Although there are 6 items in the list, when it comes to counting how many times they used those tools, it won’t count how many times those items appeared in that column, but will consider each combination of multiple selections as one item.
Is there a way of over riding this so that it just lists the number of times each of those tools were selected?
Thanks.
Hi Sarah, unfortunately a pivot table won’t be able distinguish the unique items within a single cell.
Instead, you could use a COUNTIF formula to count the occurrences of each item in your data validation list.
I saw that two other people posted about using countif on the data validation multiple selection cells. I have tried using a countif, but that doesn’t seem to distinguish unique items within a single cell either. Is there a way to count occurrences of a within a cell? I have a spreadsheet in which a person can assign multiple people to a project, and there are line breaks when each new person is chosen from the list. My supervisor wants to be able to count how many times a person’s name is used in the spreadsheet. How to do this while keeping the multiple selections within one cell?
Hi Debra;
love these options, especially the ones that you can multi select and remove; hence for some reason it doesn’t work sometimes; i tried opening your excel form, and it doesn’t work, and reopen mine again, and it didn’t work – i don’t know what might be issue – because i have to submit it to my boss, and i don’t want her to see it’s not working…
Hi Debra,
Fantastic page; it’s exactly what I was after and easy to follow.
I am using the code from the ‘Separate Rows’ sheet on the downloadable file, and I was wandering how I might have the selections appear a few rows bellow the drop down list as opposed to next to it?
Hi Debra,
This is wonderful work, to select multiple items from a drop down list. I had no issue with creating it and using.
However, this code has created a change in the date format in the worksheet. I want to use the UK date format (dd/mm/yyyy), but when I apply in the background it works only in the US date format (mm/dd/yyyy). In the view it shows correctly (example June 9, 2013 will be appeared as 9/6/2013 but when convert to date serial it gives for September 6 2013. This issue is only for the 1-12 days.
How can we avoid date change.
Nalin
Code is absolutely brilliant. One question – is it at all possible to separate your choices with an ‘&’ or the word ‘and’ instead of a comma? The spreadsheet I’m working on will only have one or two choices from the list, so an ‘and’ would work perfectly.
Hello! I have used the code and had success, however, when I open my workbook after saving and closing it, the code no longer works. How can I prevent this?
Thank you in advance!