How to create drop down list with multiple selections or values in Excel? (2023)

By default, you can only select one item in a data validation drop-down list in Excel. How can you make multiple selections in the drop-down list as below gif shown? The methods in this article can help you solve the problem.

Create drop down list with multiple selections with VBA code

This section provides two VBA codes to help you make multiple selections in drop-down lists in a worksheet. The two VBA codes can achieve:

You can apply one of the following VBA codes to make multiple selections in a drop-down list in a worksheet in Excel. Please do as follows.

1. Open the worksheet contains the data validation drop-down lists from which you want to make multiple selections. Right click the sheet tab and select View Code from the context menu.

2. In the Microsoft Visual Basic for Applications window, copy the below VBA code into the code window. See screenshot:

VBA code 1: Allow multiple selections in a drop-down list without duplicates

Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 2019/11/13 Dim xRng As Range Dim xValue1 As String Dim xValue2 As String If Target.Count > 1 Then Exit Sub On Error Resume Next Set xRng = Cells.SpecialCells(xlCellTypeAllValidation) If xRng Is Nothing Then Exit Sub Application.EnableEvents = False If Not Application.Intersect(Target, xRng) Is Nothing Then xValue2 = Target.Value Application.Undo xValue1 = Target.Value Target.Value = xValue2 If xValue1 <> "" Then If xValue2 <> "" Then If xValue1 = xValue2 Or _ InStr(1, xValue1, ", " & xValue2) Or _ InStr(1, xValue1, xValue2 & ",") Then Target.Value = xValue1 Else Target.Value = xValue1 & ", " & xValue2 End If End If End If End If Application.EnableEvents = TrueEnd Sub

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

Now you can select multiple items from the drop-down list in the current worksheet.

Supposing you have selected an item accidently and need to remove it without clearing the whole cell and starting over. The following VBA code 2 can do you a favor.

VBA code 2: Allow multiple selections in a drop-down list without duplicates (delete existing items by selecting it again)

Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 2023/01/11 'Updated by Ken Gardner 2022/07/11 Dim xRng As Range Dim xValue1 As String Dim xValue2 As String Dim semiColonCnt As Integer Dim xType As Integer If Target.Count > 1 Then Exit Sub On Error Resume Next xType = 0 xType = Target.Validation.Type If xType = 3 Then Application.ScreenUpdating = False Application.EnableEvents = False xValue2 = Target.Value Application.Undo xValue1 = Target.Value Target.Value = xValue2 If xValue1 <> "" Then If xValue2 <> "" Then If xValue1 = xValue2 Or xValue1 = xValue2 & ";" Or xValue1 = xValue2 & "; " Then ' leave the value if only one in list xValue1 = Replace(xValue1, "; ", "") xValue1 = Replace(xValue1, ";", "") Target.Value = xValue1 ElseIf InStr(1, xValue1, "; " & xValue2) Then xValue1 = Replace(xValue1, xValue2, "") ' removes existing value from the list on repeat selection Target.Value = xValue1 ElseIf InStr(1, xValue1, xValue2 & ";") Then xValue1 = Replace(xValue1, xValue2, "") Target.Value = xValue1 Else Target.Value = xValue1 & "; " & xValue2 End If Target.Value = Replace(Target.Value, ";;", ";") Target.Value = Replace(Target.Value, "; ;", ";") If Target.Value <> "" Then If Right(Target.Value, 2) = "; " Then Target.Value = Left(Target.Value, Len(Target.Value) - 2) End If End If If InStr(1, Target.Value, "; ") = 1 Then ' check for ; as first character and remove it Target.Value = Replace(Target.Value, "; ", "", 1, 1) End If If InStr(1, Target.Value, ";") = 1 Then Target.Value = Replace(Target.Value, ";", "", 1, 1) End If semiColonCnt = 0 For i = 1 To Len(Target.Value) If InStr(i, Target.Value, ";") Then semiColonCnt = semiColonCnt + 1 End If Next i If semiColonCnt = 1 Then ' remove ; if last character Target.Value = Replace(Target.Value, "; ", "") Target.Value = Replace(Target.Value, ";", "") End If End If End If Application.EnableEvents = True Application.ScreenUpdating = True End IfEnd Sub

Notes:

Easily create drop down list with multiple selections with an amazing tool

Here we highly recommend the Multi-select Drop-down List feature of Kutools for Excel for you. With this feature, you can easily select multiple items from the drop-down list in a specified range, current worksheet, current workbook or all opened workbooks as you need.

1. Click Kutools > Drop-down List > Multi-select Drop-down List > Settings. See screenshot:

2. In the Multi-select Drop-down List Settings dialog box, please configure as follows.

• 2.1) Specify the applying scope in the Apply to section. In this case, I select Current worksheet from the Specified Scope drop-down list;
• 2.2) In the Text Direction section, select an text direction based on your needs;
• 2.3) In the Separator box, enter a delimiter which you will use to separate the multiple values;
• 2.4) Check the Do not add duplicates box in the Options section if you don’t want to make duplicates in drop-down list cells;
• 2.5) Click the OK button. See screenshot:

3. Please click Kutools > Drop-down List > Multi-select Drop-down List to enable the feature.

Now you can select multiple items from the drop-down list in current worksheet or any scope you have specified in step 2.

If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Related articles: