What is Drop-Down List in Excel?
A drop-down list in excel is a pre-defined list of inputs that allows users to select an option.In simple terms, the response that the user can submit is limited to the options presented by the drop-down list. This prevents the user from typing manual entries, thereby reducing the occurrence of a garbage value in the data.
For example, to answer a set of questions in an online survey, the options provided in the drop-down list are yes and no. The user is expected to select any one of these answers. This prevents the user from selecting options other than the listed ones.
In the absence of a drop-down excel list, there are chances of typing an incorrect response in the data file. For example, the name Ravish, is incorrectly typed as Ravish , with an extra space at the end. Such cell entries return an error on applying the formula in Excel. The usage of the drop-down list ensures that the input matches the correct spelling.
In Excel, the user can create/add a drop-down list using the following ways:
This article discusses the creation of a drop-down list using the data validation option.
How to Create/Add a Drop-Down List in Excel?
The drop-down list is also known by the name data validationData validation in Excel is used to limit a user's inputs to specified cells or input ranges by offering pre-defined inputs for selection to avoid multiple redundant data inputs.read more. The following image shows the data validation option under the Data tab.
Let us understand how to create a drop-down list with the help of the following examples.
Example #1Static Drop-Down List
The succeeding table provides the names of cities in column A. The revenue earned by the different offices of an organization is shown in column B. We want to create a drop-down list of the cities in the cell D2.
The steps to create/add the static drop-down list in Excel are stated as follows:
- Select cell D2 in the Excel sheet.
- Click data validation drop-down from the Data tab of Excel. Choose the option data validation, as shown in the image below.
Alternatively, use the shortcut key Alt+A+V+V to access the data validation dialog box.
- The data validation window appears as shown in the succeeding image.
- In the Settings tab, choose list from the drop-down menu of the allow option.
- Select the range of cities in the source box, as shown in the succeeding image.
- Click Ok to create the drop-down list in the cell D2. The output is shown in the following image.
- Enter a value in cell D2. It shows the result, the value you entered is not valid.
- In Excel, we can modify the message displayed to the users on entering manual values. For this, select cell D2. Press the shortcut key Alt+A+V+V to access the data validation box. Click the input message tab.
- Type you cannot enter the values in the title box and please select from the list in the input message box. Click Ok.
- On selecting cell D2, the user will view the information entered in step 9, as shown in the following image.
- Select cell D2 and press the shortcut key Alt+A+V+V to access the data validation window. Click the error alert tab.
- Select any one icon among the following style options.
Information
Warning
StopThe succeeding image shows the specified style icons.
We have chosen the information icon.
- In the title box, type error in entry. In the error message box, type you are not allowed to enter the values manually in this cell. Click Ok.
- On entering the data manually, the error message created in the step 13 is displayed, as shown in the following image.
Example #2Dynamic Drop-Down List
Adynamic drop-down list extends on adding entries to the source range. It is formed as the number of entries at the end of the list increases. It can be created using the Excel tablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more and the INDIRECT functionThe indirect function in Excel is an inbuilt function that is used to reference and obtain cell values from a text string.read more.
Working on the data of example #1, let us add the names of two more cities, Napier and Geneva, at the end of the list. Create a dynamic drop-down list in cell D5.
The following table shows the updated list of cities and the revenue earned by the different offices of an organization in columns A and B, respectively.
The drop-down list in cell D2 lists data up to the city Cape Town. It does not show the data for the two additional cities shown in the following image.
To update the drop-down list, we need to create named ranges in Excel. The steps to create named rangesName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more are listed as follows:
Step 1: Click name manager in the Formulas tab of Excel.
Step 2: Select the new option in the name manager window.
Step 3: The new name window opens. Type drop_down_list in the name box and apply the formula in refers to box, as shown in the image. Click Ok.
Step 4: Select cell D5 and press the shortcut key Alt+A+V+V to access the data validation window. Choose list in the allow option of the data validation window.
Step 5: In the source box, enter the name typed in the name box in step 3.
Note: Alternatively, use the shortcut key Ctrl+F3 to access the name manager. From this, the user can enter the desired name for the list.
Step 6: Enter the two cities, Haryana and Colombo at the end of the list. Click the drop-down list in column D.
Thus, in the dynamic drop-down list, the user can view the updated list of cities.
The succeeding image shows the dynamic drop-down list with the updated cities.
Benefits of a Drop-Down List in Excel
The benefits of using the Excel drop-down list are stated as follows:
- The user can select an entry from a range of values, instead of entering manual responses.
- The drop-down list can be copied and pasted to any of the cells in the worksheet.
- A dependent drop-down list helps meet the specific requirement.
Frequently Asked Questions [FAQs]
The drop-down list contains pre-defined inputs or parameters for a user to choose from. It is a data validation function where a user is expected to choose an entry from the limited responses.
A drop-down list can be static or dynamic.
A static drop-down list is created when the number of choices is limited and not much change is expected in the entries over time.
A dynamic drop-down list is used when there is a long list of choices, and the entries undergo a change over time.
The benefits of the drop-down list include:
1. It improves the accuracy of the input entries.
2. It occupies less space in the worksheet and contains a lot of information.
3. It prevents the users from typing manual entries.
4. The dependent drop-down list meets the specific requirements of the user.
The following steps help to create a drop-down list in Excel:
1. Create a vertical list of options from which the users need to choose.
2. Select a specific cell in Excel to create the drop-down list. [The user can create a drop-down list in a single cell or multiple cells.]
3. Choose data validation from the Data tab of the Excel ribbon.
4. Select list from the drop-down list of the allow option.
5. Click the source option and enter the range of cells containing the vertical list of options [created in step 1] in Excel. The range reference is displayed in the source box.
6. Click Ok.
The user can view the drop-down list in the specific cell.
In a dependent drop-down list, a list of values of one drop-down list depends on a value in another drop-down list.
For example, if the user selects the option cuisine in one drop-down list, the cuisine types in the succeeding drop-down list are displayed. This cuisine type is presented by options like Chinese, Thai, Italian, and Greek.
It can be created with the help of the INDIRECT function and the named ranges.
A cascading drop-down list is a chain of dependent drop-down list controls. Here, one drop-down list is controlled by the previous [or parent] drop-down list.
An entry in a drop-down list control is populated based on a new entry chosen from another drop-down list control.
Recommended Articles
This has been a step-by-step guide to the drop-down list in Excel. Here we discuss how to create a drop-down list [static and dynamic list] using examples and downloadable templates. You may also look at theseuseful Excel tools
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion