How to add or create a dropdown list in Microsoft Excel?

What do you understand by the term dropdown list in Microsoft Excel?

The term dropdown list in Microsoft Excel is primarily defined as a feature that allows us to choose an option from a given list that appears on the screen when we click on a given cell respectively. It is just like a menu in which we can pick what we want to eat.

Moreover, with a dropdown list, we can limit the options that someone can select to make sure that they have chosen the correct information. Also, we can use it to keep employee attendance records, performs large accounting data entry, or in financial modeling efficiently.

• For Example: we can make use of a dropdown list in Microsoft Excel to mark out the student attendance in a class and reduce the risk of manual entry errors for better understanding.

What are the Key Highlights associated with the Dropdown list in the Microsoft Excel sheet?

The basic Key highlights that are associated with the Dropdown list in Microsoft Excel are as follows:

1. The respective Dropdown lists in Microsoft Excel involve a simple click on the dropdown arrow and offer an easy and efficient way to select the option we desire respectively.
2. And the Dropdown list will reduce the likelihood of making out the typos or errors that can occur with manual entry, improving data accuracy.
3. The use of dropdown lists also helps in the maintainenace of consistency and the accuracy of data, which is especially important for tasks like data analysis and reporting.
4. Whenever we attempt an invalid entry, Microsoft Excel will go on, displaying an error alert message on our screen.
5. Now it is possible to add new values to the created dropdown list by adding them to the list items, giving users greater flexibility and ensuring that the List is always up-to-date.

How can one create a Dropdown List in Microsoft Excel?

To create a dropdown list in Microsoft Excel by making use of the Data Validation, we need to follow the below-mentioned steps carefully:

Step 1: First of all, we need to select one or more cells in which we want the pick list to be getting appeared efficiently, and this could be a single cell, a range of cells, or a whole column as well. And to select multiple non-contiguous cells, we will press and hold out the Ctrl shortcut key from the keyboard.

Step 2: In this step, we will move to the Data tab, which is present in the Data Tools group, and then we will click on Data Validation, respectively.

Step 3: And on the Settings tab of the Data Validation dialog box, we will be performing the following things, which are listed below:

1. Firstly in the given Allow box, we will be selecting the List.
2. And after that, in the Source box, we will type the items separated by a comma with or without spaces. Or else we can select a range of cells on the sheet containing the items.
3. And we should ensure the In-cell dropdown box is checked (default). Otherwise, the dropdown arrow would not appear next to the cell.
4. After that, we will select or clear out the Ignore blank option depending on how we want to handle empty cells.
5. And when we finish the above step, we will click on the OK option respectively.

Moreover, a dropdown list of the comma-separated values works well for small data validation lists that are likely to stay the same. And for the frequently updated lists, it is better to use a range or table for the Source.

Note: It should be noted that to expedite data input in our Microsoft Excel sheets, we can also use a data entry form.

#1: Using List Items

To create a dropdown list using list items, we are required to follow the below-mentioned steps:

Step 1: First, we need to select the entire cell for which we want to create a dropdown list.

Step 2: After that, we must go to the Data option in the respective Excel sheet.

Step 3: Now under the Data Tools group we need to click on the Data Validation (icon)

Step 4: We need to click on Settings.

Step 5: Now in this step, we need to choose the List from the dropdown of the "Allow" section effectively.

Step 6: Specify the Source by selecting the list items (dropdown menu) in the worksheet.

Step 7: After that, we need to click on OK.

Let us now consider the below ExampleExample that will demonstrate the steps mentioned earlier in detail:

• For Example: A particular teacher has a table of students' English test marks and the passing effects, and they need to easily determine which students have passed, just passed, or failed the test conducted by the teacher. And to simplify this task, the teacher wants to create a dropdown menu for selecting the appropriate result for every student who appeared in the test respectively.

Solution for the above ExampleExample:

Step 1: First, we need to write down the dropdown menu options in column G respectively.

Step 2: After that, we need to click on the cell where we want to insert a dropdown list, and then we need to move to the Data > present under the Data Tools will click on the Data Validation dropdown> Data Validation efficiently:

And the Data Validation dialogue box will get easily displayed on our screen.

Step 3: Now, in this step, we will be clicking on Settings > choose List from the dropdown of Allow option respectively:

Step 4: After that, we will enter the Source by selecting the "Dropdown Menu" in the given worksheet as well.

Step 5: After that, we must click the OK option.

Note: It should be noted that the Source is the List of items or the Dropdown Menu that we want to add to the dropdown list, respectively.

Now this will insert a dropdown arrow adjacent to the selected cell effectively.

Step 6: Now, we will click the dropdown arrow to view the List of items.

Outcome: Now the values Pass, Fail, and Just Passed are added as the dropdown list items, and we can choose the desired item to fill in the cell effectively.

Step 7: In this step, we will drag the cross (+) to the desired row to add the dropdown list to the remaining cells.

This will be copying out the feature and inserting a dropdown list till the last cell effectively.

Step 8: Now in order to indicate a student's test result, which is primarily based upon their marks in English subject, we will be selecting the Pass from the dropdown list if they scored above 35 marks, and we will be selecting out the Just Passed if they scored 35 marks, and will select Fail if they scored less than 35 marks respectively.

#2: By making use of the Comma-Separated Values

If in case we need to create a dropdown list in Microsoft Excel by making use of the Comma-separated value method, we need to follow the given steps respectively:

Step 1: First, we need to select the entire cell for which we want to create a dropdown list.

