How to Set up Multiple Selection Excel Drop Down

[Latest update: July 27, 2016] With a bit of Excel VBA programming, you can change an Excel data validation drop down list, so it allows multiple selections. There is a sample file and instructions, in a blog post that I wrote a couple of years ago – Select Multiple Items from Excel Data Validation List.

There is a video in that post, that shows how the multiple selection options work, and a peek at the code that makes the multiple selections possible.

Setting up the Workbook

Even though the post is a couple of years old, it still gets comments and questions. This week, Rick asked for details on setting up a new workbook, and copying the sample code into the new file.

To help, I’ve recorded another video, showing the setup steps.

More Articles on Select Multiple Items

[Update]: Since posting this article on selecting multiple items from an Excel drop down list, I’ve added  more articles on the same topic. Please read these updates, for more details on working with the VBA code:

Changing the Macro Code

If you need to change the column numbers in the code, there are instructions here.

To run the code on a protected worksheet, see the notes here.

Download the Sample Data Validation File

To experiment with this technique, you can download the zipped sample file from the Contextures website, on the Excel Data Validation – Select Multiple Items page.

Buy the Premium Version

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. Instead of selecting from the drop down, a list box appears when you click the cell.

It automatically selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.

The premium version works with dependent lists too, and runs on a protected worksheet. There is also an option of showing a multi-select listbox, or a single-select version (this is helpful when working with dependent lists — you don’t want multiple items selected in the main columns).

The kit has 3 Excel files, and a user guide, with details and screen shots, on how to add this technique to your own workbooks. Click here for details: Data Validation Multi Select Premium.


You may also like...

