How to Create Drop Down List in Excel with Multiple Selections

Drop-Down List with Multiple Selections Created in Excel

Up till now, we looked at how to create drop-down lists in Excel. Today I will be showing how to create a drop-down list with multiple selections in Excel.


Download Practice Workbook


Steps to Create Drop-Down List in Excel with Multiple Selections

Here we’ve got a data set with the Names of some books in a bookshop called Martin Bookstore.

Data Set to Create Drop Down List with Multiple Selections

Our objective today is to create a drop-down list based on this data set that takes multiple selections.

We will accomplish this in two steps.

Step 1: Creating a Drop-Down List by Data Validation

Step 2: Enabling that List to Accept Multiple Selections by VBA Code


Step 1: Creating a Drop-Down List by Data Validation

  • Select the cell where you want to enter the drop-down list. Then go to Data>Data Validation>Data Validation under the Data Tools section.

Data Validation Tool in Excel

  • Click on Data Validation. You will get the Data validation dialogue box.

Data Validation Dialogue Box in Excel

  • From the Allow option, select List. Then in the Source option, enter the range of the cells that you want to enter as the drop-down list ($B$4:$B$23 in this example).

Data Validation Dialogue Box in Excel

  • Then click OK. You will find a drop-down list created in your selected cell.

Drop Down List Created in Excel


Similar Readings:


Step 2: Enabling the Drop-Down List to Accept Multiple Selection by VBA Code

Now we’ve created a drop-down list. But up till now, it accepts a single selection.

We want it to accept multiple selections.

  • To enable it to accept multiple selections, press Alt + F11 on your keyboard. It will open the Visual Basic Application (VBA) window.
  • In the left panel of the window, you will get a folder called VBA Project. Under this, there is another folder called Microsoft Excel Objects.

VBA Window in Excel

  • Double-click on the name of your worksheet (Sheet1 in this example). And it will open the VBA code window of the worksheet.

VBA Code Window for Worksheet

  • Now enter either of the two codes in this window, depending on whether you want to allow repetition in your multiple selections or not.

Case 1: VBA Code for Multiple Selection with Repetition

If you want multiple selections with repetition, enter the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub

If Target.Address = "$D$4" 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
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If

Application.EnableEvents = True

Exitsub:

Application.EnableEvents = True

End Sub

VBA Code for Drop Down List with Multiple Selections with Repetition

Note: Here in the 5th line of the code (If Target.Address = "$D$4" Then), “$D$4” is the cell where I am entering the list.

You use your one.

If you want to enter the list in multiple cells, then repeat the line for multiple cells using an or operator.

For example, if you want to enter the list in cells D4, F4, and H4, use:

If Target.Address = "$D$4" or Target.Address = "$F$4" or Target.Address = "$H$4" Then


Case 2: VBA Code for Multiple Selection without Repetition

And if you don’t want repetition, use this code instead.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String

Application.EnableEvents = True

On Error GoTo Exitsub

If Target.Address = "$D$4" 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

VBA Code to Create Drop Down List with Multiple Sections without Repetition

Note: Here in the 6th line of the code (If Target.Address = "$D$4" Then), “$D$4” is the cell where I am entering the list.

You use your one.

If you want to enter the list in multiple cells, then repeat the line for multiple cells using an or operator.

For example, if you want to enter the list in cells D4, F4, and H4, use:

If Target.Address = "$D$4" or Target.Address = "$F$4" or Target.Address = "$H$4" Then

After entering any of the above codes, save the file as an Excel Macro-Enabled Workbook (*.xlsm).

Saving Macro Enabled Workbook

Now come back to your worksheet and go to the drop-down list you created earlier.

You will be able to make multiple selections from the list now, separated by commas (,).

Drop-Down List with Multiple Selections Created in Excel


Conclusion

Using this method, you can create a drop-down list in Excel with multiple selections. Do you have any questions? Feel free to ask us.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

11 Comments
  1. When you create a list that allows for multiple selections in one cell, how does this impact the item counts on your pivot table? Is each selection within the cell counted as a singular item? Or are all of the values in the cell counted as one item?

    • Hlw Kim, Sorry for being late.

      # Excel Pivot Table normally considers multiple items within a cell as “1”. Therefore, multiple selection within a cell doesn’t have any effect on count. However, conditional counting may have different issue.

  2. How would you duplicate this to have more than 1 multiple select list option in the same worksheet?

    • Hlw Avery, Sorry for late reply.

      To duplicate this for more than 1 multiple select list option:

      # Replace ” If Target.Address = “$D$4″ Then ” line within the macro (with repetition or without repetition) with ” If Not Intersect(Target, Range(“D4:D5”)) Is Nothing Then “.

      Note: You can have multiple Source ranges assigned for “Target.Address” s using Data Validation.

  3. How to apply the macro to all cells in the same column?

    • Hlw Reena Pujari. Sorry for late reply.

      To apply macro to all cells in the same column, follow:

      1. Apply “Data Validation” to all the cell of a column (Highlight Entire Column > go to “Data” > apply “Data Validation”)

      2. Replace ” If Target.Address = “$D$4″ Then ” line within the macro (with repetition or without repetition) with ” If Not Intersect(Target, Range(“D:D”)) Is Nothing Then “.

      • Hello,
        I need to do data validation, with multiple select and no repetition, on two columns starting in row 7. I have modified the code as follows am receiving a validation error on the replaced line of code.

        Private Sub Worksheet_Change(ByVal Target As Range)
        ‘ To allow multiple selections in a Drop Down List in Excel (without repetition)
        Dim Oldvalue As String
        Dim Newvalue As String
        Application.EnableEvents = True
        On Error GoTo Exitsub
        If Not Intersect(Target, Range(“g:g”)) 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

        Can you help us fix the code?

        • Hlw Melissa,

          I’m assuming you need to Data Validate multiple selection G7 to H15 (adjacent columns) or whatever the cell is.

          # Modify the 1st IF Condition as ” If Not Intersect(Target, Range(“G7:H15″)) Is Nothing Then ” or Change the ” Range(“G7:H15″) ” portion with Capital Letters. Of course don’t forget to apply Data Validation to those cells first.

          Hope this works. Otherwise, comment with detailed cell references.

          Regards
          Maruf

  4. How can I apply the Multi validation to the entire sheet? what change I need to make in the code?

  5. How can I apply the Multi validation to the entire sheet and not any individual column

    • Hi JUNED SHAIKH
      I hope you are doing well. The term “Multi Validation” is a bit confusing. However, I would like to provide you with some information regarding Data Validation in Excel.
      1. You cannot have multiple columns or rows as your data source for data validation. The data source must be a single row or column.
      2. You can apply data validation to one specific cell once at a time. If you want to reapply data validation to one particular cell, you have to remove the previous data validation.
      3. You can apply data validation to the entire sheet keeping the procedure congruent with the above statements.
      I hope it helps. Have a great day.

Leave a reply

ExcelDemy
Logo