Step 2: Afterward, we must go to the Data option in the respective Excel sheet.

Step 3: Now, under the Data Tools group, we need to click on the Data Validation (icon)

Step 4: We need to click on Settings.

Step 5: In this step, we must choose the List from the dropdown of the "Allow" section effectively.

Step 6: Specify the Source by selecting the list items (dropdown menu) in the worksheet respectively.

Step 7: After that, we need to click on OK.

Now let us assume the below Example, which demonstrates these steps in detail:

For Example: The table below depicts a list of questions that candidates appearing for an interview must answer. And for that we want to create a dropdown list that would allow the candidates to choose from three options- Yes, No, or May Be respectively:

Solution:

Step 1: First, we need to click on the desired cell on which we need to perform the action.

Step 2: After that, we need to go to the Data > and then click on the Data Validation dropdown under the Data Tools group > under the Data Validation as well.

And soon after that, the Data Validation dialogue box will appear on the screen efficiently.

Step 3: Now, we will click on Settings > and then choose the List from the Allow dropdown.

Step 4: After that in the Source section, we will be entering out the value that is none other than: Yes, No, Maybe

Note: It should be noted that this respective method is called Comma-delimited because we manually provide the list items separating each with a comma as well.

Step 5: Now, after that, we need to click on the OK option.

And this will then insert a dropdown arrow adjacent to the selected cell respectively.

Step 6: We will click the dropdown arrow to view the list items- Yes, No, and May Be.

Step 7: Now, after that, we will be dragging the cross (+) sign to the last row in order to insert the dropdown list into the remaining cells as well.

And Microsoft Excel adds up the dropdown feature to all the rows respectively.

Step 8: Now, to fill the data table, we will choose the appropriate answer from the dropdown list.

#3: Creation of the Dependent Dropdown List

For the creation of a dependent dropdown list, we need to follow the below-mentioned steps:

Step 1: First, we need to select the cell that contains the main dropdown list for which we want to create.

Step 2: Then, after that, we need to go to the Data option.

Step 3: Now, under the Data Tools group, we will also be clicking on Data Validation (icon).

Step 4: After that, we will click on Settings respectively.

Step 5: In this step, we will be efficiently choosing out the List from the dropdown of the "Allow" section.

Step 6: After that, we will specify the Source by selecting the list items (dropdown menu) in the worksheet.

Step 7: After that, we will click on the OK option.

Step 8: Now, we need to select the cell where we want to create a dependent dropdown list.

Step 9: In this step, we need to go to the Data option.

Step 10: After that, we need to click on Data Validation (icon) under the Data Tools group.

Step 11: And then we need to click on Settings.

Step 12: Then, we will choose the List from the dropdown of the "Allow" section.

Step 13: Under the Source section, type the formula =INDIRECT (cell containing main dropdown list) respectively.

Step 14: Finally, we need to click on the OK option.

Now the below Example will demonstrate the above steps in detail:

For Example: In the below-mentioned table, we have chosen out the 3 countries- India, the United States, and Malaysia along with their popular cities as well, and we want to create a dropdown list so that whenever a user chooses a specific country, it will be displaying out the respective cities in the List respectively:

Solution:

Step 1: First of all, we will be clicking on the cell that is C14 to create a dropdown list of the countries like India, the United States, as well as Malaysia

Step 2: Now, in this step, in the Microsoft Excel ribbon, we will go to the Data > under Data Tools group and click on the Data Validation (icon) > Data Validation as well.

And then a Data Validation dialogue box will get appears on the screen respectively.

Step 3: After that, we will click on Settings > choosing out the List from the dropdown of Allow and specify the Source by just selecting the countries (row 5) in the worksheetas well:

Step 4: Now, we will be clicking on the OK option.

Step 5: As this will be creating out a dropdown list of the country names respectively:

Step 6: To create a dependent dropdown list of cities, we will select cell B14.

Step 7: Now, after that, we need to go to Data > under Data Tools, then we will click on the Data Validation dropdown> Data Validation.

A Data Validation dialogue box will get on the screen.

Step 8: In this step, we will click on Settings > choosing out the List from the dropdown of Allow option respectively.

Step 9: And to specify the Source, enter the formula:

=INDIRECT ($A$14) and clicking on the OK option.

Explanation: Here, we want the dropdown list (of cities) to change as per the country. It means the dropdown list will depend upon the other dropdown list. Therefore, it is known as a dependent dropdown list.

So, to insert a dependent dropdown list, we use the function INDIRECT followed by the cell with country names in closed brackets.

Output: A dynamic dropdown list of cities will be getting added in cell B14.

Each time we choose a different country, the dependent dropdown displays a list of cities belonging to that country respectively.

What is the important thing that needs to be remembered by an individual while working with the dropdown list in Microsoft Excel?

The important things that need to be remembered by an individual while working with the creation of the dropdown list in Microsoft Excel are as follows:

1. While creating a dropdown list, an individual can efficiently add a message from the Input Message tab in the Data Validation dialogue box, as it will display a message (instruction) whenever a user clicks on the respective cell.
2. And an individual must remember to keep the List size reasonable and consider using the scroll bars or combo boxes for longer lists.
3. And an individual needs to ensure that the data source for the dropdown list is accurate and up-to-date.
4. Moreover, an individual must consider the customization of our dropdown lists with sorting options, color coding, or other formatting changes.
5. Remember that the dropdown lists may only work in some versions of Microsoft Excel or with all other software programs. Therefore, test our lists for compatibility with intended users and applications too effectively.