How to Select Multiple Items from a Drop Down in Excel?

A drop-down list in Excel is a data validation tool that allows users to select values from a predefined list of options. 

The default behavior of a drop-down list in Excel is that if you choose an option and select another, the new value replaces the previously chosen value.

However, we can modify this default behavior to select and display multiple items from a drop-down in Excel. 

Excel does not have built-in features to enable the choice of multiple items from a drop-down list.

Therefore, we must apply Excel VBA code to allow the choice of many values from a drop-down list in Excel. 

This tutorial shows three ways of utilizing Excel VBA code to enable the selection of multiple values from a drop-down list in Excel.

How to Create a Drop-down List in Excel

We need to create a drop-down list in Excel before we can add the extra functionality of allowing the selection of multiple items in the drop-down.

Below I have the names of some laptop brands in column A, and I want to create a drop-down list in a cell that would display selected brand names when the drop-down list is used.

We use the following steps to create an example drop-down menu in Excel:

1. Name the cell range A1:A10 by selecting the list, clicking in the Name Box, typing in “Laptop_Brands_List,” and then pressing Enter.

Note: Naming the cell range makes it easy to refer to it later (instead of referring to the cell reference of the range). The name must not have spaces.

2. Open the second worksheet in the example workbook and name it ‘Drop-down 1’. This is the sheet where I will have the drop-down

3. Select cell A2, where we want to create the drop-down list.
4. On the Data tab, on the Data Tools group, click the Data Validation button.

5. On the Data Validation dialog box, on the Settings tab, open the Allow drop-down and choose List; type “=Laptop_Brands_List” on the Source box and click OK.

Note: Instead of typing the name of the named cell range on the Source box, click inside the box, press F3, select the name on the Paste Name dialog box that pops up, and click OK.

Test the Example Drop-down 1 List

Open the “Drop-down 1” worksheet and select cell A2 and notice the drop-down arrow on the bottom right corner of the cell.

Click on the drop-down arrow to open the list of options.

When we choose different options, one after the other, we notice that a new value replaces an existing value, so the drop-down only displays the latest value.

We want to change this default behavior of the feature so that we can select and display multiple values in a cell.

Method #1: Use Excel VBA Code to Enable the Selection of Multiple Items From a Drop-down List, Each Item on a New Line (No Duplicates Allowed)

We can use Excel VBA code to add extra functionality to the example drop-down list we created, enabling the selection of multiple values, each on a new line, as in the example below.

We use the below steps:

1. Press Alt + F11 to activate the Visual Basic Editor.
2. In the Visual Basic Editor, on the Project Explorer window, double-click the Sheet2 (Drop-down 1) object to open its code window on the right. This is the sheet where we have the drop-down in which we want to enable multiple select functionality

Note: If you do not see the Project Explorer window, activate it by pressing Ctrl + R. Alternatively, open the View menu on the menu bar, and choose Project Explorer.

3. Copy the code below and paste it into the worksheet’s code window.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

4. Save the sub-procedure and the workbook as a Macro-Enabled Workbook (.xlsm).

Note: This code does not allow the selection of duplicate values. However, to enable identical values to be selected, we must remove the statement “If InStr(1, Oldvalue, Newvalue) = 0 Then” from the code.

Test the Drop-down 1 List

Activate the “Drop-down 1” worksheet and select cell A2.

Click the drop-down arrow to open the options and choose several values, one after another and notice that all the values you have selected appear in cell A2, each on a new line, as in the example below.

Note: If you select many items, adjust the row height and column width of the cell accordingly to ensure all the items fit in the cell. 

Method #2: Use Excel VBA Code to Enable the Selection of Multiple Items From a Drop-down List, Separated by Commas (No Duplicates Allowed)

We can use Excel VBA code to add extra functionality to the drop-down we create, enabling the selection of multiple values, each separated by a comma, as in the example below.

We use the following steps:

1. Add a new worksheet to the “Drop-down Examples” workbook and name it  “Drop-down 2.”
2. Select cell A1, and enter “Top Laptop Brands.” Expand the column width so that the cell can accommodate many values. 

3. Select cell A2, where we want to create the drop-down list.
4. On the Data tab, on the Data Tools group, click the Data Validation button.

5. On the Data Validation dialog box, on the Settings tab, open the Allow drop-down and choose List; type “=Laptop_Brands_List” on the Source box and click OK.

Note: Alternatively, click inside the Source box, press F3, select the name on the Paste Name dialog box that pops up, and click OK.

6. Press Alt + F11 to activate the Visual Basic Editor.
7. In the Visual Basic Editor, on the Project Explorer window, double-click the Sheet3 (Drop-down 2) object to open its code window on the right.

