How to Make Multiple Selection from Drop Down List in Excel (2023)

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.

Table of Contents hide

Download Practice Workbook

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

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

Step-01: Create Drop Down List in Excel

Step-02: Write VBA Code for Multiple Selection

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

(Video) How to Make Multiple Selections in a Drop-Down List in Excel - No Duplicates Allowed - VBA Code inc

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

Things to Remember

Practice Section

Conclusion

Download Practice Workbook

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

Selecting Multiple Options from Drop Down.xlsm

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.

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.

(Video) How to Create a Drop-Down List With Multiple Options in Excel : Computers & Tech Tips

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

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.

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.

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.

Consequently, the Visual Basic Editor will appear.
Next, right–clickon the sheet where you want to select multiple options.
Then, select View Code.

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 StringDim new_val As StringOn Error GoTo ExitsubIf 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 IfEnd IfApplication.EnableEvents = TrueExitsub:Application.EnableEvents = TrueEnd Sub

🔎 How Does the Code Work?

(Video) How to Select Multiple Items From an Excel Drop Down List

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.

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.

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 StringDim new_val As StringApplication.EnableEvents = TrueOn Error GoTo ExitsubIf Target.Address = "$D$5" ThenIf Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo ExitsubElse: 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 IfEnd IfEnd IfApplication.EnableEvents = TrueExitsub:Application.EnableEvents = TrueEnd 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.

(Video) How to Create A Multi-Select Drop-Down List in Excel

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.

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 StringDim new_val As StringApplication.EnableEvents = TrueOn Error GoTo ExitsubIf Target.Address = "$D$5" ThenIf Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo ExitsubElse: 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 IfEnd IfEnd IfApplication.EnableEvents = TrueExitsub:Application.EnableEvents = TrueEnd 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 Macro–Enabled 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.

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.

How to Make Multiple Selection from Drop Down List in Excel (2023)

Download Practice Workbook

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

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

Step-01: Create Drop Down List in Excel

Step-02: Write VBA Code for Multiple Selection

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

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

Things to Remember

Practice Section

Conclusion

Further Readings

Videos

References