How to Create a Drop-Down List in Excel
Put allowed values such as Open, In progress and Done in an unobstructed worksheet column. Select the cell or cells where people should choose a status, open Data → Data Validation, set Allow: List, and point Source to the items.
Quick answer: Put allowed values such as Open, In progress and Done in an unobstructed worksheet column. Select the cell or cells where people should choose a status, open Data → Data Validation, set Allow: List, and point Source to the items. Confirm that In-cell dropdown is enabled when the option is shown, then test an entry. The exact dialog varies across Windows, Mac and Excel for the web.
A drop-down is primarily a data-entry control. It does not create a secure permission barrier and may not prevent pasted values in every workflow. You should still review imported data for invalid values.
Method 1: Use a range of worksheet cells
- On a helper sheet named
Lists, write one choice per row inA2:A4:Open,In progress,Done. - In the working sheet select the target range, for example
D2:D200, where each task’s status belongs. - Open Data → Data Validation. On the Settings panel choose List as the allowed data type.
- Click into Source and select your list range, or type a valid absolute reference such as
=Lists!$A$2:$A$4if supported by the client. For cross-sheet sources some clients prefer a named range: create a name for the source cells first if direct selection is rejected. - Check any option labeled In-cell dropdown and choose what should happen when someone types invalid data. Use an explanatory input message if useful.
- Confirm with OK, click a target cell and open the drop-down arrow. Select an item and verify the value appears exactly as intended.
Microsoft recommends storing source items in an Excel table if possible, so adding or removing options can flow into a list built from that table. For a workbook used repeatedly, keeping validation values on a clearly named helper sheet is easier to maintain than hiding them inside a long formula.
Method 2: Enter a short list directly
Instead of selecting a range, type values directly into the Data Validation Source box, such as High,Medium,Low. The list separator shown in documentation may vary with regional settings. If all choices appear as one item, use the separator expected by your Excel locale or switch to a worksheet-source list. Use direct values for short, stable lists rather than departments that change weekly.
Change a list without breaking existing entries
If your source is a normal cell range, edit those cells and ensure the referenced range still covers all items. For a growing list, use a documented Excel table source or appropriately maintained named range. If you rename In progress to Active, existing cells containing the old text will not necessarily update automatically—review them separately.
In Excel for the web, Microsoft notes restrictions on editing some drop-down sources, particularly named or range-based sources compared with manually typed lists. When a command or name manager is missing, use an available desktop Excel client instead of inventing web-only controls.
Troubleshooting
No arrow appears. Select a cell with validation, check that the validation rule still applies to the range, and confirm In-cell dropdown is enabled.
Invalid text is accepted. Check your Error Alert settings and whether the value was pasted rather than selected. Data validation is not an access-control system.
A new option is missing. Extend the source range or update the named range/table and reopen the target cell.
The feature is grayed out. Protected sheets or certain shared-workbook states can restrict validation editing; Microsoft documents this limitation.
Frequently asked questions
Can I create a Yes/No selector?
Yes. Type Yes,No directly into Source or use a two-cell source range.
Can I add a different color to each choice?
Use a separate conditional-formatting rule; data validation by itself defines permitted input, not formatting.
Will the list force colleagues to choose accurately?
It reduces typing variation, but you still need workflow review, protected areas where appropriate and data-quality checks.
Sources & Further Reading
- Microsoft — Create a drop-down list — List validation, source table, error alerts and protected sheet caveat.
- Microsoft — Add or remove items from a drop-down list — Editing list source and Excel web limitations.
Use the checklists as you go, and confirm the result before moving on to a more involved repair.