What is Drop-Down List in Excel?

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 ControlExcel 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 boxWith “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 validationData ValidationThe 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.

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 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 functionINDIRECT FunctionThe INDIRECT excel function is used to indirectly refer to cells, cell ranges, worksheets, and workbooks.read more.

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

Alternatively, use the shortcut key “Alt+A+V+V” to access the “data validation” dialog box.

We have chosen the “information” icon.

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

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 these useful Excel tools –

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.

  • How to Edit Drop-Down List?Excel Drag and DropExcel Drag And DropExcel Drag and Drop, also known as “Fill Handle”, is the PLUS (+) icon that appears when we move mouse or cursor to the right bottom of the selected cell. Using this plus icon we can drag to the left, to the right, to the top and also to the bottom from the active cell. read moreCalendar Drop-Down in ExcelCalendar Drop-Down In ExcelThe calendar drop-down in Excel is an effective way of ensuring correct data entry and records, and it can be created with the data validation option to ensure error-free use.read moreScroll Bars in ExcelScroll Bars In ExcelIn Excel, there are two scroll bars: one is a vertical scroll bar that is used to view data from up and down, and the other is a horizontal scroll bar that is used to view data from left to right.read more