In this article, I will show how to create a drop-down list with multiple selections in Excel. Once you create a drop-down list, you can only make one selection. Thus, if you select another item, the first item is replaced with the new selection. So, if you want to make multiple selections from the drop-down list in excel then it is the best place for you. In this article, you can quickly get all information about how to make multiple sections in excel, how to make multiple selections with colors, and how to remove them.
Drop-Down List in Excel
An Excel drop-down list is a data validation function that helps users to select an option from a list of choices/selections. It is particularly useful in performing financial modeling and analysis by incorporating scenarios and creating a spreadsheet more dynamic. A drop-down list is also known as a drop-down menu, drop menu, pull-down list, etc. It is a graphical control element, similar to a list box, that allows you to choose one value from a list. When this list is inactive, it displays a single value.
How to Make Excel Drop-Down List with Multiple Selection?
Here is the simple and easy step-by-step guide to making a drop-down list in excel with multiple selections.
Step 1: Make a Drop-Down List by Using Data Validation
You have to create a drop-down list first for creating a drop-down list with multiple selections. Now follow the procedure.
- First, select the cell where you want to create the drop-down list such as D5.
- Now, go to the Data tab and select Data Validation from the ribbon.
- There from the Data Validation window, select List in allow section and write the range of cells you want to add to the list in the Source field.
- Also, you can click on the small arrow upward in the Source section and select the data range from the spreadsheet.
- Finally, you will see a drop-down list created in Cell.
Step 2: Allow Drop-Down List to Accept Multiple Selection by VBA Code
You have created the drop-down list already. Now, it is time to make the drop-down list in excel for multiple selections. You have to use 2 VBA codes to allow the list to accept multiple selections. One will accept the repetition of data and another one would not take the repetition of data.
1: VBA Code for Multiple Selections (with Repetition)
Now, I will show the method to create a drop-down list with multiple selections which will take a repetition of data.
- First of all, press ALT + F11 to open the VBA window.
- Now, select project explorer. And double-click on the sheet where you want the work to be done.
- At the same time, a code window will open.
- Subsequently, write the following code in that window.
- Finally, come back to the worksheet and you will be able to select multiple items in the drop-down list with a repetition.
2: VBA Code for Multiple Selection (without Repetition)
There is a way to create a drop-down list with multiple selections without repetition of data.
- To open the VBA window, press ALT + F11.
- Select project explorer, and double-click on the sheet where you want the work to be done.
- At the same time, a Code window will appear.
- now, type the code in that window.
- Finally, return to the worksheet and you will be able to select multiple items in the drop-down list without a repetition of the same element.
How to Edit the Multiple Selection Drop-Down List?
There is an “Edit Entries” check box in the sample file on the SameCellEdit worksheet. View that box, and now you can edit the cell without any problems.
To edit a cell with multiple items selected, follow the given steps.
- Put a checkmark in the Edit Entries checkbox.
- Now select the cell, and edit the entries, either in the cell or in the formula bar.
- Press Enter, to finish the editing.
Excel Drop-Down List Multiple Selections with Colors
There is a way to add a drop-down list to the cells with colors. But, you cannot follow the same process for every cell as it will be more time-consuming.
- Select multiple cells you want to add a drop-down to, and then navigate to Data Validation and select the options for the drop-down.
- The second way to create an Excel drop-down for the upcoming cells is to create a drop-down list for one cell in each column and turn them into a table.
- To turn the cells into a table and apply the dropdown for all the cells, choose the cells which are formatted with dropdown and colors.
- Then, press Ctrl+T. This converts the selected cells into a table and any entry you make after them will have the same formatting and validations.
- In the Create dialog box, select the data and check the checkbox for My table and click OK.
- Now, navigate to Table Design in the menu and uncheck the checkbox for Filter to discard filters.
- Use the drag handle in the bottom rightmost cell to extend the table.
- Now, you may enter the data in the table one by one using the dropdown with colors.
Remove a Drop-Down List
If you want to remove a drop-down list in your worksheet then you can easily remove this.
- Select the cell with the drop-down list.
(You can use the Ctrl+Left click to select them if you have multiple cells with drop-down lists that you want to delete)
- Click Data and then Data Validation.
- In the Settings tab, click to clear all.
- Then Click ok.
If you want to delete all Data Validation, but you don’t know where they are, then you can use the Go To Special dialog. Then you can use Press Ctrl+G > Special, then Data Validation > All or Same, and follow the steps above.
So, using the above methods, you can create a drop-down list in Excel with multiple selections. If you have any question or any problem then contact us. But I hope in this article, I have provided a detailed guide about this.
Frequently Aske Questions
Q: What is a dynamic dropdown?
Ans: Dynamic dropdown menu is a clear method of showing a list of data, allowing users to pick their choice from the list.
Q: What is a conditional drop-down?
Ans: It is a list whose items depend on a selection in another field.