Create a Drop-Down List in Excel to Restrict Data

Software

by Staff Author

Updated May 27, 2019

Create drop-down lists, or menus, in Excel to limit the data that can be entered into a specific cell to a preset list of entries. Using a drop-down list for data validation makes data entry easier, prevents data entry errors, and restricts the number of locations for entering data.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Mac.

Create a Drop-Down List

The data that is added to a drop-down list can be located on either the same worksheet on the list, on a different worksheet within the same workbook, or in a completely different Excel workbook.

In this tutorial, the cookie types that can be selected in a drop-down list will be limited to an approved list. Before following along with the tutorial, enter the data in columns D and E shown in the image below.

To create a drop-down list:

Select cell B3 to make it the active cell.
Select Data.
Select Data Validation to open the Data Validation dialog box.
Select the Settings tab.
Under Allow, select the down arrow.
Choose List.
Place the cursor in the Source text box.
Highlight cells E3 through E10 in the worksheet to add the data in this range of cells to the list.
Select OK. Except for Excel for Mac, where you select Done.

A down arrow appears next to cell B3 indicating the presence of the drop-down list. When you select the down arrow, the drop-down list opens to display the eight cookie names.

A screenshot showing a drop-down list in Excel

The down arrow for the drop-down list is only visible when that cell is made the active cell.

Remove a Drop-Down List in Excel

When you're finished with a drop-down list, remove it from a worksheet cell using the data validation dialog box.

If you move a drop-down list to a new location on the same worksheet, it is not necessary to delete and recreate the drop-down list. Excel dynamically updates the range of data used for the list.

Select the cell containing the drop-down list to be removed.
Select Data.
Select Data Validation to open the Data Validation dialog box.
Select the Settings tab.
Select Clear All to remove the drop-down list.
Select OK to close the dialog box and return to the worksheet.

To remove all drop-down lists on a worksheet, place a check mark next to Apply these changes to all other cells with the same settings. You'll find it on the Settings tab of the Data Validation dialog box.