How to Create/Remove a Drop Down List in Excel


Drop-down lists or menus can be created in Excel to limit the data that can be entered into a specific cell to a pre-set list of entries. The benefits of using a drop-down list for data validation include:

  • making data entry easier;
  • preventing data entry errors;
  • restricting 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:

  1. the same worksheet as the list.
  2. on a different worksheet in the same Excel workbook.
  3. in a different Excel workbook.

Steps to Creating a Drop Down List

Enter Data with a Drop Down List in Excel
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 image above 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 – 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 - 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 should be present next to cell B3 indicating the presence of the drop-down list;
  12. When you click on the arrow the drop-down list will open 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
Remove a Drop Down List in Excel. © Ted French

Once 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 image above.

Note: If moving the drop-down list or source data to a new location on the same worksheet, it is usually not necessary to delete and re-create 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 - if necessary;
  6. Click the Clear All button to remove the drop-down list as shown in the image above;
  7. Click OK to close the dialog box and return to the worksheet.

The selected drop down list should now be removed from the selected cell, but any data entered into the cell before the list was removed will remain and must be deleted separately.

To Remove all Drop Down List on a Worksheet

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

  1. Carry out steps one through five in the directions above;
  2. Check the Apply these changes to all other cells with the same settings box on the Settings tab of the dialog box;
  3. Click on the Clear All button to remove all drop-down lists on the current worksheet.
  4. Click OK to close the dialog box and return to the worksheet.