How to Create a Drop-Down List in Excel to Restrict Data

Limit the data that can be entered in a cell

Boom barriers in car park
Jorg Greuel / Getty Images

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. When you use a drop-down list for data validation, data entry is easier, data entry errors are avoided, and data entry is restricted to allowed input only.

Note: The information is this article applies to Excel versions 2019, 2016, 2013, 2010, Excel Online, and Excel for Mac.

Create a Drop-Down List

Screenshot of Excel showing how to select a source for data validation

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 our example, we will limit the cookie types that can be selected in a drop-down list to our approved list. If you want to follow along with our example, open a blank Excel worksheet and enter the data shown in the image above. Make sure you enter the data in columns D and E.

Follow these steps used to create the drop-down list in cell B3:

  1. Select cell B3 to make it the active cell.
  2. Select the Data tab.
  3. Select Data Validation.
  4. Select the Settings tab in the dialog box.
  5. Under the Allow option in the dialog box, select the drop-down menu.
  6. In the menu, select List.
  7. Place the cursor in the Source line in the dialog box.
  8. Highlight cells E3 through E10 in the worksheet to add the data in this range of cells to the list.
  9. Select OK to close the dialog box and return to the worksheet.

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

The down arrow that indicates the presence of a drop-down list is only visible in active cells.

Remove a Drop-Down List in Excel

Screenshot of Excel showing how to clear data validation settings

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

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

  1. Select the cell containing the drop-down list to be removed.
  2. Select Data.
  3. Select Data Validation.
  4. In the dialog box, select the Settings tab.
  5. Select Clear All to remove the drop-down list.
  6. Select OK to close the dialog box and return to the worksheet.

To remove all drop-down lists on a worksheet, place a check next to Apply these changes to all other cells with the same settings.