Create a drop-down list in Excel to restrict data

Content
  1. Limit the data that can be entered in a cell
  2. Create drop-down list
  3. Remove drop-down list in Excel

Limit the data that can be entered in a cell

Create drop-down lists or menus in Excel to limit the data that can be entered in a specific cell to a predefined list of items. Using a data validation drop-down list simplifies data entry, prevents data entry errors, and reduces data entry places.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Mac.

Create drop-down list

The data added to the drop-down list may be in the same table in the list, or in another table in the same workbook, or in a completely different Excel workbook.

In this guide, the types of cookies that can be selected from the drop-down list are limited to the approved list. Before following the guide, enter the data in columns D and E as shown in the figure below.

To create a drop-down list:

  1. Select cell B3 to make it active.

  2. Select Facts

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

  4. Select tab Settings

  5. In the Allow section, select the Down arrow.

  6. Select List

  7. Place your cursor in the “Source” text box.

  8. Highlight cells E3 E10 on a worksheet to add data from that range of cells to the list.

  9. Select Okay † Except for Excel for Mac where you choose Finished

A down arrow will appear next to cell B3, indicating that there is a drop-down list. Selecting the down arrow opens a drop-down list with eight cookie names.

The down arrow for a drop-down list is only visible if that cell becomes the active cell.

Remove drop-down list in Excel

When you are done with the drop-down list, remove it from the worksheet cell using the data validation dialog box.

If you move the drop-down list to a new location on the same worksheet, you don’t need to delete and recreate the drop-down list. Excel dynamically updates the range of data used for the list.

  1. Select cell One that contains the drop-down list to be removed.

  2. Select Facts

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

  4. Select tab Settings

  5. Select delete everything to remove the drop-down list.

  6. Select Okay to close the dialog box and return to the worksheet.

To remove all drop-down lists on a sheet, check the box next to Apply these changes to all other cells with the same settings † You can find it in the Settings tab of the Data Validation dialog box.

Leave a Reply

Your email address will not be published.