Skip to main content

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.

Tip: Before applying the following methods, make sure that you have created drop-down lists in your worksheets beforehand. If you want to know how to create data validation drop-down lists, follow the instructions in this article: How to create data validation drop-down lists in Excel.

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 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:

Notes:
The above VBA code:
  • Applies to all data validation drop-down lists in the current worksheet, both existing and those created in the future.
  • Prevents you from picking the same item more than once in each drop-down list.
  • Uses comma as the separator for the selected items. To use other delimiters, please view this section to change the separator.

Using Kutools for Excel in a few clicks

If you're not comfortable with VBA, an easier alternative is Kutools for Excel's Multi-select Drop-down List feature. 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:

Notes:
  • Check the Wrap Text After Inserting a Separator option if you want to display the selected items vertically within the cell. If you prefer a horizontal listing, leave this option unchecked.
  • Check the Enable search option if you want to add a search bar to your drop-down list.
  • To apply this feature, please download and install Kutools for Excel first.

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 = "; "
Note: To change the delimiter to a newline character in these VBA codes, change this line to:
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")
Note: Here C2:C10 is the range containing the drop-down list that you want to set as multiple selections.

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
Note: In the code, make sure to replace “yourPassword” in the line pswd = "yourPassword" with the actual password you use for protecting the worksheet. For example, if your password is "abc123", then the line should be pswd = "abc123".

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.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!