by The article shows how to create an Excel drop down that allows users to select multiple items with or without duplicates.
Excel has come a long way since its inception and introduces more and more useful features with each new release. In Excel 365, they've added the ability to search within data validation lists, which is a huge time-saver when working with large sets of data. However, even with this new option, out-of-the-box Excel still only allows selecting one item from a predefined list of options. But fear not, as there is a solution. By using VBA, you can create drop-down lists with multiple selections. With the ability to prevent duplicates and remove incorrect items, this feature can streamline data input and improve accuracy in your Excel spreadsheets.
How to make Excel drop down with multiple selections
Creating a multi-select drop down list in Excel is a two-part process:
- First, you make a regular data validation list in one or more cells.
- And then, insert the VBA code at the back end of the target worksheet.
It also works in the reverse order :)
Create a normal drop-down list
To insert a drop down list in Excel, you use the Data Validation feature. The steps slightly vary depending on whether the source items are in a regular range, named range, or an Excel table.
From my experience, the best option is to create a data validation list from a table. As Excel tables are dynamic by nature, a related dropdown will expand or contract automatically as you add or remove items to/from the table.
For this example, we are going to use the table with the plain name Table1, which resides in A2:A25 in the screenshot below. To make a picklist from this table, the steps are:
- Select one or more cells for your dropdown (D3:D7 in our case).
- On the Data tab, in the Data Tools group, click Data Validation.
- In the Allow drop-down box, select List.
- In the Source box, enter the formula that indirectly refers to Table1's column named Items.
=INDIRECT("Table1[Items]")
- When done, click OK.
The result will be an expandable and automatically updatable drop-down list that only allows selecting one item.
Tip. If the method described above is not suitable for you for some reason, you can create a dropdown from a regular range or named range. The detailed instructions are here: How to create Excel data validation list.
Insert VBA code to allow multiple selections
This is the core part of the process that does the magic. To turn a regular single-selection picklist into a multi-select dropdown, you need to insert one of these codes in the back end of your target worksheet:
- VBA code for multi-select drop down with duplicates
- VBA code for multi-select drop down without duplicates
- VBA code for multi-selection dropdown with item removal
To add VBA code to your worksheet, follow these steps:
- Open the Visual Basic Editor by pressing Alt + F11 or clicking the Developer tab > Visual Basic. If you don't have this tab on your Excel ribbon, see how to add Developer tab.
- In the Project Explorer pane at the left, double-click on the name of the worksheet that contains your drop-down list. This will open the Code window for that sheet.
Or you can right-click the sheet's tab and choose View Code from the context menu. This will open the Code window for a given sheet straight away.
- In the Code window, paste the VBA code.
- Close the VB Editor and save your file as a Macro-Enabled Workbook (.xlsm).
That's it! When you go back to the worksheet, your drop-down list will allow you to select multiple items:
VBA code to select multiple items in dropdown list
Below is the code to make a data validation list that allows selecting multiple items, including repeated selections:
How this code works:
- The code enables multiple selections in all drop down lists on a particular sheet. You do not need to specify the target cell or range reference in the code.
- The code is worksheet specific, so be sure to add it to each sheet where you want to allow multiple selections in drop down lists.
- This code allows repetition, i.e. selecting the same item several times.
- The selected items are separated with a comma and a space. To change the delimiter, replace ", " with the character you want in DelimiterType = ", " (line 7 in the code above).
Note. The same character cannot be used as both the delimiter and part of the dropdown items. In our code, the default delimiter is a comma followed by a space (", "), so this combination of characters should not appear anywhere within the dropdown items themselves to avoid conflicts. If you change the delimiter to a single space (" "), bear in mind that the code will only function correctly for single-word items, failing to handle multi-word items containing spaces.
Excel multi-select dropdown without duplicates
When selecting from a large list, users can sometimes pick the same item more than once without noticing. The code below solves the problem of duplicates in a multi-selection data validation drop down list. It lets users pick a particular item only once. If you try to select the same item again, nothing will happen. Pretty cool, right?
Multi-selection dropdown with item removal
When users need to select multiple options but can make mistakes or change their minds during the selection process, a multi selection dropdown that allows for the removal of incorrect items can be a lifesaver.
Consider a scenario where you need to assign multiple tasks to team members using a drop-down list. With Excel's default functionality, the only way to remove an incorrectly assigned task is by clearing the entire contents of the cell and starting over. With the ability to remove individual items from the selection, the team can effortlessly modify task assignments without confusion or errors.
The code below implements the item removal functionality in a simple and intuitive way: the first click on an item adds it to the selection, and a second click on the same item removes it from the selection.
The below demo highlights how the multi selection dropdown with removal functionality works in Excel. The users can select multiple options from the data validation list and make adjustments on the fly. A streamlined and effective approach to managing selections!
How to make a multiple selection dropdown with custom delimiter
The character that separates items in the selection is set in the DelimiterType parameter. In all the codes, the default value of this parameter is ", " (a comma and a space) and it is located in line 7. To use a different separator, you can replace ", " with the desired character. For instance:
- To separate the selected items with a space, use DelimiterType = " ".
- To separate with a semicolon, use DelimiterType = "; " or DelimiterType = ";" (with or without a space, respectively).
- To separate with a vertical bar, use DelimiterType = " | ".
For example, if you change the delimiter to a vertical slash, your multi-select picklist will look as follows:
How to create dropdown with multiple selections in separate lines
To get each selection in a separate line in the same cell, set DelimiterType to Vbcrlf. In VBA, it is a constant for the carriage return and line feed characters.
More precisely, you change this code line:
DelimiterType = ","
to this one:
DelimiterType = vbCrLf
As a result, each item that you select from the dropdown list will appear in a new line:
How to create multi-select dropdown for specific columns, rows, cells and ranges
All the codes described in this tutorial work across an entire sheet. However, you can easily modify any of the codes, so it only applies to specific cells, rows, or columns as needed. For this, find this line of code:
If rngDropdown Is Nothing Then GoTo exitError
Add immediately after it, add a new line specifying where to allow multiple selections, as explained in the below examples.
Multi-select drop-down for specific columns
To allow selecting multiple items in a certain column, add this code:
If Not Destination.Column = 4 Then GoTo exitError
Where "4" is the number of the target column. In this case, the multi-select dropdown will be only enabled in column D. In all other columns, dropdown lists will be limited to a single selection.
To target several columns, specify their numbers using this code:
If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError
In this case, the multi-select dropdown will be available in columns D (4) and F (6).
Multi-selection dropdown for certain rows
To insert multiple drop-downs in a specific row, use this code:
If Not Destination.Row = 3 Then GoTo exitError
In this example, replace "3" with the row number where you want to enable multi-select dropdowns.
To target multiple rows, the code is as follows:
If Destination.Row <> 3 And Destination.Row <> 5 Then GoTo exitError
Where "3" and "5" are the rows where selecting multiple items is allowed.
Multiple selections in specific cells
To enable multiple selections in particular cells, add one of the below code lines.
For a single cell:
If Not Destination.Address = "$D$3" Then GoTo exitError
For multiple cells:
If Destination.Address <> "$D$3" And Destination.Address <> "$F$6" Then GoTo exitError
Just remember to replace "$D$3" and "$F$6" with the addresses of your target cells.
Multi-select drop-down for specific range
To limit your multi-select dropdown to a particular range, replace this line of code:
If Intersect(Destination, rngDropdown) Is Nothing Then
with this one:
If Intersect(ActiveCell, Range("C3:D10")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
The range of interest is specified directly in the code (C3:D10 in the above example). This modification offers a more efficient approach to handing ranges - instead of individually listing 16 cells, you use a single range reference.
How to enable multi-selection functionality in protected sheet
To enable a multi-select dropdown functionality in a protected worksheet, simply insert the following code into the sheet where you've added the primary code. This "protected sheet" code should be added after the main code.
Before adding this code to your worksheet, remember to replace "password" with the actual password you used to protect the sheet. And this is the only change that needs to be made. The code will automatically detect the presence of a dropdown list in a given cell and allow editing of that cell. In all other cells, editing will remain restricted.
Note. Please be aware that including your real password in the code could lead to a security risk. To ensure the safety of your workbook, store it in a secure location that is protected against unauthorized access or use.
So, there you have it - an Excel dropdown list with multiple selections. Adding this awesome feature to your spreadsheets will increase the accuracy of your data input and help you get your work done faster!
Practice workbook for download
Multi-selection dropdown - examples (.xlsm file)
622 comments
Hi, I tried making the Multi-selection dropdown with item removal and it works only for some items on my list. It looks like it doesn't work for items with '&' in it. Is there a way to fix that?
Hi! Unfortunately, I have not been able to reproduce your problem. The '&' symbol has no effect on the workings of the code.
Hi! I need to do the multiple drop down in multiple columns. I have tried adding another range line
If Intersect(ActiveCell, Range("D4:D79")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
or adding another section of code (copying the code again but changing the range it affects) but this does not work. Is there a way to do more than one in an excel sheet?
Hi! Use the Union function to check whether the active cell is in one of the ranges.
For example:
If Intersect(ActiveCell, Union(Range("C3:D10"), Range("C15:D17"))) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
I hope it’ll be helpful.
Thank you! This solved the issue
Hello Alexander, I hope you are well. Great tutorial, thank you for sharing.
Can you show a modified version of the code to only allow multiple selection for a selection of dropdowns over a certain cell range please?
Grateful for your help with this - thank you.
Kind regards,
Richard.
Hi! Please read the following paragraph in the article above very carefully: Multi-select dropdown for specific columns, rows, cells and ranges.
Thank you for your quick reply Alexander, I appreciate that. Clearly I need to get my eyes tested lol
Is there a way to add the code over every sheet in a workbook without having to do each sheet individually, so as you add sheets, they already have the code?
Hi! Unfortunately, Excel doesn't work that way.
Codes are working fine but for protected sheet I am able to make a single entry only. Multiple entry is not possible with it when sheets are protected. I have applied your code for password protected sheet but it is not working.
Hi! You can read all the possible solutions to your problem in the comments below.
The multiselect with removal does not remove the first entry. It will remove all other entries but does not return to blank.
Hi! You can clear a cell with the Del key.
Hello, Is there a way to ensure your drop down list is always in alphabetical order and not click order?
Hi! A drop-down list creates a text string in a cell. You need a special macro to sort the words in this text string.
I sort the source to be A-Z, then drop down list appear in A-Z order as well.
How do I make a field on the drop down where they can enter text as and "other" option?
Hi! You can edit text in a cell with a drop-down list in the same way as a regular cell with text. You can also create a Editable drop down list.
I'm trying to use your code to enable multi-selection functionality in protected sheet, but I don't know where to put this code in the VBA (I'm also using your code for "Multi-selection dropdown with item removal") Can you please provide me with the exact location where this code should be placed?
Hi! This has been discussed many times in the comments of this article. Add the code after the "Multiple selection drop down with item removal" code or whatever code you use.
Thanks so much for your quick response! I went through the old comment around this topic but doesn't seem like they received any solutions. I did try put the code for "Enable multi-selection functionality in protected sheet" after my main code "Multi-selection dropdown with item removal". it's still doesn't work. It's giving me single select of the latest one I selected. Please see below my code and any help will be greatly appreciated.
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = vbCrLf
Dim DelimiterCount As Integer
Dim TargetType As Integer
Dim i As Integer
Dim arr() As String
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Destination.Column 8 And Destination.Column 10 And Destination.Column 12 And Destination.Column 14 And Destination.Column 16 And Destination.Column 18 And Destination.Column 20 And Destination.Column 22 And Destination.Column 24 And Destination.Column 26 Then GoTo exitError
TargetType = 0
TargetType = Destination.Validation.Type
If TargetType = 3 Then ' is validation type is "list"
Application.ScreenUpdating = False
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue "" Then
If newValue "" Then
If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list
oldValue = Replace(oldValue, DelimiterType, "")
oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")
Destination.Value = oldValue
ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, newValue & DelimiterType) Or InStr(1, oldValue, DelimiterType & newValue & DelimiterType) Then
arr = Split(oldValue, DelimiterType)
If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then
Destination.Value = oldValue & DelimiterType & newValue
Else:
Destination.Value = ""
For i = 0 To UBound(arr)
If arr(i) newValue Then
Destination.Value = Destination.Value & arr(i) & DelimiterType
End If
Next i
Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType))
End If
ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
oldValue = Replace(oldValue, newValue, "")
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces
Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", ""))
If Destination.Value "" Then
If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end
Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2)
End If
End If
If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters
Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1)
End If
If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1)
End If
DelimiterCount = 0
For i = 1 To Len(Destination.Value)
If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then
DelimiterCount = DelimiterCount + 1
End If
Next i
If DelimiterCount = 1 Then ' remove delimiter if last character
Destination.Value = Replace(Destination.Value, DelimiterType, "")
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "")
End If
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="DVonly"
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect Password:="DVonly"
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect Password:="DVonly"
End Sub
Hi! There are no errors in the code. But I can't look in your workbook and your Excel to find the cause of the problem. Try installing the code in a new workbook or downloading the sample file from the link at the end of the article.
I created a brand new file and copied the Multi-selection dropdown with item removal code and added If Not Destination.Column = 4 Then GoTo exitError before the "TargetType = 0" line. This worked as expected. I added the How to enable multi-selection functionality in protected sheet code at the end. The new file has "password" as the password. When I click on the drop down, the file changes to the sheet with the Table for the drop down and changes the Table to a list, not adding the second selection to the drop down.
Hi! Unfortunately, I did not have a replication of your problem in my workbook. Please note that the code to enable the multi-select functionality in the protected sheet should be added at the end of your code, after the main code.
Is there any way to apply multiple dropdown feature on web excel ? Like on "OneDrive"
Hi! We have answered this question many times before. For example, here.
An honestly wonderful bit of code you put together. I am curious if it is possible to limit the selection to a particular range? I have tried adding each cell of the range, but the list gets excessively long (B11:C50). Neither column nor row limitation work perfectly as it negatively impacts other parts of the worksheet.
Hopefully I am overlooking something simple. Regardless, thanks for your VBA (Very Beautiful Algorithm).
Hello! Replace the following line of code to restrict the execution of the multiple selections macro to a range of cells
If Intersect(Destination, rngDropdown) Is Nothing Then
на
If Intersect(ActiveCell, Range("B11:C50")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
I hope my advice will help you solve your task.
Hi, this is great.
I used your code for Multi-selection dropdown with item removal for a specific cell option and got it working as I wanted. However, for some reason, it broke some of the macros I had in that worksheet. All the sudden my macros started giving me "variable not defined error" in multiple instances. Deleting your code fixed the issue.
I have your code at the very top in Sheet1 followed by over 20 macros triggered by buttons
After spending some time digging for answers the problem relies in the fact that your code requires the "option explicit" line. This forces me to define every single variable in my macros.
is there any workaround other than defining every single variable in my >20 macros?
Any suggestion is welcome.
Thanks
Ok found a workaround (i think). i have to cut and paste the macros from the buttons into separate modules and then assign those modules back to the buttons.
Cheers
Hi! If the "option explicit" option appears in the module, you must explicitly declare all variables using Dim, Private, Public, ReDim, or Static instructions. You can try removing this option, but I don't guarantee the code will work correctly.
Is there a way to show the dropdown symbol in excel cell using VBA (for multi selection dropdown handled thru VBA code)?
Hi! Excel has a special method of identification for cells with a drop-down list. The code for multiple selections works in all cells with a drop-down list. Unless you specify a specific range. You could try writing VBA code to get what you want, but we don't provide that help on this blog.
Thanks for the instructions! Now I want to send this Excel file to colleagues. Tried it, and from some email addresses it bounced back. I am presuming because of the macros in the Excel file which could be seen as a security risk. I tried creating a xlsx, but it loses the multiselection functionality. Is there any way to preserve the multiselection functionality in a document format that won't be perceived as a security risk?
Hi! Unfortunately, there is no other option in Excel. You can try sending the file as an archive. Maybe this article will be helpful: How to enable and disable macros in Excel.
Brilliant work!
(One other tutorial I looked at required specifying the call with the drop-down. Ugh.)
Thank you, Alexander.
-- Jon
Thanks for this tutorial. It was very helpful.
I have created a pivot a table and I will like my multi-selections to appear individually as column headers. Can this be achieved?
Hi! Multiple selection macros only work for the drop down lists you have created.
I simply cannot discern where, within the code that appears in the "VBA code to select multiple items in dropdown list" table, that the suggested code for introducing column flexibility (below) should be inserted! Please advise. Thanks.
Multi-select drop-down for specific columns
To allow selecting multiple items in a certain column, add this code:
If Not Destination.Column = 4 Then GoTo exitError
Hi! Pay attention above to the beginning of paragraph: How to create multi-select dropdown for specific columns, rows, cells. It says where to insert this code.
Hello, thank you for this useful website.
I have questions please,
I tried making a drop down with multiple selections for my data, but when I insert the formula =INDIRECT("Table1[Items]")
I get an error message saying (the source currently evaluate to error !)
I don't why?
Another question: I cannot see from where the name "Table1" in the above formula came?
Thank you
Hi! If I understand your task correctly, this article may be helpful: Create drop-down from Excel table.
Hi,
Thanks for the information. Its really help. I'm trying to use the code in protected sheet but fail to do so.
Questions:- How im going to make the code valid for more then one table list in one sheet. Cause i tried many times but its not gonna work on your code above. Please help me regarding this. i have use these code fo yours: Multi-selection dropdown with item removal & Enable multi-selection functionality in protected sheet at my Excell. Thanks in advance.
Hi! Unfortunately, your problem is not very clear. The code works for all dropdown lists on the sheet. If you want to limit its use, I recommend you to read carefully the paragraph: Multi-select dropdown for specific columns, rows, cells. Or explain it in more detail.
Thanks for your feedback. I already follow step by step, to enable the dropdown list, that can be use and pick multiple and its work perfectly using this code: Multi-selection dropdown with item removal. Unfortunately, after I implement the code that can Enable multi-selection functionality in protected sheet at my Excell, the dropdown list can't be select as before. it only pick one instead of more than one items in one cell. if i delete this code : Enable multi-selection functionality in protected sheet at my Excell, its back to normal. Cause i need to protect the sheet at several cell and remain unprotected at several cell to user for key in the input. If you have other solution on this issues please help. Thanks in advance. Really appreciate.
added input issue:-
- I have more than one list of dropdown in one sheet. I already try add the other list of dropdown into your practice example excel and its won't work if I add the Enable multi-selection functionality in protected sheet at my Excell, So the conclusion, its fail to pick more than one items, if the sheet content more than one dropdown list in protected sheet.
A worksheet can have any number of dropdown lists with multi-selection functionality. I cannot see what you are doing. Try to carefully follow the recommendations from the article above.
Hi! The code you are given works with a protected worksheet. This method of protection is described here: How to protect Excel sheet. This protects the entire sheet, not individual cells.
There is also a lot of discussion of this problem in the comments below. For example, here.
Thanks for reply. As i understand on your comment, it is valid for the different dropdown list (one or more set of validation) or it's only valid for one list(table) of dropdown only in one worksheet. And for your information, i already go through the other comment but there is no solid answer for the issue yet.
If you look carefully, you'll read in the second paragraph of the article: "The code enables multiple selections in all drop down lists on a particular sheet. You do not need to specify the target cell or range reference in the code".
In the example file, you can see how multiple selections works for 5 dropdown lists on one sheet.
I am not an expert in excel. Your VBA code helped me a lot. But still am facing problem with the "protection".
Can u explain where to insert the protection code along with the existing code.
I did copy, paste with the existing code, but it doesn't work. do you have some pictures or videos to share.
Thank you.
Hi! You can see a sample code in this paragraph above: How to enable multi-selection functionality in protected sheet.
Hi,
I've tried to the copy paste of the code that was recommended in the paragraph above, but it didn't work. I saw other people had the some issue. Could you please give an example of the complete code including both the Multi Selection functionality as well as the code to Unprotect and protect so we could evaluate what we're not doing properly?
Or even include this type of example in the excel spreadsheet you provided as an example?
Thank you in advance
Hi!
1. Add the code you need to the worksheet. For example, Multi-select drop down without duplicates.
2. AFTER THIS CODE, add the code for working on the protected sheet from the paragraph: Enable multi-selection functionality in protected sheet.
3. That's all.