How to Multi-Select Dropdown in Excel (Step-by-Step Guide)

Normally, a dropdown list in Excel only lets the user select one of its options at a time. However, sometimes we might need to select and display multiple options from the list.

For instance, a dropdown list could contain values like table tennis, chess, basketball, football, etc. Now, if the user wants to take part in multiple sports, the dropdown needs to accept more than one value.

While Excel doesn’t provide an option to create such dropdown lists, a built-in tool like VBA can help us customize them to our preferences.

Here’s a step-by-step process to create and enforce VBA to the dropdown list.

Step 1: Make a Dropdown List

1. Select the cell (s) where you want to have the dropdown list.
2. Click Data Validation under the Data tab.
   
3. Select the Settings tab.
4. Choose the List option under Allow and enable the In-cell dropdown checkbox.
   
5. Enter the cell reference or named range of the list options under the Source field. Alternatively, you can also enter a named range.
   
6. Click OK.

Step 2: Insert the VBA Code

1. Right-click the worksheet containing the above dropdown list and select View Code.
   
2. Select Worksheet in the main window and paste the following VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellRange As Range
    Dim value1 As String
    Dim value2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set cellRange = Cells.SpecialCells(xlCellTypeAllValidation)
    If cellRange Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, cellRange) Is Nothing Then
        value2 = Target.Value
        Application.Undo
        value1 = Target.Value
        Target.Value = value2
        If value1 <> "" Then
            If value2 <> "" Then
                If value1 = value2 Or _
                   InStr(1, value1, ", " & value2) Or _
                   InStr(1, value1, value2 & ",") Then
                    Target.Value = value1
                Else
                    Target.Value = value1 & ", " & value2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Note: The above code prevents entering duplicate values. So, while you can select multiple items, you can only select each of them once.

Step 3: Save the File

1. Once you add the VBA code, save the VBA window.
2. Also, make sure you select the Excel Macro-Enabled Workbook (xlsm) while saving the workbook.
3.