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:
Great code!
If anyone would like to remove an item from the list by clicking on it again, change the following lines of code:
from Target.Value = Oldvalue & ” ” & Newvalue
to Target.Value = Trim(Oldvalue & ” ” & Newvalue)
and
from Target.Value = Oldvalue
to Target.Value = Trim(Replace(Oldvalue, Newvalue, “”))
Don’t forget to Save your workbook as “Excel macro-enabled workbook”, so you don’t lose your VBA code every time.
Thanks Jose. However, when i use this it doesn’t delete the comma so I end up with double commas. How could I adjust the code to correct this?
Please try this which takes care the coma as well.
iPos = InStr(1, Oldvalue, Newvalue)
If iPos = 0 Then
Target.Value = Trim(Oldvalue & “, ” & Newvalue)
Else:
If iPos = 1 Then
If Target.Value = Newvalue Then
Target.Value = “”
Else
Tmpvalue = Newvalue & “,”
Target.Value = Trim(Replace(Oldvalue, Tmpvalue, “”))
End If
Else
Tmpvalue = “, ” & Newvalue
Target.Value = Trim(Replace(Oldvalue, Tmpvalue, “”))
End If
End If
I got this to mostly work. It will never remove the first item I select in the list. I had to copy this code into 2 places to make it work. Do you have the whole code what would make this work all put together?
Dat / Kens , did u got a solution to avoid multiple commas? if yes, then pls do share.
Is there possibly a way to set one value from the list to be exclusive. In other words, my list has Standard, Glass, Tile, Wood. I would like the to be able to select one or more of Glass, Tile, Wood, but if I select Standard, it should prevent adding any of the other values… Or, if other values are already listed, selecting Standard should replace the text.
This is amazing! I had literally just told a colleague this would be impossible. Is there by chance a way to go back to the cell and remove one entry without getting the error message (due to the data validation; list)?
Not working for me
What is the VBA code modification to enable the drop-down in all rows and columns, not specific cells, rows or column, but the entire spreadsheet?
Thank you for your code it helped me allot with this big project I am working on. If a user accidentally selects and item and was not needed, how do they just clear that item without starting their process all over again?
Its Work only at once however after reopen the file its not working. So i follow the above given steps again still its not working. Im using Excel 2016
Hello, thanks for the above info. 🙂 Just need help on how to apply the multi drop down selection for multiple columns? I have already tried in 1 column and it works but i tried to add 1 more column and error prompts. Thanks in advance for your help. God bless. 🙂
I am also looking for an answer to this problem!
Continued scrolling through the comments, and found the answer in “Muhammad Elboreini”s post! I now realize that it is also in the FAQ, under the 2nd and 3rd questions 🙂
Can I have the count of the number of selections displayed in the cell and the selection list shall be shown upon clicking the cell. Is it possible.
Because I am working in the construction industry and for my labor allocation I will be selecting the labor names from a list. However, I want to display only the count. The names of labors selected will be displayed upon clicking the cell.
Thank You! Great Information and presentation!
Great solution to a nagging problem. Thanks!
This is really helpful. I have finally managed to activate multiple selection of items on a drop down list for more than one column.
Thanks alot.
Very useful article, thanks for sharing
Hi, i want make this method for 2 diferent cells in the same document but i its working just to one cell.
I tried use comand and/or, copy chancing the cell but doesn’t work.
I am very inexperient in VBA, can help me?
Best regards.
Thanks so much for this! How to I apply it to an entire column except the header for that column in line 1? Many thanks
When I want to remove 1 (A) value from list of say 3 values (A,B,C), its not working for me, I have to delete all the values in cell and start new selection for values.
Do we have to save the file in a macro-enabled template? If so, I saved my data and the VBA code is not functional
Hello! Thank you very much for this tutorial. But I have one question. What if the drop-down item list is placed in another different worksheet? Is it possible to synchronize this function between different worksheets of the same workbook?
Lea, press f3 under source in the data validation dialogue box to select your dropdown list from any sheet
Thanks v much .. so helpful.
How would I run this Macro with the same purpose under multiple columns with different drop downs for each column (drop downs are already setup)
2 things. 1.I tried using a range instead of cell address and it would not work. Can that be done. I even changed the Target.Address to Target.Range. 2. My drop down list has a default of “Select One”. How can that be replaced with the first selection? As it works now my selection is just added to the Defaulted ” Select One”.
Vert nice 🙂
How would one extend this to work when sharing via O365 ?
I’ve tried& it doesn’t work. Not sure if it’s a limitation from O365 or if I need additional steps.
I have copied the code correctly but i still cannot select multiple items at a time.
I did exactly as per your instructions. The only difference is the cell change based on my workbook (H2 instead of C2). The dropdown still works as a single selection. New selection replace the old.
Change
If Target.Address = “$K$2” Then
to
If Target.Column = “$K$2” Then
This worked for me to apply to a whole column, but I have not figured out how to apply to multiple columns as yet.
You can use the following to use it for multiple columns:
If Target.Column = 2 or Target.Column = 3 Then
This will make this work for column 2 and 3
Hi Sumit, Can the VBA code work for columns that are not consecutive? I can add VBA code for columns 14, 15, 16. But let’s say I have multiple selections in column 3 and another set of multiple selections in columns 14 and 15. How would the code look?
Code below does not work:
If Target.Column = 3 or Target.Column = 14 or Target.Column = 15 Then
And following question, if in sheet 1 cell A1 I has vba code with multiple selections, can the same selections be returned in sheet 2 cell B2? That way the data auto populates and does not require to input the vba code for sheet 2 and manually select the second time.
Thank you for taking the time and educating us.
I have the same doubt, I was able to do it with consecutive columns, but when they are not, it no longer works, did you find a solution?
how to make drop down list only can choose 2 items by user. Even in drop down list have many choice
I was wondering the same thing
How to remove mistakenly selected drop down? Please suggest
your issue resolved? even I want the same answer
An easy way that requires no code is to add a blank cell to your range. when you select the blank cell it clears the rest out.
How do I unselect the items from the drop down list? I have created the multi select no repetition?
I am having the same exact problem. Did you find an answer?
Hi, is it possible to add a function that unselect an item from a drop down list? I am using it to specify users of equipment, and let say we had 5 users, but now one of the users is not using equipment anymore. So I wish to remove just one user. Now i need to remove everyone and select them all over again….
Same problem
Same issue from my side, and if I also go to the cell and delete, doesn’t help because it will appear once again all the options selected previously. In my case, I just have remove all the code from VBA because it doesn’t help…It’s sad because it is a good idea, however from what it see the code doesn’t take this in consideration and anyone as provided a solution either…If someone has figure out something it would be nice to know.
The XLSM file with VB scripts when opened in other PCs the VB script doesn’t work. Why?
Incredibly helpful tutorial – thank you!
Hi Sumit,
The code was really helpful.Thank you.But if there is an option “All” in the list, how to include that so that once this option is selected,no other option can be selected.
Hi
Thank you for this quick fix, great!
How do I lock the values of a cell (data range) when I make a table of many rows?
Each new row creates a new range of data values, selection +1 for each new row.
Thx!
How can I remove a selected item in a multiple drop down list?
How do I apply this to other columns and cells within the same sheet?
Hi Peter, I have the same question…
So easy to understand and replicate in my documents. Thank you!
Can you please help with another issue I have in regards to macros and multiple section of various sheets for PDF conversion and/or printing.
Can you help?
Mike H
Can I make the multiple selection functionality work in a sheet protected with a Password?
I found this solution worked perfectly, but when I opened the workbook again the ability to make multiple selections no longer worked. I notice Rachel also commented below having the same issue. Is there a fix?
Hi Karen. I fixed this by clicking the ‘Enable Content’ button in the yellow bar when you first open up the workbook. Otherwise the macros are disabled.
In case of multiple columns, if dropdown is not there and user manually enter the data, that data also concatenating. For e.g. IF target column is C, now enter 1 and press enter, then press 2 and enter. Both digits concatenated(1, 2). How to stop this?
Thank you for sharing this!
Hello! How do you make the items are sorted alphabetical in every cell?
it doesn’t work. I went step by step few times and it does not work
Hi Sumit. This really great and helpful. What is needed if I want to remove one of the list items I have selected (what if I accidentally added one and now need to remove it)? Thank you for your help.
Hi Michael, I would like to know this also, did you find a solution?
I am too looking for a solution to remove an item from the list.
Try deleting all the selected items and then add them again.
HI!!
I need to create multiple drop-downs in all the rows. How can I do this?
Followed your video and no luck
how do you use this code for a range in a workbook containing multiple worksheets
These steps work perfectly, but when I close the file and reopen, I can no longer select multiple items. I’ve tried several fixes. Any thoughts?
This is great but what if I make a wrong selection. How do you then remove or change it?
Will this work for multiple sheets if I update in Module
Hi,
Thanks for the code,very helpful. Now how do i change this line to apply the macro from cell H4 to H50:
If Target.Address = “$H$4” Then
Thanks a lot
Found it, need to replace the line by:
If Not Intersect(Target, Range(“H4:H50”)) Is Nothing Then
Hello, I wasn’t able to get this to work when I replaced if Target.Address=”$H$4″ Then with If Not Intersect(Target, Range(“H4:H50”)) Is Nothing Then. Any suggestions?
what is the code for dependent data validation. Your code desnt works in that case. Can you please help
Hello, I’m working with Excel Online, and do not have a developer tab. Is there a way to do this on the online version?
You can add the developer tab by going to File>Options>Customize Ribbon>Check Developer tab> Click OK