Drop-down List in Excel

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:

With “data validation”
With “Form controlForm Control Excel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars.read more” combo box
With “ActiveX control” combo box

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?

You can download this Drop Down List Excel Template here – Drop Down List Excel Template

The drop-down list is also known by the name “data validationData Validation The data validation in excel helps control the kind of input entered by a user in the worksheet.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 #1–Static 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
• Stop

The 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 #2–Dynamic Drop-Down List

A dynamic 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 tablesExcel Tables In 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 functionINDIRECT Function The INDIRECT excel function is used to indirectly refer to cells, cell ranges, worksheets, and workbooks.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 rangesNamed Ranges Name 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)

1. Define a drop-down list in Excel and state the benefits of creating it.

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.

2. How to create/add a drop-down list in Excel?

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.

3. What is a dependent drop-down list in Excel?

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.

4. What is a cascading drop-down list?

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.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Drop-down List in Excel

What is Drop-Down List in Excel?