how to create drop down list in excel with multiple selections

To create a drop-down list in Excel that allows multiple selections, you need to follow two main steps: first, create a regular drop-down list using Data Validation, and second, add VBA code to enable multiple selections.

Step 1: Create a Drop-Down List Using Data Validation

1. Select the cell or range of cells where you want the drop-down list.

2. Go to the Data tab on the ribbon, then click Data Validation.

3. In the Data Validation dialog box, under the Settings tab, select List from the Allow dropdown.

4. In the Source box, enter the range or table column that contains your list items. For example, if your items are in a table named Table1 in the column "Items," use:

   =INDIRECT("Table1[Items]")
   

Alternatively, you can select a range or type a comma-separated list of values.

5. Click OK. Now you have a standard drop-down list that allows only single selections

Step 2: Add VBA Code to Enable Multiple Selections

Excel does not support multiple selections in drop-down lists natively, so you must add VBA code to allow this functionality.

1. Press Alt + F11 to open the VBA editor.
2. In the Project Explorer, find the worksheet where your drop-down list is located.
3. Right-click the worksheet name and select View Code.
4. Paste the following VBA code (example code from Ablebits and Trump Excel tutorials):

vba

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim rngDropdown As Range

    ' Define the range with drop-down lists (adjust as needed)
    Set rngDropdown = Me.Range("D3:D7")

    If Intersect(Target, rngDropdown) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    Target.Value = Newvalue

    If Oldvalue = "" Then
        ' If cell was empty, just add the new value
        Target.Value = Newvalue
    Else
        ' If cell had a value, add the new value separated by comma
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
        Else
            Target.Value = Oldvalue ' Prevent duplicates if desired
        End If
    End If
    Application.EnableEvents = True
End Sub

5. Modify the rngDropdown range to match the cells where your drop-down list is.
6. Close the VBA editor and save your workbook as a macro-enabled file (.xlsm).
7. Now, when you select an item from the drop-down, it will be added to the existing cell content, allowing multiple selections separated by commas

Additional Tips

• You can customize the VBA code to restrict multi-selection to specific columns or rows.
• Using a table as the source for the drop-down list makes it dynamic—adding or removing items updates the list automatically.
• To avoid duplicates in selections, the VBA code checks if the new item is already present before adding it

This method combines Excel's built-in Data Validation with VBA to create a multi-select drop-down list effectively.