38 Responses

  1. sven says:

    Thanks a lot for this solution. This video is simple, effective and clear. And it works very well. Thank you again, Sven

  2. Contextures Blog » Excel Drop Down Multiple Select or Remove says:

    […] to cellsset up multiple […]

  3. Bijal says:

    Thank you so much for this video. This was what I needed.

  4. Kristen says:

    How do I set up more than one column with this function inside the code?

  5. Tee says:

    thank you, thank you, thank you!!!!!!!!!

  6. HARPINDER says:


  7. Charles says:

    Very nice job. Good sample presentation.

  8. Bethany says:

    This was awesome!!! Thank you so very much for sharing and making it easy to understand!!!

  9. Tease Gould says:

    I would like to be able to set this up for more than 1 column as well.

  10. Leila says:

    Thanks you and I start using it, however if I made a mistake in choosen one and then I need to delete it .. I can’t.. I need delete all the line and restart choose from begining.. How can I delete one selection from the multiple selection I choosed without deleting all the line>>

  11. @Leila There is an example in the following blog post, for removing items that have already been selected:

    Excel Drop Down Multiple Select or Remove

  12. Sue says:

    Just add “Or Target.Column = ?” words next to the “If Target.colomn”


    If Target.Column = 9 Or Target.Column = 10 Then

  13. Mallory says:

    I basically have no idea how to use VBA programming and my company needs me to make a multiple selection drop down box for proposals. They want each item to be able to be selected, if needed, and placed in its own box in one column. I have seen on your website that this is possible, I just dont know how to change the codes to get them to work for me. Is there any way you can help me or point me to help?

    Thanks so much!

  14. Peter says:

    This is great. Is there a way to overcome the 8 items limitation without scrolling? In other words, show more than 8 initial items when the drop down is selected?


  15. Angela says:

    When i copy the code into my workbook and attempt to select something from the drop down list, I get the error “ambiguos name detected” Worksheet_Change. Anyone else run into this?

  16. Amy says:

    When I open your sample worksheet in Excel 2010, it does not function as it does in your video. Do you have a suggestion for how to change the code in order to make it work in the newer version? I love the idea of incorporating this function in my speadsheet and I thank you for making it so easy!

  17. James says:

    Hi – Thank you very much for the code. It was easy to follow. What happens if I need to have multiple columns within one worksheet to have the multi-select. Let’s say for example it’s columns 5 and 6. How would I change the code so that it works for both columns?

    Thanks again!

  18. Tom says:

    I’m also having issues getting this to run in Excel 2010. Do you have the changes required to get the “mutiple columns” code to work? If you could include the changes for avoiding duplicates too that would be great! adTHANKSvance

  19. Tina says:

    I need to do two things: (1) have dropdown boxes with mult-select in several different columns within my spreadsheet. I do how change the code to do this (they will be in columns B, D, F). (2) I want users to be able to select several items from the dropdown box. Currently, they have to click on the down-arrow each time they want to select an additional item. Is there a way to hold the CTRL key and select several items at one time? Thank you!

  20. Kyle says:

    I am trying to get this feature to work Excel 2002 SP3. Is there something that needs to be modified in the code or another setting to allow the feature to work on this old of a version? other than upgrading, do you have any suggestions?

  21. Antony Wootten says:

    This is fantastic, and exactly what I’m looking for. However, the DataValMultiSelect spreadsheet will not work on my computer. It obviously does work, just not for me! What am I doing wrong? Is there something I have to do to enable the function? When I click on any of the entries in the drop down lists in any of the tabs, nothing happens. I really want to get this to work, particularly the one which compiles the selection into a single cell with commas separations.

  22. Georgia Ramsey says:

    I am trying to set up multiple selection of cells on multiple cells within one sheet. I can not figure out to apple these settings to more than one cell on the same sheet?????

  23. Vishnu says:

    It is really good…but what I am looking is if with that above code how I will use for different Columns. If I need same filters or Data Validation up to 10 columns(1-10) what I have to do?

  24. Dianne says:

    Good Morning, the code does not work for me. Can someone help me??

  25. Kris says:

    I am getting the code to work when I type in the values in the Data Validation box, however, when I try to use values from a separate sheet within the same workbook it will not allow multiple values. Is there some code that I need to change to make this happen? Thank you in advance.

  26. Michael says:

    Thank you for this code. I am Adding Values to the Same Cell. It works with limitations. I am using a workbook with multiple spreadsheets. This workbbok will serve as a set of templates for a project which are distinguished by a tab within the worksheet. The sheets in the workbook are identical. One cell on each has my multiple-selection data validation criteria. When I make the selection on one sheet it is fine, but occasionally a bug arises when I try to clear the cells in a sheet to re-enter the data. Sometime the cells lock and other times the deleted data appears on other sheets in the workbook on the data validation field.

  27. Rachel says:

    Thank you so much for sharing this tutorial and your code! It is extremely clear and easy to follow. You saved me hours of headache! Thanks!!!

  28. Kate says:

    Hi, This has been brilliant. I have the same question as others. Just wondering how i can apply this to muliple colulmns? If anyone can help out, that would be awesome. Thanks!

    • Lori says:

      has anyone received info on the 2010 version question, and/or applying to multiple columns, and/or using a reference list (vs typing in a list)?

  29. NLD says:

    This code is great, but I can’t delete a row when use it in my workbook. When I autofilter and delete rows based on my criteria, the actually rows don’t delete, but the rows at the bottom of my table do. Please help!

  30. Tom Flynn says:

    This is a great code I need it to do this on the same worksheet for multiple columns, I use
    IF Target.Column = ActiveCell.Column Then

    This will also keep you from having to update the column when copying from page to page

    Thanks again

  31. Jill says:

    This is great for logging the information needed on a daily basis. Thank you for that.

    Is there any way to get this to work on a protected worksheet?

    I have several formulas as well and want them protected but when I protect the worksheet, the multi selection stops working.

    Thanks in advance.

  32. Walter A. Quiroz says:

    You’ve just saved my life, thank you for your support.

  33. Dwayne says:

    This worked great however if an error was made in the selection my team can delete or edit to change. Anyone have an answer for this

  34. Sarah says:

    I can’t get the sample coding you have provided working in excel 7 or 10 but it works great in 03. Is there something I need to do to get this to work??

  35. Leigh says:

    HI I alos need it to work for mulitple columns but niether of the ideas further up in the thread had worked for me. ANy other ideas?

  1. November 14, 2013

    […] need to do multiple selection same cell drop down lists. Easily done by following this tutorial. However, it only works in column C. I see in the VB code it refers to column 3 alone, so what do i […]

Leave a Reply

Your email address will not be published. Required fields are marked *