How to Create a Drop-Down List in an Excel Cell
Drop-down lists in Excel 2013 and 2010 provide a list of acceptable entries for a particular cell, assisting remote users in properly filling out a form you create. If a user types in the cell rather than using the list, Excel checks the contents against the list and displays an error message if the contents aren't allowed. To display a drop-down list, you first need to enter the list items into a range of cells, and then set up data validation on the target cell.
Enter the items you want in the drop-down list anywhere in your workbook, one per cell. Select the entire list and assign it a name in the Name Box.
Click the cell where you want the list to appear and press "Data Validation" in the Data Tools section of the "Data" tab.
Choose "List" in the Allow box and make sure "In-cell Dropdown" is checked. Type the name you assigned your list, preceded by an equals sign, into the Source box.
Enter a message on the "Input Message" tab to display a notification whenever someone clicks the cell. This message can contain any text, such as to present the list of options to users who immediately start typing in the cell rather than checking the drop-down list.
Pick an error style on the "Error Alert" tab and write a message to display if a user enters an invalid choice. The "Stop" style prevents users from entering options not on the list, whereas the "Warning" and "Information" styles allow the user to override the error.
Click "OK" to create the list. Select the list's cell and open the drop-down menu to pick from the available options.
Tips & Warnings
- To adjust the range of cells that appear in the drop-down list, press "Name Manager" on the "Formulas" tab, choose the name you assigned your list and edit the cell range in the "Refers to" box.
- Prevent users from editing the list options by placing the cell range containing the options on a separate worksheet from the drop-down list, and then protecting that sheet: After creating the list, open the "File" menu, click "Info," "Protect Workbook" and "Protect Current Sheet." Enter a password to lock the sheet. Right-click the sheet name and choose "Hide" if you also want to prevent users from seeing the list.
- Tables linked to SharePoint sites can't contain drop-down lists. To add a list, change the table into a range of normal cells by right-clicking the table, choosing "Table" and clicking "Convert to Range."
References & Resources
- Office Online: Create a Drop-Down List
- Office Online: Add or Remove Items From a Drop-Down List
- Office Online: Protect Your Document, Workbook, or Presentation With Passwords, Permission, and Other Restrictions
- Office Online: Hide or Show Worksheets or Workbooks
- Office Online: Convert a Table to a Range