Drop-down List in Excel

Publication Date :

06 Jul, 2021

Blog Author :

Edited by :

Reviewed by :

Download FREE Drop-down List Excel Template and Follow Along!

Drop-down List Excel Template.xlsx

Table Of Contents

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 control” 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?

The drop-down list is also known by the name “data validation.” 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 tables and the INDIRECT function.

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 ranges 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.