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

How to Make Multiple Selection from Drop Down List in Excel

Depending on the circumstances you may need to select multiple values within a drop-down list. In this tutorial, I am going to show you how to make multiple selection from a drop-down list in Excel. For this session, I am using Office 365, feel free to use your preferred version.


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


3 Ideal Examples of Multiple Selection from Drop Down List in Excel

Before diving into the session, let’s get to know about the dataset that is the base of the examples. Here I have several stationery elements, using these I will create a drop down list in Excel and make multiple selection from there.

Note that it is a simple dataset to keep things straightforward. In a practical scenario, you may encounter a much larger and more complex dataset.

Dataset for Examples of Multiple Selection from Drop Down List in Excel


1. Make Multiple Selection from Drop Down List with Duplicate Options

In this example, I will explain how you can create a drop down list for multiple selections and select duplicate items. For this, you will have to create the drop down list first. But, a conventional drop down list always selects a single item. So, you will have to write a VBA code for multiple selections. Let’s explore the steps.


Step-01: Create Drop Down List in Excel

In this step, I will create the drop down list for the dataset.

  • Firstly, create a section in the Excel sheet for adding the drop-down option.

Make Multiple Selection from Drop Down List with Duplicate Options in Excel

  • Secondly, select the cell where you want to add the drop down list. Here, I selected Cell D5.
  • Thirdly, go to the Data tab.
  • Next, select Data Validation.

  • Next, the Data Validation dialog box will appear.
  • Select the Settings tab.
  • Then, click on the drop down option for Allow.
  • Afterward, select List.

Create Drop Down List in Excel for Multiple Selection

  • Next, click on the marked button to select the source.

  • After that, select the data range. Here, I selected cell range B5:B12.
  • Next, click on the marked button to add the selected range as Source.

  • Then, select OK.

  • Finally, you will see that a drop down option is added to your selected cell.

  • Now, click on the drop down button.
  • Then, select an item from the list.

SElecting Items from Drop Down List for Multiple Selection

  • After that, you will see that the selected item is showing in the selected cell.

  • Next, select another item from the drop down list.

  • Here, you will see that the item you selected has replaced the previously selected item.


Step-02: Write VBA Code for Multiple Selection

Now, I will show you how you can write a VBA code to make multiple selections from a drop down list in Excel.

  • In the beginning, go to the Developer tab.
  • Then, select Visual Basic.

Write VBA Code for Multiple Selection from Drop Down List in VBA

  • Consequently, the Visual Basic Editor will appear.
  • Next, rightclick on the sheet where you want to select multiple options.
  • Then, select View Code.

Opening Worksheet to Write VBA Code for Multiple Selection from Drop Down List in Excel

  • Now, a sheet module will appear.
  • Click on the marked drop-down button.
  • Then, select Worksheet.

  • Afterward, select the marked drop-down button.
  • Next, select Change.

  • Now, you will see a Private Sub will be created.

  • After that, write the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val 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
            new_val = Target.Value
            Application.Undo
            old_val = Target.Value
            If old_val = "" Then
            Target.Value = new_val
            Else
            Target.Value = old_val & ", " & new_val
            End If
            End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

VBA Code for Making Multiple Selection from Drop Down List in Excel

🔎 How Does the Code Work?

  • Here, I have declared two strings called old_val and new_val.
  • You can see I made the drop down list in the D5 cell, that’s why the target address is D5. And in addition, I have rechecked whether the cell is using data validation or not using Target.SpecialCells.
  • Once a value is selected, I turned off events (Application.EnableEvents = False) so changes don’t trigger the event again. Then stored the selected item in the new_val.
  • After undoing the change, I have set the value to the old_val. Then check whether the old_val is empty or not. If empty (means only one value is selected), then return the new_val. Otherwise, concatenate the old_val and new_val.
  • Before ending the reset the event, so that I can change it if required.
  • Further, Save the code and go back to your worksheet.

Saving VBA Code for Multiple Selection from Drop Down List in Excel

  • Now, select a second item from the drop down list.

  • Here, you will see both of the selected items are showing in the cell.

  • Now, select an item that you have selected before.

  • Finally, you will see that this item is also added to the list.

Read more: Excel Drop Down List Depending on Selection


2. Select Multiple Options with Unique Selection Only from Drop Down List

In this example, I will show you how you can select multiple unique items from a drop down list in Excel. If you select an item that you have selected before then it will not add it to the Selected Item. Let’s see the steps.

Steps:

  • To begin with, add a drop down list in your preferred cell by following Step-01 from Method-01.

Select Multiple Options with Unique Selection Only from Drop Down List in Excel

  • Next, open the Visual Basic Editor window and then open a Worksheet to write the VBA code by following Step-02 of Method-1.
  • After that, write the following code in that Worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val 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
            new_val = Target.Value
            Application.Undo
            old_val = Target.Value
            If old_val = "" Then
            Target.Value = new_val
            Else
            If InStr(1, old_val, new_val) = 0 Then
            Target.Value = old_val & ", " & new_val
            Else:
            Target.Value = old_val
            End If
            End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

