How to create multi-select drop down list in Excel (2024)

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 create multi-select drop down list in Excel (1)

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.

How to create multi-select drop down list in Excel (2)

The result will be an expandable automatically updatable drop-down list that only allows selecting one item.

For more information, please see 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).

How to create multi-select drop down list in Excel (3)

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:

Option ExplicitPrivate Sub Worksheet_Change(ByVal Destination As Range)Dim DelimiterType As StringDim rngDropdown As RangeDim oldValue As StringDim newValue As StringDelimiterType = ", "If Destination.Count > 1 Then Exit SubOn Error Resume NextSet rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)On Error GoTo exitErrorIf rngDropdown Is Nothing Then GoTo exitErrorIf Intersect(Destination, rngDropdown) Is Nothing Then 'do nothingElse Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue = "" Then 'do nothing Else If newValue = "" Then 'do nothing Else Destination.Value = oldValue & DelimiterType & newValue ' add new value with delimiter End If End IfEnd IfexitError: Application.EnableEvents = TrueEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)End Sub

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).

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?

Option ExplicitPrivate Sub Worksheet_Change(ByVal Destination As Range)Dim rngDropdown As RangeDim oldValue As StringDim newValue As StringDim DelimiterType As StringDelimiterType = ", "If Destination.Count > 1 Then Exit SubOn Error Resume NextSet rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)On Error GoTo exitErrorIf rngDropdown Is Nothing Then GoTo exitErrorIf Intersect(Destination, rngDropdown) Is Nothing Then 'do nothingElse Application.EnableEvents = False newValue = Destination.Value Application.Undo oldValue = Destination.Value Destination.Value = newValue If oldValue <> "" Then If newValue <> "" Then If oldValue = newValue Or _ InStr(1, oldValue, DelimiterType & newValue) Or _ InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then Destination.Value = oldValue Else Destination.Value = oldValue & DelimiterType & newValue End If End If End IfEnd IfexitError: Application.EnableEvents = TrueEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)End Sub

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.

Option ExplicitPrivate Sub Worksheet_Change(ByVal Destination As Range)Dim rngDropdown As RangeDim oldValue As StringDim newValue As StringDim DelimiterType As StringDelimiterType = ", "Dim DelimiterCount As IntegerDim TargetType As IntegerDim i As IntegerIf Destination.Count > 1 Then Exit SubOn Error Resume NextSet rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)On Error GoTo exitErrorIf rngDropdown Is Nothing Then GoTo exitErrorTargetType = 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) Then oldValue = Replace(oldValue, newValue, "") ' removes an existing value from the list when you select it again Destination.Value = oldValue 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 comma and space 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 comma and space as first characters Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1) End If If InStr(1, Destination.Value, ",") = 1 Then ' remove comma as first character 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 comma 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 IfexitError: Application.EnableEvents = TrueEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)End Sub

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:

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)