One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel.
When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.
He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.
Something as shown below in the pic:
There is no way you can do this with Excel in-built features.
The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value.
Watch Video – How to Select Multiple Items from an Excel Drop Down List
How to make Multiple Selections in a Drop Down List
In this tutorial, I will show you how to make multiple selections in an Excel drop-down list (with repetition and without repetition).
This has been one of the most popular Excel tutorials on this site. Since I get a lot of similar questions, I have decided to create an FAQ section at the end of this tutorial. So if you have any questions after reading this, please check out the FAQ section first.
There are two parts to creating a drop-down list that allows multiple selections:
- Creating the drop-down list.
- Adding the VBA code to the back-end.
Creating the Drop Down List in Excel
Here are the steps to create a drop-down list in Excel:
- Select the cell or range of cells where you want the drop-down list to appear (C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria.
- In Source field, select the cells which have the items that you want in the drop down.
- Click OK.
Now, cell C2 has a drop-down list which shows the items names in A2:A6.
As of now, we have a drop-down list where you can select one item at a time (as shown below).
To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end.
The next two sections of this tutorial will give you the VBA code to allow multiple selections in the drop-down list (with and without repetition).
VBA Code to allow Multiple Selections in a Drop-down List (with repetition)
Below is the Excel VBA code that will enable us to select more than one item from the drop-down list (allowing repetitions in selection):
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Now you need to place this code in a module in VB Editor (as shown below in the ‘Where to put the VBA code’ section’).
When you have placed this code in the backend (covered later in this tutorial), it will allow you make multiple selections in the drop down (as shown below).
Note that if you select an item more than once, it will be entered again (repetition is allowed).
Try it Yourself.. Download the Example File
VBA Code to allow Multiple Selections in a Drop-down List (without repetition)
A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.
Here is the code that will make sure an item can only be selected once so that there are no repetitions:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Now you need to place this code in a module in VB Editor (as shown in the next section of this tutorial).
This code will allow you to select multiple items from the drop-down list. However, you will only be able to select an item only once. If you try and select it again, nothing would happen (as shown below).
Try it Yourself.. Download the Example File
Where to Put the VBA Code
Before you start using this code in excel, you need to put it in the back-end, such that it gets fired whenever there is any change in the drop-down selection.
Follow the below steps to put the VBA code in the backend of Excel:
- Go to the Developer Tab and click on Visual Basic (you can also use the keyboard shortcut – Alt + F11). This will open the Visual Basic Editor.
- There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).
- Double click on Worksheet Name (in the left pane) where the drop-down list resides. This opens the code window for that worksheet.
- In the code window, copy and paste the above code.
- Close the VB Editor.
Now when you go back to the drop-down and make selections, it will allow you to make multiple selections (as shown below):
Try it Yourself.. Download the Example File
Note: Since we are using a VBA code to get this done, you need to save the workbook with a .xls or .xlsm extension.
Frequently Asked Questions (FAQs)
I have created this section to answer some of the most asked questions about this tutorial and the VBA code. If you have any questions, I request you to go through this list of queries first.
Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells? Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then
Q: I need to create multiple drop-downs in entire column 'C'. How do I get this for all the cells in the columns with multi-select functionality? Ans: To enable multiple selections in drop-downs in an entire column, replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Column = 3 Then On similar lines, if you want this functionality in column C and D, use the below line: If Target.Column = 3 or Target.Column = 4 Then
Q: I need to create multiple drop-downs in a row. How can I do this? Ans: If you need to create drop-down lists with multiple selections in a row (let's say the second row), you need to replace the below line of code: If Target.Address = "$C$2" Then with this line: If Target.Row = 2 Then Similarly, if you want this to work for multiple rows (let's say second and third row), use the below line of code instead: If Target.Row = 2 or Target.Row = 3 Then
Q: As of now, the multiple selections are separated by a comma. How can I change this to separate these with space (or any other separator). Ans: To separate these with a separator other than a comma, you need to replace the following line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of VBA code: Target.Value = Oldvalue & " " & Newvalue Similarly, if you want to change comma with other character, such as |, you can use the following line of code: Target.Value = Oldvalue & "| " & Newvalue
Q: Can I get each selection in a separate line in the same cell? Ans: Yes you can. To get this, you need to replace the below line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of code: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine inserts a new line in the same cell. So whenever you make a selection from the drop-down, it will be inserted in a new line.
Q: Can I make the multiple selection functionality work in a protected sheet? Ans: Yes you can. To get this done, you need to do two things: Add the following line in the code (right after the DIM statement): Me.Protect UserInterfaceOnly:=True Second, you need to make sure the cells - that have the drop-down with multiple selection functionality - are not locked when you protect the entire sheet. Here is a tutorial on how to do this: Lock Cells in Excel
You May Also Like the Following Excel Tutorials:
This was exactly what I needed. Followed your steps and it worked perfectly! Thank you!!
Using your multiple option list – I am trying to put in more than “one change action” on a worksheet and it wasn’t working. I tried a few more things and still no dice.
Do you have the code that you could share? I am sure there is a trick to it.
Vanessa
I have followed all of the steps that you have provided and i am getting an error. – Compile error- what does this mean and what do i need to do to fix it so that i may be able to select multiple different items in the drop down menu. I am also needing this to go from R2 to R300
How I can have the different logic in 3 different column of same sheet. Please suggest
When I protect the worksheet, it will not allow users to multiselect in the cell. Is there a way to fix that?
This is great. Thank you for sharing the code. I want to do more out of this, however, there are some problems.For eg., I cannot delete anything added by error. My teammates running this on Mac face problem where the highlighted value from the list gets added if one wishes to abort by pressing ESC.
Any ideas on how to implement such changes ?
FANTASTIC! WORKED LIKE A CHARM Thank you.
How can i make multiple selection dropdownlist work in all columns of a worbook
Thanks for this.
how to make the list visible while selecting more than one item from the list. now if i want to select 3 items from my list, i have to click 3 times and select each one separately. would more convenient if the list stays open until i finish selecting my items.
Yes….this!!! How to keep the drop down open?
Thank you, this is helpful! In my list, I have a wildcard (Other: ***), that I want people to be able to replace with an additional response. However, when I do that with the above code, it copies what I have listed and then shows it again with my changes.
i.e.: First choose “Option 1”, then chose “Option: ***” which I change to “Option 4”. The output is “Option 1,Option ***,Option 1, Option 4”. I want to get rid of “Option ***” and “Option 4”.
Any suggestions?
Thank you, Sumit for this very helpful instruction. I have the same issue as Benny. I have a list of stakeholder names and want to provide people with the option to write the name of a stakeholder outside of my list. Disabling the Error Alert in data validation allows me to do that. However, when someone selects an option (e.g., finance) from the list and then writes the name of a new stakeholder, I get: Finance, New Stakeholder, Finance, New Stakeholder.
on the other hand, if someone types the name of the new stakeholder first and then select from the drop-down list, I get: New stakeholder, Finance
Is there any way to fix this to make sure we get the same results whether people type before or after selecting from the existing list?
Thanks again.
Your code has nearly got me to what i need done , Many thanks. I just need to be able to select more than one on a drop down list on spacific cells, Currently any drop down box in colum c but ideally need to just choice for eg c13, c16 ,e16 can this be done
I need to add multiple selection in just spacific cells can this be done
I need to create multiple drop-downs in multiple columns (like C and F). How do I get this for all the cells in these columns with multi-select functionality?
dear sir,
very informative. can you please tell me how to apply multiple selections in multiple column in a protected sheet?
I have Excel 2010 and cant seem to get this to work. Any ideas?
Amazing! Thank you so much for sharing your expertise! I had never used any code of any kind in Excel and your step-by-step tutorial made this simple to execute.
Is there any way to modify the code so that I can manually drag a cell’s contents into another part of the table? In the table I have built, each user has one entry per row, but they should be allowed to drag that cell to another column in the same row. As the code stands, the only way to do this is to delete the cell’s contents and retype it in the new column, which is not ideal.
Thanks!
I really need to know if there is a code that allows to remove the selected object in the multiple selection list. Please, it will be really helpful.
Do you know if Excel have the function to have fillable group populate after you select from a selection list?
Thanks Sumit for this excellent trick. This worked like a charm. Can you please advise, how can I now count the number of selections made?
I just finished using your code for reoccurring choices. Thanks, it works great. My next task is to work on exporting it to an Access DB.
First, Thank you. This is extremely helpful and it worked! I just have one issue. It changed my date format. My list has a date format of mmm-yyyy. When I add this VBA code it allowed multiple selection, but changed the date format from DEC-2018 to 12/01/2018. Is there someplace in this code I can specify the date format mmm-yyyy??
Thank you in advance! Greatly appreciative of the help.
How can unselect item from selected?
I have copied your VBA without repetition but it doesn’t seem to work. Note I made two changes – (1) replaced the target address to $B$9 and (2) the spacing between selections from “,” to vbNewLine.
Can you please give me ideas why this doesn not seem to work?
It worked like a charm, instruction and layout is very clean and clear. thank you so much!
Hi I have just built my spreadsheet which is perfect. Now I need to make one of my drop downs editable. When you put other and want to put in an explanation.
I’d like to alpha sort the multi pick list results so that the list displays alphabetically, not in the order it was selected. Any suggestions?
Hi, Thanks for this helpful guide. I copied the VB script and changed the column from C to P and it worked – however I needed to create multiple drop-downs in the entire column ‘P’ rather than C – when I followed the instructions given for this is only worked on column C – and I could not see what I needed to change to make it point to column P. Please can you advise what I need to change in the code to make this work. Many thanks,
Jenny
So I figured this out now – I just needed to replace 3 with 16. However, I can see that there is a problem trying to deselect an item. I see other queries about this – is there a way to do that please.
Dear Sumit, thanks for the great code,it works fine with me however when i send the excel file to my college they couldn’t open the file they had the following warning ( this message had contained attached files that deleted during attachment filtering .xlsm) do you have an answer that could help me please
How can we delete only one selected value form multiple Drop down option
Try this:
If InStr(1, Oldvalue, Newvalue) = 0 Then ‘ Add
Target.Value = Oldvalue & vbNewLine & Newvalue
Else ‘ Remove
If InStr(2, Oldvalue, Newvalue) = 0 Then ‘ 1st entry
If Oldvalue = Newvalue Then
Target.Value = “”
Else
Target.Value = Replace(Oldvalue, Newvalue & vbNewLine, “”)
End If
Else ‘ Not 1st entry
Target.Value = Replace(Oldvalue, vbNewLine & Newvalue, “”)
End If
End If
Thx Hyperion135:
had to make a few changes to your code.
Here is what your final code looks like inserted in the original “without repetition code” provided by Sumit Bansal.
Works like a charm!
‘ To allow multiple selections in a Drop Down List in Excel (without repetition & delete selected value from dropdown list function)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 6 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = “” Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = “” Then
Target.Value = Newvalue
Else:
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
If Oldvalue = Newvalue Then
Target.Value = “”
Else:
Target.Value = Replace(Oldvalue, Newvalue & vbNewLine, “”)
End If
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
I need to use Multiple select drop down for Marge Column Example :–
I Have Marge C2 , B2 , D2
What address do i need to provide
I have managed to get each selection to be on a separate line within the same cell, however when using that cell to place on another sheet within the same workbook the two are pushed together again. Is it possible to adjust this?
Facing the same issue if you know the answer please let me know.
Hi Hollie, how did you manage to get it on a separate line in the same cell? I really need to do this but I can’t figure out how.
How do you deselect an item? I used the code without repetition, but now I want to deselect and item, but nothing happens and I can not delete the item. For example I select one, two from drop down list. Now I do not want two, how do I get rid of it? Thank you.
Hi there, this doesn’t work when I protect the worksheet. I am using this code to make a rudimentary form, so I want to protect the other cells from editing. The cells that contain the dropdown are not protected but the vba still doesn’t work. As soon as I protect the worksheet the drop downs only allow a single selection. What can I do to make this work. My apologies if this is answered somewhere and I didn’t see it.
Thanks for the great site and sharing your Knowledge!
This works great. I am having a problem counting the selections now. I have a separate count section that every time I select “white” in a column, it will then count those occurrences in another section. Now when I select “white, Black, native” the count doesn’t work. How do I fix this issue? Basically, I want to be able to select multiple options in a drop down and have them be counted separately. The code I use for the count is =COUNTIF(C:C, “BLACK,”)
This worked and was so easy! Thank you so much!
I’ve managed to do everything but my multiple choices still kicks out the previous choice. I have gone to address and changed it to column and on $c$2 to 4 as I want the whole column.
I’m doing this for a non for profit service dog trainers so we r not techies. I thought I was following the directions is my software or system too old? It really need this little thing it could change our who training program. Thank you in advance I feel you were very easy to follow but I am missing something.
Hi Sumit – your VBA instructions are great and I love that you’ve thought about all the other uses (ie duplicates / non duplicates, separate line in cell) as it’s perfect for what I want! I’ve done some tests which all work fine and will add the code to my working spreadsheet. Fingers crossed!
I noticed that after adding the codes that allows me to select item once, I can’t delete the last input if it was incorrectly selected. It will give me validation error message. How do I counter this issue?
can I applied this for other columns in the same worksheet?
You are brilliant! thank you very much for posting this. It seems to work great!!
Thank you for this code, works great. How do you deselect an item? I used the code that does not allow selection of the same item but now I want to deselect but nothing happens and i can not delete the item. For example I select one, two from drop down list. Now i do not want two, how do i get rid of it. Thank you.
I also want to know this
How do I alter this code so that there is an ending row for the multiple selections of the drop-down list?
For example, I have a table on excel with a drop-down list in columns 2 and 3 (B and C). I would like to alter this function so that at the bottom of this table, the function stops. I would also like to be able to use functions, such as count in column 4 which is D, on excel.
All help is appreciated, thanks so much!
I cannot make it work. I downloaded the file to see what the problem was and I cannot make it work with that either. I can still only have one selection from my dropdown box. What am i doing wrong?
Thanks for the tutorial but I think I must have missed something.
I put information for drop down list in Sheet 2.
Sheet 1 – created drop down list and targetted information in Sheet 2.
Pressed Alt-F11 and got the Visual Basic for Applications (VBA) screen, selected Sheet 1, pasted the code and closed VBA.
Tried to select more than one item from drop down list and only got one item showing at a time.
Reopened VBA, deleted code from Sheet 1 and copied the code to Sheet 2 then closed VBA.
Tried to select more than one item from drop down list and only got one item showing at a time.
Not sure if need to have a particular cell or sheet selected before Alt F11?
Using Microsoft Office 365 ProPlus.
Cheers.
How can I get the code to choose items in a dependent dropdown list? Suppose I have a different list in each dropdown?
Thank you. Hope this will help this old man learn and understand excel
I copied and pasted the code and it works!!!
now, how do you sumifs multiple values from multiple drop down list using named ranges ?
i.e. suming multiple product groupings as well as multiple regions .
I copied and pasted the code and it works!!!
now, how do you sumifs multiple values from multiple drop down list using named ranges ?
i.e. suming multiple product groupings as well as multiple regions .
Hi, How can i get this to apply to multiple worksheets?