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.
_________________
@LB, Thanks! Glad you were able to help Alex out with his code.
@Alex, you’re welcome!
[…] Select Multiple Items from Excel Data Validation List – instead of selecting just one item from a data validation drop down, you can select two or more. […]
Hello Debra, I know the Same Cell example was simply an example, but I liked it alot and I added it to a worksheet as you know. I get a little problem that maybe you have made a fix for it.
The code works great, but when I click on the drop-down list and for some reason click another cell in accident, it duplicates what ever is in the cell at the moment.
Example: cell A1 currently has
apples, pears, oranges in the cell
for some reason, when I activate the cell A1 with the text, and then drag my mouse away and click some other cell, it generates duplicate data of whats inside cell A1. So I end up with: apples, pears, oranges, apples, pears, oranges.
I un-checked ‘ignore blanks’ but it still happens.
Any suggestions?
Thank you,
Alex
Debra, Thank you for all your help! I got it to work but I have to click on the adjacent cell, then back to the cell in which I want to the data to appear for it to work. Is this correct? I am hoping to simply select from the drop down list for the item to appear without the additional step of selecting the adjacent cell and then click back. Am I doing something wrong? Thank you in advance for your assistance and Happy New Year! Best regards, Andrew
Debra,
Thanks so much for sharing this. It’s exactly what I need to assist (and impress) my colleagues. Outstanding programming. Keep up the great work.
M
Hi Deborah and Contextures Team,
Your tutorial and helpful details on the installation of the code ran very well for the ‘same cell’ multiple items data validation. I am so very greatful that you have taken the time to show us simple folk how to use excel in wonderful ways. I needed to use this programming code for my research project. Your help is greatly appreciated! Thank you.
Viktor.