🔎 How Does the Code Work?

  • Here, I declared 2 variables named old_val and new_val as String.
  • Then, I used the Application.EnableEvents property to enable events for specified properties.
  • Next, I used IF Statement to add the items from the list.
  • Afterward, I used another IF Statement to check if the item was selected before.
  • Then, I used the InStr function in the IF statement to find one string within another.
  • Further, I ended the IF Statements.
  • Finally, I ended the Sub Procedure.
  • Next, Save the code and go back to your worksheet.
  • Afterward, select an item from the drop down list.

  • Then, you will see the item is added.
  • Now, select another item from the list.

  • Here, you will see both of the items are added to the Selected Item.

  • Afterward, select an item that you have selected before from the drop down list.

  • Now, you will see that the item is not added to the selected Item.

Read more: Multiple Dependent Drop-Down List Excel VBA


3. Make Multiple Selection and Add Items on New Line in Excel

So far, I have found the items are separated by a comma. In this section, I will arrange the selected items in newlines. Let me show you the steps.

Steps:

  • In the beginning, I will merge a few cells with Cell D5.
  • To do that, select the cells you want to merge. Here, I selected cell range D5:D9.
  • Then, go to the Home tab.
  • After that, select Merge & Center.

Make Multiple Selection and Add Items on New Line in Excel

  • After that, add the drop down list following Step-01 from Method-1.

  • Afterward, open the Visual Basic Editor window and then open a worksheet to write the VBA code by following Step-02 of Method-1.
  • Then, write the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val 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
            new_val = Target.Value
            Application.Undo
            old_val = Target.Value
            If old_val = "" Then
            Target.Value = new_val
            Else
            If InStr(1, old_val, new_val) = 0 Then
            Target.Value = old_val & vbNewLine & new_val
            Else:
            Target.Value = old_val
            End If
            End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

This code works in the same way as the previous code. But, instead of the delimiter use vbNewLine to add the newly selected item in a new line.
  • After that, Save the code and go back to your worksheet.
  • Next, select an item from the drop down list.

  • Then, you will see that the item will be added to the Selected Item.

  • After that, select another item from the drop down list.

  • Now, you will see that the new item is added to a new line.

  • In the following image, you can see that I have added a few more items from the list and each of them is added to a new line.

Read more: How to Create a Drop Down List From Another Sheet in Excel


Things to Remember

  • If you are working with VBA then you must save the Excel file as Excel MacroEnabled Workbook.

Practice Section

Here, I have provided a practice sheet for you to practice how you can create a drop down list with multiple selection options in Excel.

Practice Sheet for How to Create a Drop Down List for Multiple Selection in Excel


Conclusion

That’s all for today. We have listed several approaches to make multiple selection in a drop-down list in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

