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:
AWESOME, easy peasy (non techie here!)
In the code window, when you put in the code, the drop down on top changes from Declarations to Change. Mine is not doing that and the code is not working. Please help
hello thanks for great help.i have a problem. i use vlookup for my list and when choose a item in list the price of item show in other cell. now i want when choose multi item in list sum the price of item sum and show. please help me
How can I do for multiple drop down lists for each cells in the same column? For example, in Column A, a cell as in A1, A2, and A3.
How to get the count of selected items instead of those values?
How can I get the count of selected values of drop down list insted of those values?
Need 2 multi pick list in same sheet how to use this code?
Doesn’t work
Thanks Sumit for the excellent tip. Please suggest how do I apply it to multiple columns in the same work sheet?
How do I sort the data once it its entered? If, for example, my dropdown list contained the values: One, Two, Three, Four and someone were to select them in the following order: Two, One, Four, Three. How would I go about re-ordering the entry to coincide with the list order?
Im want to use multiple selection list in many sheets in one work book. Each list, in each workbook is in different cell but all the lists are created from same named range, eg. Multiplelist. Can I use that code for all lists created from named range Multiplelist in whole workbook?
can i make multiple selection in different coulmn of a single sheet?
This does not seem to work if I protect the sheet or workbook. How can I get it to work on a protected sheet
What happens if I want to use a multi-select in different columns and the data validation list options are different for the columns? Does the VB code for the various columns need to be on different sheets? or do the lists just need to be different names?
How to Make Multiple Selections in a Drop Down List in Excel (non repeating)
I don’t know what I am doing wrong. I cannot make this work.
Hi, the coding has applied itself to the whole sheet instead of the target columns I have told it to use? How do I overcome this?
I want to do the same thing in each column from “B” through “R” whats the easiest way to be able to add this to the code?
How can I remove already selected word from the drop-down list? example
selected: one, two, three …..now I need to remove three and
re-select four
i have the same question, did you solve it already?
I have the same problem do you have code for this ?
If there is a known solution I would love to hear it 🙂
I have the same question, any solution to this?
Honestly can’t get this to work. I want to reference a list defined in Worksheet X from cells in Worksheet Y. I have a range of values in consecutive cells in Worksheet X – let’s call them A, B, C, D, E. I’ve copied the code above and no joy. First question: how do I reference Worksheet X from cells in Worksheet Y – do I need to change the $C$2 in the example code to something like =X!$A1:$A5 ? Does this VBA code need to be in Worksheet Y or X?
Hi I just wonder if you can apply this multiple selection drop-down list to a whole column but only in specific table? Thanks
This is highly useful, many thanks!
However I do have a question: How can I achieve that in the filter function (in the headline of any column), the different options do appear separately even if multiple items have been chosen in the fields of the table ? Many thanks for any hint!
This is so helpful thank you! I was wondering, is there any way to create a pivot table from this which give information about how often one of the options is mentioned. So for example, lets say I have a table of fruit choices for 5 people (separated below with 😉 with drop down list as above, the responses are banana, apple, orange; apple, orange; banana, orange; apple; banana, apple, is there a way to run a pivot table so that I know how many people chose banana, how many chose apple, etc. I’ve tried it but it’s only giving me numbers for their full responses. Hope that makes sense!
Life saver! Thanks a lot Sumit!
I restricted the code to work in a range of cells, and I added “Please select!” in the cells to apperar when you press delete to cleare it.
Works fine for me with the code below.
Good luck!
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range(“M6:M20”)) Is Nothing 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 = “” Or Oldvalue = “Please select!” 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
Application.EnableEvents = True
Exitsub:
If ActiveCell.Value = “” Then
Target.Value = “Please select!”
End If
Application.EnableEvents = True
Exit Sub
End If
End Sub
Removing/deleting an item in your list (eg after clicking a wrong one) and at the same time also removing leading and trailing spaces and comma’s works fine for me with the code below. Good luck.
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:
RemoveLocation = InStr(1, OldValue, NewValue)
ItemLength = Len(NewValue)
If Len(OldValue) Len(NewValue) Then Target.Value = Left(OldValue, RemoveLocation – 1) & Mid(OldValue, RemoveLocation + ItemLength + 2)
End If
End If
‘remove leading and trailing spaces en comma’s
Do While True
If Left(Target.Value, 1) = ” ” Or Left(Target.Value, 1) = “,” Then
Target.Value = Mid(Target.Value, 2)
Else
Exit Do
End If
Loop
Do While True
l = Len(Target.Value)
If Right(Target.Value, 1) = ” ” Or Right(Target.Value, 1) = “,” Then
Target.Value = Left(Target.Value, l – 1)
Else
Exit Do
End If
Loop
End If
Where would I place this code relative to the original one provided by Sumit? Thanks
Hello, I’m getting an error on this line –
If Len(OldValue) Len(NewValue) Then …
Any thoughts? Thanks.
i need help, please do to assist me sir.
my problem is how to make dropdown list in excel displayed in 3 rows.
eg:
A
B
C
when A (row 1) is chosen then B and C also automatically displayed in row 2 and 3.
please assist me sir, and thank you so much for your attention.
Hi!
Try the code I posted above.
Hi
This is a very useful and simply explained video. I have watched few videos. However, this one is the best and simple. like it very much. Thanks.
I need to know how to apply this in multiple columns
Hi, the macro is working well but when I write in the cell and or erase it keeps the old and new value. How can i modify the macro to make erasing possible? thank you
Summit, thank you for this great VBA code. How to save this vba code with my worksheet? Everytime I close my workbook then reopen it, the VBA code is gone and I have start it all over again?
sir
I need all Excel sheet in all drop down list multiply selection kindly send code or excel formula apply.
Thanks
Sumit, this an is excellent macro, thank you. One thing, I have the macro set to return the results with each item on a new line. however, the cell height is not adjusting and my responses are getting cut off. I do have the Wrap text option selected for the cells. I also have the selection macro running on more than one row however it is running all in the same column, so the macro would need to run regardless of the row the data is in. Would you be able to write/or tell me an addition to the macro that will adjust the cell height after it is filled?
thank you so much for your help
thank you for this great tutorial! I was able to set everything up but then when I tested it , I was still only able to pick one item only. I copy pasted the exact code. can you elaborate on why this would happen? thanks
Sabrina
I used this code a year ago and it worked. Now I wanted to use it again and it isn’t working anymore. I retrieved my old file from last year and the multiple selection I used is gone, only one of the options is left. I downloaded the sample file from this webpage and it isn’t working on this file. HasExcel changed in the meantime and do I need to modify the code?
Hi Sumit Bansal, thank you so much for your help with the coding – I cannot tell you how much it has helped me and made my life so much easier :). Please could you advise if you are able to help with creating a list box in word with the functionality to select multiple dropdowns? I have searched the web and been unsuccessful. Your help will be greatly appreciated. Many thanks.
Can you make multi selection drop down boxes in more than one column?
This works great until I save it and try it again. After starting the spreadsheet again, the functionality fails. I tried saving it as an .xlsm. Any ideas
Thank you very much for this tutorial, this was exactly what I needed. However, when I use Filter function in this column to sort results, it does not recognize individual word in cells with multiple choices. As exemple, “xxx” will be recognized if standing allone in the cell, but not in cells with two choices, like in “xxx, yyy” – it simply does not appear in filtered results at all. Is there any way to correct this? Thank you!
My scenario. My first column displays a list. The second column displays a list depending on the first column selection. I am able to make multiple selections in the first column. However, the next column will not let me make selections off of the first columns multi-selections if i choose more than one.
What if I select an item and then want to deselect it from list?
Private Sub Worksheet_Change(ByVal Target As Range)
‘ To allow multiple selections in a Drop Down List in Excel (remove when selection is repeated)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 35 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 Oldvalue = Newvalue Then
Target.Value = “”
ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & “, ” & Newvalue
ElseIf InStr(1, Oldvalue, Newvalue) = 1 Then
Target.Value = Replace(Oldvalue, Newvalue & “, “, “”)
Else:
Target.Value = Replace(Oldvalue, “, ” & Newvalue, “”)
End If
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
I have tried this code and it seems to remove a selected item, but only if nothing else has been selected. That is, if I select item A, and then select item A again, it will disapear. However if I select item A and item B then click item A again to remove it, it does not remove item A. I just does nothing.
Would you have any further suggestions?
Thank you
Hi Minbee,
In my workbook it works with this code. I can remove any item what I want. So also Item A after an item B is selected. So I am not sure why it doesn’t work in your version.
The only thing you have to adjust is the column number. (35 in my example) but I assume that you have done that.
Maybe you can post your code here then I can have a look.
Thank you so much! This worked great!
Hi,
I was excited to see your video on this. I have a range that I need to apply this to and it looks like this is mostly for a couple of columns or cells. I need this for a range of D17:W76. Is there a way to alter your code to accomplish this?
Hi,
I’ve created a worksheet with the multiple selection functionality in a protected sheet, however I can’t get the VBA code to run whilst the spreadsheet is shared with multiple users. Please could you help or provide any suggestions for a novice like me. Thanks. Dan.
Hey! Thanks for this – super useful. Quick question; I need this code to appear twice in the same sheet. Once with “, ” and once with ” “. When I try to use them both I get compile errors. Appreciate any help!!
How can I use the non repetition code for multiple columns on the same report?
Works like a charm. THANK YOU!
I am using the code that allows repetition, but when i select the same value multiple times it does not append the target.value. For instance i select 0.1, 0.2, 0.1 and the output is just 0.1, 0.2
Tried it in a row. Worked perfectly well . Thanks
when i try to enter the line ” Target.Value = Oldvalue & “, ” & Newvalue” i get an error message, it highlights the “,” and a box pops up Saying compile error “expected end of statement. What do i need to do to fix that.
Hi, could you please provide a code for the same first example above such that I can delete a value after choosing it from the drop list?
I have 3 columns in which I wanted to apply the multichoice option, but each column has its own options. Is it possible to do this?
Thanks!
i want to do this as well. can anybody help?
This looks great but for some reason the code does not work here… I copy paste it but still I cannot make multiple choices from the list. :- appreciate some help!
did you get an answer? I am having the same trouble.