- Limit the data that can be entered in a cell
- Create drop-down list
- 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:
-
Select cell B3 to make it active.
-
Select Facts †
-
Select Data Validation to open the Data Validation dialog box.
-
Select tab Settings †
-
In the Allow section, select the Down arrow.
-
Select List †
-
Place your cursor in the “Source” text box.
-
Highlight cells E3 † E10 on a worksheet to add data from that range of cells to the list.
-
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.
-
Select cell One that contains the drop-down list to be removed.
-
Select Facts †
-
Select Data Validation to open the Data Validation dialog box.
-
Select tab Settings †
-
Select delete everything to remove the drop-down list.
-
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.