How to select multiple items from drop down list into a cell in Excel?

Managing data in Excel often involves selecting values from dropdown lists. But what if you need to select multiple items and display them in a single cell? While Excel doesn’t offer this feature by default. This guide will walk you through the best techniques to create multi-selection in dropdown lists in Excel.

Select multiple items from drop down list into a cell with VBA

Select multiple items from drop down list into a cell easily and quickly

Select multiple items from drop down list into a cell with VBA

Here are some VBA codes that can help you accomplish this task.

Select duplicate items from drop down list in a cell

1. After creating a drop-down list, right click at the sheet tab to select View Code from the context menu.

2. Then in the Microsoft Visual Basic for Applications window, copy and paste below code to the blank script.

VBA: Select multiple items from drop down list in a cell

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20221111
    Dim xRgVal As Range
    Dim xStrNew As String
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    xStrNew = Target.Value
    Application.Undo
    If xStrNew = Target.Value Then
    Else
    xStrNew = xStrNew & " " & Target.Value
    Target.Value = xStrNew
    End If
    Application.EnableEvents = True
End Sub

3. Save the code and close the window to go back to the drop down list. Now you can select multiple items from the drop down list.

Note:

1. With the VBA, it separates the items by space, you can change xStrNew = xStrNew & " " & Target.Value to others to change the delimiter as you need. For example, xStrNew = xStrNew & "," & Target.Value will separate the items with commas.

2. This VBA code works for all drop down lists in the sheet.

Select multiple items from drop down list to a cell without repeat

If you just want to select unique items from the drop down list to a cell, you can repeat above steps and use below code.

VBA: Select multiple items from drop down list to a cell without repeat

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20221111
    Dim I As Integer
    Dim xRgVal As Range
    Dim xStrNew As String
    Dim xStrOld As String
    Dim xFlag As Boolean
    Dim xArr
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    xFlag = True
    xStrNew = " " & Target.Value & " "
    Application.Undo
    xStrOld = Target.Value
    If InStr(1, xStrOld, xStrNew) = 0 Then
        xStrNew = xStrNew & xStrOld & " "
    Else
        xStrNew = xStrOld
    End If
    Target.Value = xStrNew
    Application.EnableEvents = True
End Sub

Neither of the above VBA codes supports deleting specific parts of the cell contents; they only allow clearing all items from the cell.

Select multiple items from drop down list into a cell easily and quickly

Simplify your Excel workflow with Kutools for Excel! Its Multi-select Drop-down List feature allows you to easily select multiple items from a dropdown list and automatically display them in a single cell, eliminating the need for complicated VBA coding. Say goodbye to tedious manual processes and enjoy a smarter, more streamlined way to handle dropdown lists in Excel.

1. Click Kutools > Drop-down List > Enable Advanced Drop-down List. Then, click Multi-select Drop-down List from the Drop-down List again. See screenshots:

2. In the Multi-select Drop-down List dialog box, please configure as follows.

• 2.1) Select the cells containing the drop down list;
• 2.2) In the Separator box, enter a delimiter which you will use to separate the multiple items;
• 2.3) Ckeck the Enable search option as you need. (If you check this option, you can do a searchable in the drop down list later.)
• 2.4) Click the OK button.

Now, when you click the cell with drop-down list, a list box will appear. You can click the ➕ button to add the items to cell, and click ➖ button to remove the item as you need. See the demo below:

Selecting multiple items from a dropdown list in Excel doesn’t have to be a challenging task. With the step-by-step methods outlined in this guide, you can quickly enable multi-selection using VBA code or simplify the process with a tool like Kutools for Excel. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

Related articles:

Insert drop down list in Excel
You can help yourself or others to work more efficiently in worksheets for data entry by using drop down lists. With drop down list, you can quickly pick an item from the list instead of typing the own value manually.

Drop down list with multiple selections
By default, you can only select one item per time from a data validation drop-down list in Excel. How to make multiple selections from the drop-down list as below screenshot shown? The methods in this article can help you solve the problem.

Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier.

Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list.