How to Make Drop-Down List in Excel with Multiple Selections

How to make Drop-Down list in Excel with multiple selections? 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.

Dynamic Drop-Down List 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?

Create a Drop Down list in Excel

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 without VBA 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 create a drop-down list but show different values in Excel?

1. Choose the cells in which you want the lists to appear.
2. Click DATA > Data Validation on the ribbon.
3. Set Allow to List in the dialogue box.
4. To create a comma-delimited list for your drop-down menu, select Source, then enter the text or numbers you wish to appear in it. Then click OK.

How do I create a drop-down list with an IF formula?

Excel drop-down list formula illustration

• With Excel, make a customized drop-down list with nested IF statements.
  On C2, click.
• Choose Data > Data Tools > Data Validation from the Ribbon.
• Range F2:I2 should then be chosen after choosing List from the Allow drop-down menu.

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.

1. 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. 
2. 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.
3. 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.
4. 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.
5. In the Create dialog box, select the data and check the checkbox for My table and click OK.
6. Now, navigate to Table Design in the menu and uncheck the checkbox for Filter to discard filters.
7. Use the drag handle in the bottom rightmost cell to extend the table.
8. Now, you may enter the data in the table one by one using the dropdown with colors.

Excel Drop-Down Multiple Select or Remove

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.

Read Also, Fallout Shelter Pro APK Latest Version Download Free 2023

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.

Conclusion

It is all about how to create a drop-down list in excel. I hope in this article, I have provided a detailed guide about this. So, using the above methods, you can create a drop-down list in Excel with multiple selections.