Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Drop Down List in Excel with Multiple Selections

Until now, we looked at how to create drop-down lists in Excel. Today I will show how to create a drop-down list with multiple selections in Excel.


Download Practice Workbook

You can download the practice workbook from here.


Step-by-Step Procedures to Create Drop Down List in Excel with Multiple Selections

Here, we’ve got a dataset having a column Book Name which contains some book names. Our objective today is to create a drop-down list based on this dataset that takes multiple selections. I will show step-by-step procedures in the below section.

how to create drop down list in excel with multiple selections


STEP 1: Create Drop-Down List by Using Data Validation

For creating a drop-down list with multiple selections, we have to create a drop-down list first. Let’s walk through the procedures.

  • First, select the cell where you want to create the drop-down list. I have selected Cell D5.

Create Drop-Down List by Using Data Validation

  • Then, from the Data Validation window, select List in Allow section and write the range cells whose data you want to add to the list in the Source field.
  • Alternatively, you can click on the small upward arrow in the Source section and select the data range from the worksheet.

  • Finally, we will see a drop-down list created in Cell D5.


Similar Readings:


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

We have created the drop-down list already. Now, it’s time to prepare the drop-down list for multiple selections. I will use 2 VBA codes to enable the list to accept multiple selections. One will accept the repetition of data and another one won’t take the repetition of data.

Case 1: VBA Code for Multiple Selections with Repetition

In this section, I will show the way to create a drop-down list with multiple selections which will take a repetition of data.

VBA Code for Multiple Selections with Repetition

Let’s walk through the procedures.

  • First, press ALT + F11 to open the VBA window.
  • Then, select Project Explorer. Also, doubleclick on the sheet where you want the task to be done.

  • Simultaneously, a Code window will open.
  • Afterward, Write the following code in that window.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$D$5" 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

Note: In the code part (If Target.Address = “$D$5” Then) instead of cell reference $D$5, you write the cell reference where you have created the drop-down list.
  • Finally, come back to the worksheet and we will be able to select multiple elements in the drop-down list with a repetition of the same element.

how to create drop down list in excel with multiple selections result


Case 2: VBA Code for Multiple Selection without Repetition

In this section, I will show the way to create a drop-down list with multiple selections which won’t take a repetition of data.

VBA Code for Multiple Selection without Repetition

Let’s walk through the procedures.

  • First, press ALT + F11 to open the VBA window.
  • Then, select Project Explorer. Also, doubleclick on the sheet where you want the task to be done.

  • Simultaneously, a Code window will appear.
  • Afterward, Type the following code in that window.
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$5" 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

 

Note: In the code part (If Target.Address = “$D$5” Then) instead of cell reference $D$5, you write the cell reference where you have created the drop-down list.
  • Finally, come back to the worksheet and we will be able to select multiple elements in the drop-down list without a repetition of the same element.

how to create drop down list in excel with multiple selections result


Conclusion

Using this method, you can create a drop-down list in Excel with multiple selections. Do you have any questions? Feel free to let us know in the comment section. Visit our ExcelDemy Website for more articles regarding Excel.


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