This guide will explain how to allow multiple selections in a drop-down list in Excel.
We’ll use Excel VBA code to add additional functionality to our basic Excel drop-down list.
Drop-down lists are one of the most useful features available in Excel. They allow you to create a list of items that you can then select from. This is extremely helpful when you have a large list of items that you must choose from.
However, one limitation of Excel drop-down lists is that users can only select one possible option.
This could be an issue if your spreadsheet requires you to choose 1 or more options from a list of values.
Let’s take a look at a quick example where we have to make multiple selections from a drop-down list.
An example use case for selecting multiple options in a drop-down list would be if you were creating a spreadsheet that allows users to filter for specific tags. With a single drop-down list, users could only view entries in the dataset one tag at a time.
We can add Excel VBA code to our worksheet to add additional functionality to our drop-down list. We can modify our drop-down list so that multiple selections appear as a comma separated list.
Now that we have an idea of when drop-down lists with multiple selections could be useful, let’s learn how to set it up on an actual spreadsheet.
A Real Example of a Drop-down List that Allows Multiple Selections
The following section provides a simple example of a drop-down list that allows the user to make more than one selection. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a real example of the function being used in an Excel spreadsheet.
In the example below, we have a list of colors in the range B3:B10. We can use the Data Validation feature in Excel to create a cell with a drop-down list where the options are populated by the values in our range.
After selecting Yellow, the user can then choose to select another option, such as Pink.
The final value in E2 are the two options selected by the user. Each option is separated by a comma.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. We’ve also attached the necessary VBA code necessary to add this functionality.
Note that Excel VBA code can run in the Desktop version of the Excel app only.
Use our sample spreadsheet to test out how the drop-down list handles three or more options. If you’re ready to try setting up the drop-down list yourself, head over to the next section to read our guide on how to do it!
How to Make Multiple Selections from drop-down List in Excel
This section will guide you through each step needed to allow users to select multiple options from a drop-down list. You’ll learn how we can Excel VBA code to allow users to select more than one option.
Follow these steps to start making selections from the drop-down list in Excel:
- Select the cell that will contain the drop-down list. In this example, we’ll add the drop-down list in cell E2.
- In the Data tab, click on the Data Validation option.
- In the Data Validation dialog box, select List from the drop-down menu for ‘Allow’. In the text box under ‘Source’, indicate the cell range with your list of values. Alternatively, you may type each option manually, separated by commas.
- Your cell should now have a drop-down list with the desired range of options.
- Next, we’ll add the Excel VBA code that will allow the user to select more than one option. Type the keyboard shortcut Alt + F11 to access the VBA Editor.
- In the left-hand panel, select the Microsoft Excel Object that has the same name as your current sheet. Paste the provided VBA code into the editor.
Ensure that the value for ‘Target.Address’ is the same as the address with the drop-down list. After making the necessary changes, save the current VBA project with the shortcut Ctrl + S.
- Let’s check if the drop-down list works as intended. Start by selecting one option from the list.
- Next, select another option from the drop-down list.
- If the VBA code was added correctly, your cell should now contain both options separated by a comma.
These are all the steps you need to allow multiple selections from a drop-down list in Excel.
This guide will provide you with the information necessary to start creating a drop-down list that allows for multiple selections.
You should now understand how to insert VBA code into your worksheet to make it possible for users to select multiple options in a drop-down list.
The function is just one example of many that can be used in Excel spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one most appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
