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.
_________________
Adam, the code was created in Excel 2003, so should work in that version. Did you enable macros when you opened the workbook?
Hi Debra,
Great work!! Can you add ‘All’ in the selection list and when selected it will add all the values from the list? I have a use for this in a chart.
Cheers,
Myrna
Myrna, thanks! I don’t have an example that lets you select All, but it’s an interesting idea. If I put something together I’ll post about it on the blog.
Dear Debra,
The code is working perfectly. However, Is it possible to avoid to select twice the same items from the dropdown list ?
Dear Debra,
I have been looking for this in the past few days and is realy work for ppl like me who has no any programming background. Thanks thanks thanks!
It work perfect for me and I am able to apply to more than one columns and not entire sheet.
Cheers,
Fiona
Junior, I don’t have an example that prevents selecting the same items twice, but maybe someday!
Fiona, You’re welcome! Thanks for letting me know that the example helped, and you got it working perfectly.