An Excel drop-down list limits a cell to choices you define, which keeps typos and stray values out of your data. Here is how to build one with Data Validation, and what to check when the button won't respond.
Microsoft's support page lists these steps for Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel 2019 and Excel 2016.
Build the drop-down in four steps
- Type your list entries. Put them in a single column or row with no blank cells. Microsoft says it is ideal to put them in an Excel table.
- Select the cell where you want the drop-down to appear.
- Open Data Validation. Go to the Data tab. Under Data Tools (or Tools), select Data Validation (or Validate). The names differ slightly between versions.
- Point the dialog at your list. Use the range that holds your entries. For a short list, you can type the entries directly into the data validation tool instead of using cells.
The cell now offers your entries as choices.
Leave the header out of the range
If your list has a header row, such as "Status" above "Open", "Closed" and "Pending", don't select it as part of the source. According to Microsoft, if you leave the header row out of the source range, the header won't show up as a choice.
Since blank cells aren't allowed in the list, clean the source column first. If it was pasted in from somewhere else, removing duplicates stops the same option from appearing twice.
Typed list or cell range: which to use
Typing entries straight into the dialog is fine for a handful of fixed options, like Yes and No.
For anything longer, or anything you expect to change, keep the entries in cells. Microsoft recommends an Excel table for list items.
Data Validation is greyed out
This is the most common snag. If you can't select Data Validation, Microsoft says the worksheet might be protected or shared. Turn off protection or sharing for that sheet, and the button should become available. Add the drop-down, then turn protection or sharing back on if you need it.
What this guide doesn't cover
These related tasks aren't covered here:
- Dependent drop-downs, where the second list changes based on the first.
- Lists stored on another sheet.
- Character or item limits. Community threads on Microsoft Learn Q&A, like this one, discuss limits on typed lists. Those are user answers, not official documentation.
- Excel for the web, which isn't in the "applies to" list on Microsoft's page.
For these, start with Microsoft's data validation page.
Once the drop-down is filling cells with consistent values, you can match those values against another table with XLOOKUP.
