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

Limit the data that can be entered in a cell

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on January 21, 2021

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.

Data used in the tutorial to create a drop-down list in Excel

To create a drop-down list:

  1. Select cell B3 to make it the active cell.

  2. Select Data.

  3. Select Data Validation to open the Data Validation dialog box.

  4. Select the Settings tab.

  5. Under Allow, select the down arrow.

  6. Choose List.

    A screenshot showing the Data Validation dialog box in Excel

  7. Place the cursor in the Source text box.

  8. Highlight cells E3 through E10 in the worksheet to add the data in this range of cells to the list.

    A screenshot showing how to select data for a drop-down list in Excel

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

  1. Select the cell containing the drop-down list to be removed.

  2. Select Data.

  3. Select Data Validation to open the Data Validation dialog box.

  4. Select the Settings tab.

  5. Select Clear All to remove the drop-down list.

    A screenshot showing how to delete a drop-down list in Excel

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

Was this page helpful?