What to Know
- Enter the data you want to appear in the drop-down list. It can be in the same worksheet or another one.
- Select the cell where you want the drop-down list to appear, and then select Data > Data Validation > Settings > Allow > List.
- Click the Source box, select your list range, and select OK. To remove, go to Data > Data Validation > Settings > Clear All.
This article explains how to 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 errors, and restricts the number of locations for entering data. Instructions cover Excel 2019, 2016, 2013, 2010; and Excel for Mac.
Create a Drop-Down List
The data added to a drop-down list can be located on either the same worksheet as the list, on a different worksheet in the same workbook, or in a completely different Excel workbook. In this tutorial, we're using a list of cookie types. To follow along, 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.
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.