A drop-down list can turn a free-text field into a controlled choice. That is useful for statuses, departments, priorities, categories and other values where inconsistent typing would make later filtering or reporting harder.
Create a basic drop-down list
- Put the allowed values in a worksheet column.
- Select the cell or range where users should choose a value.
- Go to Data > Data Validation.
- Under Allow, choose List.
- Select the source range and make sure the in-cell dropdown option is enabled.
- Test both a valid selection and an invalid entry.
Microsoft recommends using an Excel Table as the source when appropriate because adding or removing items can automatically update associated drop-downs.
Example: order status
Create a list containing:
| Status |
|---|
| New |
| Processing |
| Shipped |
| Cancelled |
Apply Data Validation to the Status column. Users can then choose a valid status instead of typing slightly different versions such as Shipped, shipped or Ship.
Use a table as the source
If the list of choices changes over time, storing the source in an Excel Table can reduce maintenance. Add a new option to the table and test whether the drop-down updates as expected in your workbook.
Configure an error alert
Data Validation can display an error message when someone enters an invalid value. Use Stop when invalid entries should be blocked, or a less restrictive warning when the workflow needs to allow exceptions.
When a drop-down is not enough
A drop-down controls entry, but it does not automatically solve every data-quality problem. Users can still paste values into cells or work around a validation rule depending on the workbook. For important operational sheets, combine validation with sensible protection, review and downstream checks.
Common mistakes
- Including the header: Do not include the heading in the list of selectable values.
- Hard-coding a list that changes often: Use a table or managed source range when options are maintained regularly.
- Ignoring blanks: Decide whether blank entries are acceptable.
- Not testing invalid input: Check that the error behavior matches the workflow.
- Expecting validation to clean old data: A new rule does not automatically correct inconsistent values already in the sheet.
Quick answer
Select the target cells, choose Data > Data Validation > List, and point the Source to the allowed values. For lists that change regularly, consider storing the choices in an Excel Table.
Related Excel guides
For sorting and filtering controlled data, see How to Sort and Filter Data in Excel. For cleanup before applying validation, see How to Clean Data in Excel.