31 Comments
  1. How can you apply this formula to a range of cells? I would like this to apply to a column of cells instead of a single cell.

  2. Hi! Same question! Anybody find an answer?

    • Reply
      Naimul Hasan Arif Aug 16, 2022 at 2:46 PM

      You can wrap your code in a loop to iterate continuously across a column if you want to traverse it.

      • Hi, this was really helpful but please could you provide the code for the loop? Thank you!!

        • Naimul Hasan Arif Aug 25, 2022 at 3:35 PM

          I have tried the following VBA code for all the cells in D Column. I hope this is the thing you are looking for.

          Private Sub Worksheet_Change(ByVal Target As Range)

          Dim Oldvalue As String
          Dim Newvalue As String
          Dim mn As Range, pq As Range
          On Error GoTo Exitsub
          Set mn = Range(“D:D”)
          For Each pq In mn
          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
          Next pq
          Application.EnableEvents = True
          Exitsub:
          Application.EnableEvents = True
          End Sub

  3. I deleted the following lines of the VBA code:

    If Target.Address = “$D$4” Then

    the first “End If”

  4. How do you deselect once selected using this code?

  5. Its brilliant, but………. I want to also be able to add freeform text. So user selects a couple of standard texts from the drop down – all good
    Then they click in to add specific text, hit enter and all of a sudden it duplicates the specific text already added.
    I cant quite work out why this is happening.

    • Reply
      Naimul Hasan Arif Aug 16, 2022 at 2:52 PM

      I’m afraid in an ideal scenario once you’ve used data validation with list of items to be selected, you will encounter an error since Excel anticipates values from that list only not any free text.

  6. Reply
    Naimul Hasan Arif Aug 25, 2022 at 3:53 PM

    Thanks all of you guys for your comments.
    I think so many people are facing the same problem of applying the code in a range of cells. In our article, the VBA code that we have shown only works for a fixed cell. So, I am going to give you guys a slightly modified VBA code that will work for a range of cells( i.e. entire D column).

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Oldvalue As String
    Dim Newvalue As String
    Dim mn As Range, pq As Range
    On Error GoTo Exitsub
    Set mn = Range(“D:D”)
    For Each pq In mn
    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
    Next pq
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub

    • Hello,
      that’s really interesting, but using this last code I get an error of syntax related to the following lines:

      Set mn = Range(“C:C”)

      Target.Value = Oldvalue & “, ” & Newvalue

      Where is this syntax error?

      Thanks

      • Reply
        Naimul Hasan Arif Sep 4, 2022 at 2:11 PM

        Thanks for the appreciation.
        In my case, it works just fine. It is very tough for me to give a solution without analyzing your code related to the dataset. It would be helpful for me if you could provide me your code.

        • Georgina Woods Nov 9, 2022 at 10:57 PM

          I am now getting the same code as Christopher.
          Syntax error in two lines of code:

          Set mn = Range(“G:G”)

          and

          Target.Value = Oldvalue & “;” & Newvalue

          I copied your code exactly, and only changed it to G:G to relate to my specific column needs.

          Many thanks,

        • Thank you Naimul,
          this is the code, I didn’t report it because I just copied and pasted your.

          I only changed the column from D to C:

          Private Sub Worksheet_Change(ByVal Target As Range)

          Dim Oldvalue As String
          Dim Newvalue As String
          Dim mn As Range, pq As Range
          On Error GoTo Exitsub
          Set mn = Range(“C:C”)
          For Each pq In mn
          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
          Next pq
          Application.EnableEvents = True
          Exitsub:
          Application.EnableEvents = True
          End Sub

  7. Thanks for your help! I am trying to use your code, but receive an error stating “Compile Error; Expected: End of statement” with the first Dim highlighted. What does that mean?

    • Hello ANN HALL,
      This may happen for different reasons.
      1. Putting two statements in a single line. Check whether this is encountered and send the second statement in a new line.
      2. Absence of parentheses.
      3. Lack of whitespace in front of the ampersand(&) interpret as a long variable. So, put space between the variable and operator(&).

  8. Hello – is there a way to turn off the error checking in the excel cell. I am creating a form for users and I don’t want them to see the “validation error” message.

    • Hello SG,
      You can customize the error message. In the Data Validation message box=> Error Alert icon, you can choose “Warning” style from the dropdown list. Now, if you try to input invalid data, the error message will show 3 options asking you whether you want to continue: Yes/No/Cancel/Help. Clicking “Yes” will allow you to proceed with the current value and will not show the error again.

  9. Hello, I tried this however absolutely nothing seems to happen. The first time I attempted to click ”save” it asked me to save the spreadsheet in a macro enabled format (.xlsm) which I have done. I closed it and reopened it. But nada! The code is in there but it doesnt seem to have worked.

    I literally copied/pasted the code exactly as you wrote it….assuming nothing needed to be amended in the code?

    Thanks,

    • Reply
      Naimul Hasan Arif Nov 10, 2022 at 11:40 AM

      Thanks for your valuable comment.
      A few factors might play a vital role in your problem.
      1. As the file contains VBA code, the file must be saved in “.xlsm” format.
      2. Don’t forget to change the ranges in your code. I have applied the code in D column. So, it’ll only be applicable in the D column.

  10. I have different lists in a single sheet where the VBA code is to be placed. Now I have to make multi selections from specific lists (ie having cells referring to different list to make multi selection from). Should I simply add different data validation for the respective cells?

    • Dear PRATIK,
      Thanks very much for reading our articles. You have mentioned that you have different lists in a single sheet. Here, we will show how to declare data validation in different cells with different lists in a single sheet.

      Steps:
      ● Here, we have three different lists of mobile, laptop, and routers. Here, we will introduce different drop-down lists with multiple selections.

      Solution_1

      ● Now, apply data validation as shown in the article already.
      ● We will choose Range B5:B11 at Cell G4, Range C5:C11 at Cell G5, and Range D5:D11 at Cell G6 for the data-validation source.
      ● Choose any item from the drop-down list of Cell G4.

      Solution_3

      ● Similarly, choose items for Cell G5 and G6 from the drop-down list.

      Solution_4

      ● Now, put the following VBA code in the VBA module. After that, save the VBA code.
      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Oldvalue As String
      Dim Newvalue As String
      On Error GoTo Exitsub
      If Target.Column = 7 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

      null

      ● Now, choose multiple options from the drop-down list.

      Solution_5

      Here, we changed the Target.Address to Target.Column and put the column number. Also, need to mention the whole column will save the previous result.

  11. Please Help. The First code works for my project however I can only get the drop down list to work for once cell.

    I want to change the line

    If Target.Address = “$AG$4” Then

    to include cells AG4 to AG43

    How do I get this to work? I tried $AG$4:$AG$43, as well as AND but they did not work

    • Hello Allison,
      Glad that you shared your problem. I have looked at it and therefore suggest these solutions.
      If you want a drop-down list for some selected cells (AG4:AG43) then replace
      If Target.Address = "$AG$4" Then
      With
      If Target.Address = "$AG$4" Or Target.Address = "$AG$5" Or Target.Address = "$AG$6" …….. Or Target.Address = "$AG$43" Then
      If you want a drop-down list for the whole AG Column which is the 33rd column in the workbook then replace
      If Target.Address = "$AG$4" Then
      With
      If Target.Column = 33 Then
      I hope the solutions will help you. Let us know your feedback.
      Regards,
      Guria,
      Exceldemy

  12. @ Allison, try this one:

    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(“F:F”)) 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

Leave a reply

ExcelDemy
Logo