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:
Hi,
I need help modifying the code.
I want to appear “Please select” in the Target.Address/Target.Column.
I tried but i failed. 🙁
I love the one with no Repetition but it has a flaw I need help with. I disable error and ignore checkbox via the data Validation this way it give me the option to have a drop down with multiple selections but also the ability to still add my comments so to speak it then repeats the selected selections. The next issue is that when I select 2 options in my drop down list and then try to delete one it then duplicate and add them back to speak. Are we able to get one where I have be able to delete one of my selection if I no longer decide to have multiple selection with it then repeating. Same goes for when I want to add my own comments that is not on the drop down list source. I I’m basically looking for a drop down list to be able to pick multiple options and still be able to add my own comments in the same field with out causing any errors or it duplicating my selected options and be able to delete.
I inserted the code so that the checklist would appear in every cell in column E but it didn’t work. I made the change:
If Target.Address = “$C$2” Then
with this line:
If Target.Column = 5 Then
My validation list is on another tab. Is that the problem?
Hi Sumit, i have inserted the code for multiple selections (without repetition) within a column for multiple sheets (same template) but it does not work. Not sure if the cause is conditional formatting of the options (which I’ve coloured) or if it’s something else? Appreciate your help here, thank you!
How do I create multiple selections in multiple columns in the same file? Right now, I can only get the code to apply to one column at a time.
Hi Sumit,
Thank you for your great assistance in multiple selection drop down list through a VBA code. I have used it and it is working fine for me. Only, change in the output I need per my project’s requirement is, can we have the selections in either new rows or columns rather new line? It would be a really helpful if that is possible.
I copied and pasted your code. It does not work when I use the target.colum line. see below:
If Target.Column = 3 Then
The above code does not work unless I put I write with with quotation marks –>
If Target.Column = “3” Then
Now I can choose multiple items, however, I get a Data verification error. When I use the debugger it points to:
Application.EnableEvents = True
Can I do this for a whole workbook?
I have successfully created a multiple drop-down list without repetition – thank you. However, when I apply the “Me.Protect UserInterfaceOnly:=True” code, so I can use it when the sheet is protected, it doesn’t hold its password protection and by simply clicking un-protect the spreadsheet becomes exposed. Do I need more code?
This was awesome, thank you! How do I reset a list where selections have been made 🙂 My list is LARGE – 123 unique values
Hi Sumit,
Your code is a life saver, Thank you. It saved a lot of re-work for me.
However in my case, the data is getting duplicated in case I am selecting one item multiple times. Any suggestions?
Excel Version: MS Excel for Office 365 MSO (16.0.11929.20708) 64-bit
List of values:
One
Two
Three
Four
Five
Output (upon multiple selection):
One, Two, Five, One
Regards,
Dj
Hello, this was spectacular thank you for creating.
The code you provided to enable the code while protected is great – HOWEVER it is constantly protected. I cannot switch it off? Everytime I make an adjustment to a cell, I have to unlock it again. How do I make it so that it looks ONLY when I switch on protection? Thank you.
If you want to add more than 2 column then place column numbers in below formula. I did it from column no.9 to no. 20. If and then should be used in start and end only.
If Target.Column = 9 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 12 Or Target.Column = 13 Or
Target.Column = 14 Or Target.Column = 15 Or Target.Column = 16 Or Target.Column = 17 Or Target.Column = 18 Or
Target.Column = 19 Or Target.Column = 20 Then
Hi.. thanks a lot for sharing this, it’s really helpful. But is there a way to limit the amount of selections one can make in a drop down list to say, 5? Currently there’s roughly ~25 items in the list and I only want them to be able to select a maximum of 5 of those at a time. Thanks you.
Hi,
Thank you for this very comprehensive tutorial!
I wanted to know if I can have different multiple selection drop-down menus for different columns. I explain: on a worksheet named Services provided I have in column B a drop-down menu for target clients; in column C a drop-down menu for basic services and in column F a drop-down menu for Specific areas of expertise… how can I do this?
I would really appreciate you explaining it to me!
Thanks
Alice
Very useful information and easy to follow. Thank you very much.
Sumit,
Thank you very much for this tutorial. However I have a question not covered by those answered above. I would like to have the same ability in two columns. When I change your code from $C$2 to column. “38” I also want the same ability in column 40. I tried Columns. (“38 & 40”) in the command, however neither column will allow multiple drop down selection. Yours is the very first macro I’ve worked with so I know syntax is crucial. Can you offer any help.
Thank you
Mike McClure
Can I modify multiple selection code (no repetition) when Target.Column = 6 so that the header is not affected? Currently, the Column F header is following the “separated by a comma” rule with any edits to the header text in Cell $F$1.
Hello and thank you for this amazing presentation! One question- how would i modify this to do multi-selects in multiple columns? For example, if i have a set of drop downs in columns B,C, D with unique selections, how do i modify the code to capture this functionality for multiple columns? best.
Is it possible to apply this code to an entire column?
Hi! I was wondering the same with specific columns and i did it like this and it worked, hope you can get some help of this: If Target.Column= 2 or Target.Column = 4 Then
How can you edit the text in the cell once selected? I tried to remove a line of text and i get an error each time.
When the sheet is protected then the ability to add more than one selection. For example, I can select one item and it will replace whatever previous item was there instead of adding it to a comma-separated list.
Thoughts?
So, I just found this page, and am trying to get this to work. Currently, my drop down list text is located in Sheet 2, but the drop down box itself is in Sheet 1. How would I get this to work?
This is great, works well, many thanks
sir how can i create 2 rules (multiple dropdown selection) in two or more different column?
Hello, Sir, I have tried this option for my excel report for SQL database parameters to pass multiple values for one parameter but my report showing blank when I select multiple values .is there any option for this please suggest
Thanks a lot, very clear, good programmer
Real well explained, I finally found my solution. thank you very much for explaininf and giving simple code for each cases
Thank you so, so, so much! This helped me a lot with some work related issues!
Hi,
Can I remove existing selection by clicking on the same value in drop down list.
The spreadsheet works when I save however this is contained within a shared folder so when my colleague accesses the code no longer works. How do I fix this?
Thank you Sumit Bansal. Question: I have another column in the same sheet I would like to put a multi select drop down list into. How can I do this? I have tried changing the Oldvalue & Newvalue to Oldvalue 4 and Newvalue4 for the additional column but it does not work. Do i need to set a range in this line “If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then” ?
Thank you
I tried emailing my spreadsheet with the added code but when they get the file the code in not there. what happens to the code when I email the excel file to someone?
Why can’t colleagues see the multiple selections in a drop down list? It’s the same document on a shared server.
As of now, the multiple selections are separated by a comma. How can I change this to separate and show in in different columns?
Awesome Man!!!!!
Hi. Thank you for this guidance it is excellent and very easy to follow. I have read all of the comments and there are many unanswered questions about subsequent filtering of data using the multi select cell information. I have encountered this issue too. Can you help? Is there a way around this? Basically I have multiple filters informed by the drop down. Where a single selection is made the filter finds it. Where multi selection is made the filter cannot since there is no exact representative value in the data (e.g. Multi select shows East, South but the data column contains East). I guess I need the filter to recognise and search for each individual selection separately. Is this possible? Would be extremely grateful for advice. Thanks in advance.
Hi,
What if the target at different worksheet?
Tq
Thank you so much! I was using the macro way which is 10x complicated than this. This is a genius method.
Thank you so much! Your posts have got me out of sticky situations more than once!
OOPS.. had to fix the if statement to handle rows higher than 9
If Target.EntireColumn.Address = “$G:$G” And Mid(Target.Address, 4, Len(Target.Address) – 3) > 1 Then
this is EXACTLY what I was looking for, thank you!
My contribution to this: For those looking to apply the multi-select functionality to a range of cells… just replace the IF statement with this:
(assuming you want this validation rule to apply to the range of cells c2 = c10)
If Left(Target.Address, 2) = “$C” And Mid(Target.Address, 4, 1) > 1 And Mid(Target.Address, 4, 1) < 11Then
I just tested this out successfully in my spreadsheet
oops – had to fix for rows past row 9, also modified to col G
If Target.EntireColumn.Address = “$G:$G” And Mid(Target.Address, 4, Len(Target.Address) – 3) > 1 Then
I am going to apply your code but would like to thank you first!
Hi Charles, I am getting a syntax error when using this
Btw, be aware of the double quote!
If you want to apply this for all data vallidation on the sheet then ?
Amit see my answer above ‘multi-select functionality
to a range of cells’
You have made my day with all of your “How To” directions on this site! Allowing for multiple selections from a drop down box is great! Is there a way to make this a bit easier by making all of my selections at once?
How can i delete items from the list?in the above program if i want modify then i have to delete everything and select once again is there any method to just delete required items?
This is excellent and really useful. I have one question though.. if I select multiple items from drop down and want to get back again to single or lesser item? How do we do that?
Hi,
In the multiple drop down I need each item selected to be numbered as (1), (2) etc..
Can someone help me by providing the VBA code for the same
I need to create multiple drop-downs in a range of cells example m4:m5000 . How can I do this?
see my comment on ‘multi-select on a range of cells’
Q: if I wanted to run a Countif function would it recognize each selection separately or think it was one long value?
Hello, Could you please help me if I want to one dropdown in column 4 another in column 5, another in 6 and so on.
Please be kind to help
Best Regards