5 Ways To Create Multiple Selection Dropdown In Excel

Excel is a powerful spreadsheet software that offers numerous features to help users manage and analyze data efficiently. One of the most useful features in Excel is the ability to create dropdown menus, which can be used to restrict input data, provide options for users to select from, and make data entry more efficient. In this article, we will explore five ways to create multiple selection dropdowns in Excel.

Excel's built-in features allow users to create dropdown menus, but they are limited to single selection. However, there are workarounds and techniques to create multiple selection dropdowns in Excel. These techniques are useful when you need to allow users to select multiple options from a dropdown menu. Let's dive into the five methods to create multiple selection dropdowns in Excel.

Method 1: Using the Data Validation Feature

The data validation feature in Excel allows users to restrict input data in a cell or range of cells. We can use this feature to create a dropdown menu with multiple selections. Here's how:

• Select the cell where you want to create the dropdown menu.
• Go to the Data tab in the ribbon and click on Data Validation.
• In the Data Validation dialog box, select the Settings tab.
• In the Allow dropdown menu, select List.
• In the Source field, enter the range of cells that contains the options for the dropdown menu.
• Check the box next to "In-cell dropdown" to display the dropdown menu in the cell.
• Click OK to apply the data validation.

To allow multiple selections, we need to add a bit of VBA code. Press Alt + F11 to open the VBA editor, and then insert the following code in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Dim cel As Range
        For Each cel In Target
            If cel.Value <> "" Then
                cel.Value = cel.Value & ", " & cel.Offset(0, 1).Value
            End If
        Next cel
    End If
End Sub

This code will allow users to select multiple options from the dropdown menu and display them in the cell separated by commas.

Method 2: Using the Combo Box Control

Another way to create a multiple selection dropdown in Excel is by using the Combo Box control. Here's how:

• Go to the Developer tab in the ribbon and click on the Combo Box control in the Controls group.
• Draw the Combo Box control on the worksheet.
• Right-click on the Combo Box control and select Properties.
• In the Properties window, select the ListFillRange property and enter the range of cells that contains the options for the dropdown menu.
• Set the MultiSelect property to True to allow multiple selections.
• Click OK to apply the changes.

To display the selected options in a cell, we need to add a bit of VBA code. Press Alt + F11 to open the VBA editor, and then insert the following code in the worksheet module:

Private Sub ComboBox1_Change()
    Dim i As Long
    Dim sel As String
    For i = 0 To ComboBox1.ListCount - 1
        If ComboBox1.Selected(i) Then
            sel = sel & ComboBox1.List(i) & ", "
        End If
    Next i
    Range("A1").Value = Left(sel, Len(sel) - 2)
End Sub

This code will display the selected options in the cell A1 separated by commas.

Method 3: Using the ActiveX Control

We can also use the ActiveX control to create a multiple selection dropdown in Excel. Here's how:

• Go to the Developer tab in the ribbon and click on the ActiveX Control in the Controls group.
• Draw the ActiveX control on the worksheet.
• Right-click on the ActiveX control and select Properties.
• In the Properties window, select the ListFillRange property and enter the range of cells that contains the options for the dropdown menu.
• Set the MultiSelect property to True to allow multiple selections.
• Click OK to apply the changes.

To display the selected options in a cell, we need to add a bit of VBA code. Press Alt + F11 to open the VBA editor, and then insert the following code in the worksheet module:

Private Sub ActiveXControl1_Change()
    Dim i As Long
    Dim sel As String
    For i = 0 To ActiveXControl1.ListCount - 1
        If ActiveXControl1.Selected(i) Then
            sel = sel & ActiveXControl1.List(i) & ", "
        End If
    Next i
    Range("A1").Value = Left(sel, Len(sel) - 2)
End Sub

This code will display the selected options in the cell A1 separated by commas.

Method 4: Using the RefEdit Control

Another way to create a multiple selection dropdown in Excel is by using the RefEdit control. Here's how:

• Go to the Developer tab in the ribbon and click on the RefEdit control in the Controls group.
• Draw the RefEdit control on the worksheet.
• Right-click on the RefEdit control and select Properties.
• In the Properties window, select the ListFillRange property and enter the range of cells that contains the options for the dropdown menu.
• Set the MultiSelect property to True to allow multiple selections.
• Click OK to apply the changes.

To display the selected options in a cell, we need to add a bit of VBA code. Press Alt + F11 to open the VBA editor, and then insert the following code in the worksheet module:

Private Sub RefEdit1_Change()
    Dim i As Long
    Dim sel As String
    For i = 0 To RefEdit1.ListCount - 1
        If RefEdit1.Selected(i) Then
            sel = sel & RefEdit1.List(i) & ", "
        End If
    Next i
    Range("A1").Value = Left(sel, Len(sel) - 2)
End Sub

This code will display the selected options in the cell A1 separated by commas.

Method 5: Using the UserForm

The final method to create a multiple selection dropdown in Excel is by using a UserForm. Here's how:

• Press Alt + F11 to open the VBA editor.
• In the VBA editor, click on Insert > UserForm.
• Draw a ComboBox control on the UserForm.
• Right-click on the ComboBox control and select Properties.
• In the Properties window, select the ListFillRange property and enter the range of cells that contains the options for the dropdown menu.
• Set the MultiSelect property to True to allow multiple selections.
• Click OK to apply the changes.

To display the selected options in a cell, we need to add a bit of VBA code. In the UserForm module, insert the following code:

Private Sub ComboBox1_Change()
    Dim i As Long
    Dim sel As String
    For i = 0 To ComboBox1.ListCount - 1
        If ComboBox1.Selected(i) Then
            sel = sel & ComboBox1.List(i) & ", "
        End If
    Next i
    Range("A1").Value = Left(sel, Len(sel) - 2)
End Sub

This code will display the selected options in the cell A1 separated by commas.

Gallery of Multiple Selection Dropdowns in Excel

Multiple Selection Dropdowns in Excel

We hope this article has helped you learn how to create multiple selection dropdowns in Excel using different methods. Whether you're using the data validation feature, Combo Box control, ActiveX control, RefEdit control, or UserForm, you can create a multiple selection dropdown that meets your needs.