Note: If the Project Explorer window is not open, activate it by pressing Ctrl + R. Alternatively, click View on the menu bar and choose Project Explorer.

8. Copy the below code and paste it into the worksheet’s code window.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

9. Save the sub-procedure and the workbook as a Macro-Enabled Workbook (.xlsm).

Note: This sub-procedure does not allow the selection of duplicate values. However, to allow the same values to be selected again, we must delete the statement “If InStr(1, Oldvalue, Newvalue) = 0 Then” from the code.

Test the Drop-down 2 List

Activate the “Drop-down 2” worksheet and select cell A2.

Click the drop-down arrow to open the options and choose several values, one after another and notice that all the values we have selected appear in cell A2, separated by commas, as in the example below.

Note: If you select many items that exceed the column width, expand the column width as needed to accommodate more items.

The above code only works for cell A2. 

Sometimes we may want the code to work for drop-down lists in a whole column, as in the example below:

For the code to work for drop-down lists in an entire column, for example, column B, we must change the cell range in the sixth line to Range (“B:B”) as shown below in red:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Method #3: Use Excel VBA Code to Enable the Selection of Multiple Items From Drop-down Lists in a Row of Cells (No Duplicates Allowed)

We can utilize Excel VBA code to add extra functionality to the drop-down lists we create in a row of cells, enabling the selection of multiple values in each cell in the row, as in the example below.

We use the following steps:

1. Add a new worksheet to the “Drop-down Examples” workbook and name it  “Drop-down 3.”
2. Enter “Vendor 1”, “Vendor 2”, and “Vendor 3” in cells  A1, B1, and C1, respectively, as shown below:

3. Select cells A2, B2, and C3 where we want to create the drop-down lists.
4. On the Data tab, on the Data Tools group, click the Data Validation button.

5. On the Data Validation dialog box, on the Settings tab, open the Allow drop-down and choose List; type “=Laptop_Brands_List” on the Source box and click OK.

Note: Alternatively, click inside the box, press the F3 function key, select the name on the Paste Name dialog box that pops up, and click OK.

6. Press Alt + F11 to launch the Visual Basic Editor.
7. In the Visual Basic Editor, on the Project Explorer window, double-click the Sheet4 (Drop-down 3) object to open its code window on the right.

Note: If you do not see the Project Explorer window, open it by pressing Ctrl + R. Alternatively, click View on the menu bar and choose the Project Explorer option.

8. Copy the code below and paste it into the worksheet’s code window.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2:C2")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

9. Save the sub-procedure and the workbook as a Macro-Enabled Workbook (.xlsm).

Note: This Excel VBA code does not allow the selection of duplicate values. However, to enable identical values to be selected, we must delete the statement “If InStr(1, Oldvalue, Newvalue) = 0 Then” from the code.

Test the Drop-down 3 List

Activate the “Drop-down 3” worksheet and select cell A2.

Click the drop-down arrow to open the options and choose several values, one after another and notice that all the values we have chosen appear in cell A2, separated by commas. Next, select cells B2 and C2, selecting multiple values. 

Multiple values appear in cells A2, B2, and C2 as shown below:

How to Enable Selection of Multiple Items From Drop-down List On a Protected Worksheet

To enable the selection of multiple items from a drop-down list on a protected worksheet, we must ensure the following:

1. The cell(s) containing the drop-down list is not locked, as you protect the worksheet. For example, if we want cell B1 to remain unlocked in a protected worksheet, we do the following:

• Select cell B1, which we want to remain unlocked.
• Press Ctrl + 1 (press and hold down the Ctrl key, then press 1) to open the Format Cells dialog box.
• Open the Protection tab on the Format Cells dialog box, deselect the Locked option, and click OK.

• Protect the worksheet by opening the Review tab and clicking the Protect Sheet button on the Protection group. 
• Click OK on the Protect Sheet dialog box that appears.

2. Add the following statement to the code immediately after the Dim statements.

Me.Protect UserInterfaceOnly:=True

Benefits of Using a Drop-Down List in Excel

Drop-down menus in Excel can be helpful in many ways, including the following:

• They ensure consistency and accuracy in data entry by limiting the options available to users.
• They make data entry faster and more efficient by allowing users to select options from a predefined list rather than having to type out the values. Manual data entry can be error-prone, but selecting options from drop-down endures no errors.

This tutorial showed three techniques of using the Excel VBA code to enable the selection of multiple items from a drop-down list in Excel. We hope you found the tutorial helpful.

We would greatly appreciate it if you could share your thoughts with us in the comments section.

Other Excel articles you may also like:

• How to Remove Drop-down List in Excel?
• How to Select Visible Cells Only in Excel?
• How to Select Every Other Row (Alternate Row) in Excel?