How to Create A Drop-Down List in Excel?

A dropdown list is a type of list that contains various components and appears in the list only when the user clicks on it. We can say that It is a type of pre-defined list and provides the user an excellent option to choose from it. You can see this type of list on many platforms like online form filling, several websites and apps, excel dashboard, and many more. In this article, we will learn how to create a drop-down list in Excel and some of the interesting customizations you can do with this.  So, let’s get started.

How to Create A Drop-Down List in the Excel sheet?

To create a drop-down list in excel there are possibly three ways to create a list

With the use of Data from Cells

Open the excel sheet and make a similar list of items you want to create for the drop-down list.

Let’s see the steps to create a drop-down list:

1. Choose and select the cell on the sheet where you want to create the drop-down list.
2. Navigate to the Data tools, Data tab, select the Data Validation, here three options will appear, then choose again the Data Validation.
3. When you click on the Data Validation, a dialog box will appear; in which three tabs are there. It includes Settings, Input Message, and Error Alert.
4. Select the settings tab, Validation criteria will open, select the List as the Validation criteria from the list of options.
5. When you select this option a source field will appear to enter as “= A2:A5”, or you can click on the source field and with the help of a mouse select the cells, then click on OK.
6. Now, these will create a drop-down list where you select the cells earlier for using this option. This will provide you a list of options from this list.

NOTE: There is a minor limitation that when you enter a column in the excel sheet and then this will affect the drop-down list also. However, this will just add a blank option to the drop-down list. Also, check that the dropdown option is checked in the cell (usually by default it is checked). If it is unchecked, this will not show the drop-down so you have to enter the value manually.

If you want this drop-down list in multiple cells, select the cells from the sheet where you want to create it and follow the steps same as the above-mentioned steps.

Read More: How To Install Google Chrome In Ubuntu?

Entering the Data Manually

1. Another option we can go through for creating the drop-down list is adding the items directly to the cells in the source field manually.
2. Let us understand it better with an example. If we have two options for the dropdown in a cell that is True or False. So, how you can directly add the options in the data validation source field:
3. Select the cell in which you want to add the drop-down list.
4. Navigate to the Data tools, data tab and select the Data Validation, here three options will appear, then choose again the Data Validation.
5. When you click on the Data Validation, a dialog box will appear; in which three tabs are there. It includes Settings, Input Message, and Error Alert.
6. Select the settings tab, Validation criteria will open, select the List as the Validation criteria from the list of options.
7. As you select the list option, fill the source field by entering here True, False
8. Keep an eye on the dropdown option and check it in the cell.
9. Finally, click on OK.

In this way, the drop-down list will be created in the selected cell. All the items are listed in the different lines in the drop-down menu in the source field and are separated by the comma.

NOTE: For creating this drop-down list in multiple cells at once, select the cells from the sheet where you want to create it and follow the steps same as the above-mentioned steps.

Read More: How to install Python on Windows 10?

With the use of Excel Formulas

If you are looking for another way to create the drop-down list rather than the above-mentioned methods. Another way is using the formula in the source field to create the drop-down list.

We use here such a formula that provides the values for the list, which we can use to create a drop-down list in excel.

Let understand this with an example

1. You can create an excel drop-down list with the help of using the OFFSET function:
2. Firstly, select the cell on the excel sheet where you want to create a drop-down list.
3. Navigate to the Data tools, data tab and select the Data Validation, here three options will appear, then choose again the Data Validation.
4. When you click on the Data Validation, a dialog box will appear; in which three tabs are there. It includes Settings, Input Message, and Error Alert.
5. Select the settings tab, Validation criteria will open, select the List as the Validation criteria from the list of options.
6. When you select the list, the source field will appear in the dialog box.
7. In the source field enter the formula: =OFFSET($A$2,0,0,4)
8. Always check that the cell dropdown option is checked.
9. After doing this, click on OK.

It will create a drop-down list in which all the names of items are included in this list.

NOTE: For creating this drop-down list in multiple cells at one time, select the cells from the sheet where you want to create it and follow the steps same as the above-mentioned steps. Keep the cell references absolute, don’t make them relative.

Read More: How to Convert Pdf to Word?

Conclusion

Many excel users search on the web “How to Create a drop-down list in Excel?” To give a solution to this query we come with this article. In this article, we describe the method to create a drop-down list in the article in three ways.

These three types of methods are creating the list with the use of Data from Cells, using the Excel Formula, and Entering the Data Manually. You can use any of the methods which are suitable for you. I hope you will like this article and find the information useful.

You may also like:

How To Use Zoom On A Computer?

How to Chromecast Sling TV Channels to your TV?

How to Make Skype Calls on TV using Chromecast?