Select multiple items in Excel drop-down list – full guide

Excel drop-down lists are a fantastic tool for ensuring data consistency and ease of entry. However, by default, they restrict you to selecting just one item. But what if you need to select multiple items from the same drop-down list? This comprehensive guide will explore methods to enable multiple selections in Excel drop-down lists, manage duplicates, set custom separators, and define the scope of these lists.

• Using VBA code
• Using Kutools for Excel in a few clicks

• Allowing duplicate items
• Removing any existing items
• Setting a custom separator
• Setting a specified range
• Executing in a protected worksheet

Enabling Multiple Selections in Drop-Down List

This section provides two methods to help you enable multiple selections in drop-down list in Excel.

Using VBA Code

To allow multiple selections in a drop-down list, you can use "Visual Basic for Applications" (VBA) in Excel. The script can modify the behavior of a drop-down list to make it a multiple-choice list. Please do as follows.

Step 1: Open the Sheet (Code) editor

1. Open the worksheet that contains the drop-down list for which you want to enable multiple selection.
2. Right click the sheet tab and select "View Code" from the context menu.
   

Step 2: Use VBA code

Now copy the following VBA code and paste it to the opening sheet (Code) window.

VBA code: Enable multiple selections in Excel drop-down list.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Result

When you return to the worksheet, the drop-down list will enable you to choose multiple options, see the demo below:

Using Kutools for Excel in a few clicks

If you're not comfortable with VBA, an easier alternative is the "Multi-select Drop-down List" feature of "Kutools for Excel". This user-friendly tool simplifies enabling multiple selections in drop-down lists, allowing you to customize the separator and manage duplicates effortlessly to meet your different needs.

After installing Kutools for Excel, go to the "Kutools" tab, select "Drop-down list" > "Multi-select Drop-down List". Then you need to configure as follows.

1. Specify the range containing the drop-down list from which you need to select multiple items.
2. Specify the separator for the selected items in the drop-down list cell.
3. Click "OK" to complete the settings.
   

Result

Now, when you click on a cell with a drop-down list in the specified range, a list box will appear next to it. Simply click the "+" button next to the items to add them to the drop-down cell, and click the "-" button to remove any items you don't want anymore. See the demo below:

More operations for multi-selection drop-down list

This section collects the different scenarios that may be required when enabling multiple selections in the Data Validation drop-down list.

Allowing duplicate items in drop-down list

Duplicates can be a problem when multiple selections are allowed in a drop-down list. The VBA code above does not allow duplicate items in the drop-down list. If you need to keep duplicate items, try the VBA code in this section.

VBA code: Allow duplicates in data validation drop-down list

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        Target.Value = xValue1 & delimiter & xValue2
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Result

Now you can select multiple items from the drop-down lists in the current worksheet. To repeat an item in a drop-down list cell, continue to select that item from the list. See screenshot:

Removing any existing items from drop-down list

After selecting multiple items from a drop-down list, you may sometimes need to remove an existing item from the drop-down list cell. This section provides another piece of VBA code to help you accomplish this task.

VBA code: Remove any existing items from the drop-down list cell

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRngDV As Range
    Dim TargetRange As Range
    Dim oldValue As String
    Dim newValue As String
    Dim delimiter As String
    Dim allValues As Variant
    Dim valueExists As Boolean
    Dim i As Long
    Dim cleanedValue As String

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Set your desired delimiter here

    If Target.CountLarge > 1 Then Exit Sub

    ' Check if the change is within the specific range
    If Intersect(Target, TargetRange) Is Nothing Then Exit Sub

    On Error Resume Next
    Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
    If xRngDV Is Nothing Or Target.Value = "" Then
        ' Skip if there's no data validation or if the cell is cleared
        Application.EnableEvents = True
        Exit Sub
    End If
    On Error GoTo 0

    If Not Intersect(Target, xRngDV) Is Nothing Then
        Application.EnableEvents = False
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        Target.Value = newValue

        ' Split the old value by delimiter and check if new value already exists
        allValues = Split(oldValue, delimiter)
        valueExists = False
        For i = LBound(allValues) To UBound(allValues)
            If Trim(allValues(i)) = newValue Then
                valueExists = True
                Exit For
            End If
        Next i

        ' Add or remove value based on its existence
        If valueExists Then
            ' Remove the value
            cleanedValue = ""
            For i = LBound(allValues) To UBound(allValues)
                If Trim(allValues(i)) <> newValue Then
                    If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
                    cleanedValue = cleanedValue & Trim(allValues(i))
                End If
            Next i
            Target.Value = cleanedValue
        Else
            ' Add the value
            If oldValue <> "" Then
                Target.Value = oldValue & delimiter & newValue
            Else
                Target.Value = newValue
            End If
        End If

        Application.EnableEvents = True
    End If
End Sub

Result

This VBA code allows you to select multiple items from a drop-down list and easily remove any item you've already chosen. After selecting multiple items, if you want to remove a specific one, simply select it again from the list.

Setting a custom separator

The delimiter is set as comma in the above VBA codes. You can modify this variable to any preferred character to use as the separator for the drop-down list selections. Here is how you can do:

As you can see that the above VBA codes all have the following line:

delimiter = ", "

You just need to change the comma to any separator as you need. For example, you want to separate the items by semicolon, change the line to:

delimiter = "; "

delimiter = vbNewLine

Setting a specified range

The above VBA codes apply to all drop-down lists in the current worksheet. If you only want the VBA codes to apply to a certain range of drop-down lists, you can specify the range in the above VBA code as follows.

As you can see that the above VBA codes all have the following line:

Set TargetRange = Me.UsedRange

You just need to change the line to:

Set TargetRange = Me.Range("C2:C10")

Executing in a protected worksheet

Imagine that you have protected a worksheet with the password "123" and set the drop-down list cells to "Unlocked" before activating the protection, thus ensuring that the multi-select function remains active after the protection. However, the VBA codes mentioned above cannot work in this case, and this section describes another VBA script that is specifically designed to handle multi-select functionality in a protected worksheet.

VBA code: Enable multiple selection in drop-down list without duplicates

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range
    Dim isProtected As Boolean
    Dim pswd As Variant

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    
    ' Check if sheet is protected
    isProtected = Me.ProtectContents
    If isProtected Then
        ' If protected, temporarily unprotect. Adjust or remove the password as needed.
        pswd = "yourPassword" ' Change or remove this as needed
        Me.Unprotect Password:=pswd
    End If

    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        If isProtected Then Me.Protect Password:=pswd
        Exit Sub
    End If
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0

    ' Re-protect the sheet if it was protected
    If isProtected Then
        Me.Protect Password:=pswd
    End If
End Sub

By enabling multiple selections in Excel drop-down lists, you can greatly enhance the functionality and flexibility of your worksheets. Whether you're comfortable with VBA coding or prefer a more straightforward solution like Kutools, you now have the capability to transform your standard drop-down lists into dynamic, multi-select tools. With these skills, you're now equipped to create more dynamic and user-friendly Excel documents. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.

Related Articles