How to Create and Remove a Drop-Down List in Excel

Limit the data that can be entered in a cell with a drop-down list

A blank Excel worksheet

Ted French

Drop-down lists or menus can be created 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 offers benefits that include:

  • Making data entry easier
  • Prevent data entry errors
  • Restrict the number of locations for entering data

List and Data Locations

The data that is added to a drop-down list can be located on:

Steps to Creating a Drop-Down List

Enter Data with a Drop Down List in Excel

Ted French

The steps used to create the drop-down list shown in cell B3 (cookie types) in the accompanying example image are:

  1. Click on cell B3 to make it the active cell.
  2. Click on the Data tab of the ribbon.
  3. Click on Data Validation to open the drop-down menu of validation options.
  4. In the menu, click on Data Validation to bring up the Data Validation dialog box.
  5. Click on the Settings tab in the dialog box.
  6. Click on the Allow option in the dialog box to open the drop-down menu. The default value is Any value.
  7. In this menu, click List.
  8. Click on the Source line in the dialog box.
  9. Highlight cells E3 through E10 in the worksheet to add the data in this range of cells to the list.
  10. Click OK to close the dialog box and return to the worksheet.
  11. A down arrow appears next to cell B3 indicating the presence of the drop-down list.

When you click on the arrow, the drop-down list opens to display the eight cookie names.

Note: The down arrow indicating the presence of a drop-down list is only visible when that cell is made the active cell. 

Remove a Drop-Down List in Excel

Remove a Drop Down List in Excel

Ted French

When you are finished with a drop-down list, it can be easily removed from a worksheet cell using the data validation dialog box as shown in the accompanying image.

Note: If you are moving the drop-down list to a new location on the same worksheet, it is not necessary to delete and recreate the drop-down list, as Excel will dynamically update the range of data used for the list.

To remove a drop-down list:

  1. Click on the cell containing the drop-down list to be removed.
  2. Click the Data tab of the ribbon.
  3. Click the Data Validation icon on the ribbon to open the drop-down menu.
  4. Click the Data Validation option in the menu to open the Data Validation dialog box.
  5. In the dialog box, click on the Settings tab.
  6. Click the Clear All button to remove the drop-down list.
  7. Click OK to close the dialog box and return to the worksheet.

The selected drop-down list is removed from the selected cell, but any data entered into the cell before the list was removed remains and must be deleted separately.

To Remove All Drop Down Lists on a Worksheet

To remove all the drop-down lists located on the same worksheet at one time:

  1. Click on the cell containing the drop-down list to be removed.
  2. Click the Data tab of the ribbon.
  3. Click the Data Validation icon on the ribbon to open the drop-down menu.
  4. Click the Data Validation option in the menu to open the Data Validation dialog box.
  5. In the dialog box, click on the Settings tab.
  6. Check the Apply these changes to all other cells with the same settings box on the Settings tab of the dialog box.
  7. Click on the Clear All button to remove all drop-down lists on the current worksheet.
  8. Click OK to close the dialog box and return to the worksheet.