How to Create Multiple Selections Drop-Down List in Excel

Drop-down lists are an excellent data validation tool of Excel that allows you to restrict the input in cells.

In a drop-down list, you can specify options of your choice and restrict the users to input data from these options only. With the evolution of newer versions of Excel, the data validation tools of Excel have also advanced.

However, we have still not hit that level of versatility where a drop-down list allows multiple selections. Under normal built-in features of Excel, you can only select one option at a time from a drop-down list 📝

Nevertheless, making multiple selections from Excel drop-down lists is not impossible. It will take a little bit of coding, but you will eventually reach there.

How? Let’s unveil that together in the guide that follows. And before you slide down, here’s your free practice workbook for this tutorial.

Table of Contents

Creating Multiple Selections in a Drop-Down List

I am beginning with creating drop-down lists with multiple selections in Excel. Unless you know how to create drop down list in Excel, there’s little point in learning how to make it work with and without multiple selections.

So here we go. Creating drop-down lists in Excel is easy and fun 🚗

For instance, I have a quick auto-mobile survey form here in Excel.

For the Car’s Make section, I want to create a drop-down list that contains a pre-defined list of popular car-making brands 🚘

To do this:

Step 1) Populate the options you want to insert in the drop-down list in a separate range of cells.

Step 2) Activate the cell where you want the drop-down list inserted.

Step 3) Go to the Data tab > Data tools > Data Validation list button.

From the Data Validation dialog box that will popup next:

Step 4) Select “List” under the Allow section.

Step 5) Check the checkbox for “Ignore Blank” and “In-cell drops down”.

Step 6) As the Source, define the range of cells that contain the options we populated in separate cells (in Step 1).

Step 7) Click on Okay.

A drop-down list will be inserted in the activated cell.

You’ll see a small drop-down arrow on its right, clicking on this arrow launches the drop-down list 🔽

As you click on any option from this list, it will automatically be populated in the cell where the drop-down list is created.

However, you can select only one option from the drop-down list at a time. Upon selecting the second option, the first one is replaced by it.

Long story short, Excel drop-down lists do not allow users to make multiple selections, and there’s no readily available feature in Excel to help you achieve this.

The hack to do this is explained in the forthcoming sections 👇

Allow multiple Selections in a Drop-Down List (with repetition)

One way how you might want multiple selections in a drop-down list is with repetition.

For example, if I select Honda first, then Audi, and then Honda again, this is how the cell looks like with the selected items:

Honda is repeated twice in the cell because I selected it twice from the drop-down list 💡

If you want your drop-down list to allow repetition in multiple selections, follow these steps:

Step 1) Create the drop-down list (combo box) with multiple selections (see the above section).

Step 2) Go to the Developer tab on the Ribbon > Visual Basic Editor button.

Or you can press the shortcut key of Alt key + F11 key to launch the Visual Basic Editor ⌨

Pro Tip!

If you can’t see the Developer Tab on the Ribbon, you need to activate it from Excel Options following the steps below:

1. Go to the File tab > Excel Options.
2. From the pane on the left, select “Customize Ribbon”.
3. From the windowpane on the right of the Excel options, find and check the box for the Developer tab.
4. Press okay.
5. The Developer tab would be added to the Ribbon.

Step 3) In the Visual Basic Editor, you will see the Project Explorer pane on the left.

Step 4) From this pane, find and select the sheet where your drop-down list sits.

Step 5) In the Code window that opens next, copy and paste the following VBA Code.

Step 6) Now close the window.

Step 7) Press the F12 key and save this Excel file as a Macro-Enabled workbook (.xlsm format).

Now try making multiple selections (with repetition) in the drop-down list 🎯

Wow! This time the drop-down list doesn’t replace the already selected option with the new option but retains both.

And if an option is selected twice, it appears twice.

Isn’t this interesting?

Allow multiple Selections in a Drop-Down List (without repetition)

Another way to have multiple selections in a drop-down list is without repetitions.

For example, if I select Honda first, then Audi, and then Honda again, Honda will appear only once. Selecting it multiple times won’t it there more than once 📚

Although I selected it twice, Honda is not repeated in the cell.

If you want your drop-down list to not allow repetition in multiple selections, follow these steps:

Step 1) Create the drop-down list with multiple selections (see the above section).

Step 2) Press the shortcut key of the Alt key + F11 key to launch the Visual Basic Editor.

Step 3) In the Visual Basic Editor, from the Project Explorer pane on the left, find and select the sheet where your drop-down list sits.

Step 4) In the Code window that opens next, copy and paste the following VBA Code.

Step 5) Now close the window.

Step 6) Press the F12 key and save this Excel file as a Macro-Enabled workbook (.xlsm format).

Now try making multiple selections (with repetition) in the drop-down list.

This time the drop-down list doesn’t allow you to add the same selection from the drop-down list more than once 🚀

Conclusion

How does it feel like to be a coder (even if a beginner)? If it feels good, you have a bright future with VBA Coding in Microsoft Excel.

It makes Excel so much more dynamic and useful if you even know the basics of doing Excel VBA. Interested in learning?

Hop on to the following Spreadsheeto Excel tutorials to learn more about VBA in Excel and other interesting Excel tips.

• VBA Data Types: How to Declare (Dim) and Set VBA Variables
• Learn VBA in Excel: These 11+ Tutorials Teach You VBA in 20 Hours
• How to Create a VBA InputBox That Collects User Data (2024)