Create an Excel Drop-Down List with Data Validation
On this page
A drop-down list helps people choose consistent categories. It is useful for task statuses and product groups, but it does not prove that existing records are correct. You still need clear field definitions and checks for imported or pasted data.
Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.
Build the source list
Enter Allowed status in H1 and Not started, In progress and Done in H2:H4. In A1 type Task and in B1 type Status. Add Draft outline, Review notes and Publish guide in A2:A4.
Keep the allowed list free of blank rows and duplicate labels. Use categories that describe the process rather than collecting every spelling from an inconsistent old file. Decide what each status means before asking people to choose it.
Apply the validation rule
Select B2:B20. Open Data Validation from the Data tab, choose a List rule and set the source to =$H$2:$H$4. Enable the in-cell drop-down if available, then apply the rule. Dialog labels vary between versions; the essential choices are target range, rule type and source.
Choose In progress in B2, Done in B3 and Not started in B4. If Allowed status appears as an option, the source range includes H1 incorrectly. Check the actual choices rather than assuming the rule was applied correctly.
Test an invalid direct entry
Configure an error alert that rejects invalid entries, commonly called Stop. Type Almost done into a validated cell. Under that rule it should be rejected. Warning or Information alerts can allow an entry after a prompt, which is a different design.
Add a short input message such as Choose the current task status. A clear message is more useful than a long paragraph obscuring the sheet. The rule should help users complete the field, not make them guess what the workbook expects.
Decide what a blank means
A blank may mean not reviewed, while Not started is an explicit state. Test the ignore-blank setting and actual blank behaviour in your version. Do not assume these meanings are interchangeable.
Validation does not automatically clean previous entries. Review an existing value such as Complete and decide whether it maps to Done. Bulk pasting can bypass or overwrite validation in some workflows, so audit imports separately. Validation is an entry aid, not a security boundary.
Maintain the list
If you add Blocked in H5, a source ending at H4 will not include it. Extend the source or configure an expanding source deliberately, then test the drop-down again. Avoid deleting a status still used by records without deciding how those records should be migrated.
Try it yourself
Add Blocked and extend the source range. Put a blocked task's explanation in a Notes column. Why should the explanation not become a unique status value?
Check your answer
The source should include H2:H5, and Blocked should appear in the choices. Keep the specific reason in Notes so all blocked tasks share one category.
If every explanation becomes a different status, summarising blocked tasks becomes difficult. The status describes the state; the note describes the individual circumstances. Test direct invalid entry and a blank after updating the rule.
Keep exploring: Excel learning path.