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:
How can you correct a mistakenly added item from the drop down menu. If you were to choose multiple items and then want to remove one later, it is not allowed. Please advice
Thanks So much, this was much easier than adding a checkbox for selection
I have a table in Sheet named Abst. Range A Q 2 4 to A R 4 7
Similar to Following Table:-
…. A Q ……..AR
24…Blank….Blank
25…A……….B
26…C……….D
27…E……….F
etc. All Values are Text.
In column I & J I have multi select boxes. In Column ” I ” Drop Down List from Table A Q 24 to A Q 47 While in Column ” J ” from table AR 24 to AR 47. These boxes give comma separated values.
I want in column J to select corresponding automatic comma separated values as per the table. Such as in above example:-
If I select In column” I ” A,C,E
The column ” J ” should give B,D,F automatically
Can you suggest me the proper code ?
Thank you.
Prakash Kulkarni
Hello, this question was asked below but there was no response to it, how do you use this code in a column in multiple cells and across multiple tabs?
I entered the VIB Code, but it doesn’t work.
How do I clear contents if they made a mistake or the item selected has changed?
thanks for the code, work great !!
how do I eliminate the data validation error? I can click the error message to ignore the error, but I would like to get rid of it
Hi Keith, were you able to finally get rid of the yellow triangle with an exclamation on the top left corner of the cell? I am assuming that was what you were also getting. Please let me know. Thanks.
Nevermind. I got it sorted by converting it from Table to Convert to Range.
I am trying to get the multiple selections without repeat to work in two columns next to each other. Each has their own selection list. When I do one column it seems to stop the code from the other and vice versa. Not sure, but somehow I am overriding the one with the other. Any thoughts on what I am doing wrong?
This is great, thank you. Once I have multiple selections the filter function doesn’t seem to work – I want to be able to filter by one of the selections and see any line that contains the selection
Hi,
The is working nice, but I try to make a vlookup based upon its but it not work can you help me
Thanks for the multiple selection tip, the problem I has is that I would like to have multiple selection for columns 2,3,4,5,6,7,8,9 and I have tried to amend with no luck so far. Help appreciated please.
Thank you Sumit for your instructions on multiple selections from an Excel drop-down menu without repetitions. I however found it funny that soon after protecting the sheet this function ceased working and went back to default of selecting just one item. What could be the reason?
Hi,
I used your codes for the multiple selection dropdown list without repetition and I can’t get it to work on my Data Entry form combo box. I’m thinking it’s because I’m not referencing the right location of the list which is not the cell it self but the combo box. Do you have solution for this? I appreciate all the help I can get….Thanks!
I’m using this so to reference engineer experience against applications. I have a list of global applications in the business, then a list of engineers. I’ve created three columns ‘Basic, Intermediate and Advanced’ and the engineer can place their names in the relevant columns. However, if an engineer has basic skills on one of the applications, then increases his knowledge and becomes Intermediate, how do I get it to remove him automatically from the Basic column if he selects his name under Intermediate?
I want c2 cell is depend b2 so that I will use indirect formula or any please suggest me
amazing ! you really save my life with that code we can not thanks you enough ,thank you millions and more !
Thank you so much for this article!
I will try my best to explain the issue I am encountering. I don’t know if I have all the correct technical names.
I have successfully created dropdown lists which allow for multiple selections without repetition for three columns. These dropdown lists are within a table for which I would like to be able to sort and refine searches. Is there a way for criteria to be recognized individually in the table rather that a concatenated value for all the possible variations? Again, thank you for your this article, it has been most helpful!
Amazing – thank you for this thread.
Question: if you want to amend/remove a selection you need to remove and start again. Any way when you select the same again, it simply removes that selection?
NOOOOOO! I got it to work, but then it stopped for some reason! I’ve tried everything! (rebooting, starting over, downloading the sample and adding my information there) I cannot get it to work again! On any excel document. I’m not even sure what to do now!
Oops! I meant CAN’T! 🙂
I need to create a multiple selection drop down list for an entire column–except the first few cells. Do I have to add the thousands of cells to the formula one by one or is there a short-cut? Obviously, I can add all of them… Thanks!
This is really cool code. If I specify a certain cell as the target address (can’t do column because there are other cells in the same column where I can only allow one option to be chosen), is there any way to use offset or relative references so that the target moves accordingly if, for example, I insert rows above the cell that is being referenced in the code? Thanks in advance!
When I selected the the wrong entry from the drop down, it doesn’t give me the option to delete it. I am receiving an error message: “The value you entered is not from the required list.”. How do I get this fix?
delete the cell & again select
This has been fantastic for me. One thing I’ve found is when I want to manually delete a selection using the backspace key it will instead delete the value I wanted to keep and insert the value I wanted to remove. Is there a way to have the backspace key actually remove the value you want deleted? I have the code for removing an item by selecting it a second time from the drop down list
Hi Amanda! I was looking for this code but couldn’t find it anywhere, would you mind sharing it with me? I would really appreciate it.
Hope this is what you’re looking for.
If Target.Column = 4 Or Target.Column = 6 Then
If oldVal = “” Or newVal = “” Then
‘Do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) – Len(newVal) – 1)
Else
Target.Value = Replace(oldVal, newVal & Chr(10), “”)
‘Allows multiple items to be selected and to deselect
End If
Else
Target.Value = oldVal & Chr(10) & newVal
Hi Amanda,
Should this be in addition to the above code or replace the entire code and use this one?
Great tip, thank you.
I am using the VBA code without repetition. What code can I use to deselect a selected item when chosen again. Right now, I can’t manually delete a selected item without getting an error code. I have to start over.
Hi Lex!
I have the same issue. I was wondering if you got the fix for this that you wouldn’t mind sharing.
Damn – What a great tip, thank you so much for sharing, not only the primary code but the options you offered in the Q&A. Truly awesome
How we can de-select the already selected item from the cell. Do I need to remove all and re-do the selection ? Can we un-select the few one from the list?
I need to remove a entity by double selecting the option. How is that possible?
I cannot get this code to work. Even when I download the file directly from this site, the cells in the downloaded file will not allow multiple selections. Any ideas?
I have a need to allow my users to type in a value for “other”. If I do this with this formula, it appends what ever was input first, i.e. “one, two, one, two, other”. I’m sure there is something easy to prevent this. Thanks for the help and the advice!
Hi,
How to do the same in Google sheets?
Hi, let say we have another column that we need to do drop down but with another list. so in the excel sheet we have now 2 columns where they have drop down list. how can we add the second to the first VBA code?
hi, first of all, thank you very much for your teaching and it is very helpful… here is my question
let’s say I have input the following data in
C2 Two, Three
C3 Three, Four
C4 One, Four
C5 Two
then I discover I cannot count the data by “Filter”
what I can do so that I can count out :
One x 1
Two x 2
Three x 2
Four x 2
thank you very much for your help !!!!
ruby from Japan
I have a mutliple drop down list in cell C3, for example, dependent on criteria entered in C1. When the value in C1 changes, how can I ensure C3 resets to blank and does not leave the old values that were relevant to the old value entered in C1?
Hello hello hello. How do I apply this code to the whole row?
Okay, this is super cool! Thank you for all the added code to make each selection show up on a separate line, and work in a protected file. Having the step by step instructions and the code was very useful!
I have this code on my worksheet but when I go to another worksheet within the same workbook with the code (to allow multi-select) it turns the multi-select off on all the applicable tabs. If I save and come back in it turns it on again but when I click on another tab with the multi-select code it goes away again. HELP 🙂
After finish and save it but next day I open that file ,the link is not working ,means drop down list is working but related drop down list and vlookup link is not working ,equation link also.
Thank you! This was so great and it worked perfectly! I have one question: If you selected One, Two, Three, but want to take the Three out, and maybe even go further and select Four now, is there a way to do that? or would you just press delete and start over with Selecting?
Thanks alot, very handy code.
How can we create multiple selection in 2 columns with dependant values. Eg. In column 1 if we have country names and column 2 states. Then in column 1 we can select 2 countries and then column 2 shows state list only for those 2 selected countries and then we can select multiple states from this drop-down list which showed dependant values, i.e. State names for those 2 countries.
I am facing issue where in after selecting 2 countries, the state column is not showing the list of states for these 2 selected countries.
Great code! Does anyone know if you can count items when you have multiple items in a single cell? For example, in cell B2, I have apples, carrots, and bananas selected (apples, carrots, bananas) and cell B3 has banana, grape, and watermelon (banan, grape, watermelon). How would I be able to count the number of times the word banana is seen in the cell range B2:B3?
use below code. It basically counts no of commas and add 1 to it. so indirectly it counts no of items
=(LEN(B6)-LEN(SUBSTITUTE(B6,”,”,””))+1)
replace B6 by cell which u want to count
I have a related question! How would I count the number of dropdown selections per cell? In the example above, I’d want B2’s total number of items (apples, carrots, and bananas- so, 3 items) and B3’s total (banana, grape, watermelon- 3 items). Thanks for the help!!
Thank you! This was great and solved my issue.
is it possible to do any data validation from the multiple selection drop downs ?
Once I have values selected and now want to remove one. What do I do?
Thanks for the great code.
Is it working??
What if I want multiple drop downs in a row and columns? I want to repeat the same row of options line after line, but only the first line is adding options. The rest are single choice only. Thanks!