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

The drop-down list is frequently used in the Excel daily work. By default, only one item can be selected in a drop-down list. But in some times, you may need to select multiple items from the drop down list into one single cell as below screenshot shown. How can you handle it in Excel?

doc select multiple items from drop down list 1 doc arrow right doc select multiple items from drop down list 2

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

Select multiple items from drop down list into a cell with Kutools for Excel’s handy option


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

Here are some VBA can do you a favor on solving this job.

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.
doc select multiple items from drop down list 3

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)
'UpdatebyExtendoffice20180510
    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
    Application.EnableEvents = False
    xStrNew = Target.Value
    Application.Undo
    xStrNew = xStrNew & " " & Target.Value
    Target.Value = xStrNew
    Application.EnableEvents = True
End Sub

doc select multiple items from drop down list 4

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)
'UpdatebyExtendoffice20180510
    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
    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

Select multiple items from drop down list into a cell with Kutools for Excel’s handy option

If you are not familiar with VBA code, you can free install a handy tool - Kutools for Excel, which contains a group of utilities about drop-down list, and there is an option Multi-select Drop-down list can help you easily select multiple items from drop-down list into a cell.

After creating the drop-down list, please select the drop-down list cells and click Kutools > Drop-down List > Multi-select Drop-down List to enable this utility.
doc kutools multi select drop down list 1

Then the selected drop-down list cells can be selected multiple items into the cell.

If you use this option at first time, you can specify the settings of this utility as you need before applying this utility.

Click Kutools > Drop-down List > the arrow beside Multi-select Drop-down List > Settings.
doc kutools multi select drop down list 2

Then in the Multi-select Drop-down List Settings dialog, you can

1) Specify the applying scope as you need;

2) Specify the direction of items place;

3) Specify the separator between items;

4) Specify if do not add duplicates and remove duplicate items.

Click Ok and click Kutools > Drop-down List > Multi-select Drop-down List to take it effect.

Tip: To apply the Multi-select Drop-down List utility, you need to install it at first. If you want to create a drop-down list with multiple levels, the below utility may help you.

Easily create a dynamic 2-level or multiple-level dependent drop-down list in Excel

In Excel, to create a 2-level or multiple level drop-down list is complex. Here the Dynamic Drop-down List utility of Kutools for Excel can do you a favor. What you only to do is order the data as example shown, then select the data range and output range, then leave it to the utility.  Click for 30 days free trial!
doc advanced combine rows
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    karim.abdelrazek@gmail.com · 6 months ago
    If I create a sheet with dropdown list using checkboxes, is there a way to share this workbook with this checkboxes feature?
  • To post as a guest, your comment is unpublished.
    John · 11 months ago
    How would you apply this functionality but making it so that there aren't redundant values? Any help would be appreciated!
  • To post as a guest, your comment is unpublished.
    Madhu · 1 years ago
    I used this "Select multiple items from drop down list to a cell without repeat"
    this is what happened
    1. Multiple selections happen without problems.
    2. The issue is when i try to edit and remove an option. There is no way for me to remove an option. it keeps multiplying.
  • To post as a guest, your comment is unpublished.
    debadeep · 1 years ago
    After adding,the data from dropdown,delete doesnt work.
    • To post as a guest, your comment is unpublished.
      Susan Lynn · 1 years ago
      I found the same issue. I think I found a way to overcome it. At the bottom of the code you'll see the code "xStrNew = xStrOld". Instead, make it say xStrNew = " "

      It works for me.
      • To post as a guest, your comment is unpublished.
        CJ · 2 months ago
        Any chance you still have this code? I'm trying to do what you said but the text still duplicates.
        Thanks
        • To post as a guest, your comment is unpublished.
          Susan Lynn · 2 months ago
          I’m sorry but I don’t. However, I just looked at the code above and it appears that the author edited the code with my suggestion.

          post your issue on the main thread to this article.