Drop Down List in Excel – Data Validation Step-by-Step

2026-07-20

  • Excel
  • drop down list
  • data validation
  • tutorial
  • dropdown

Why drop-down lists help

Drop down list in excel searches cluster around forms, status columns, and templates. Data Validation restricts input to approved values and reduces typos.

Add a simple list

  1. Select target cells (e.g. D2:D100).
  2. Data → Data Validation → List.
  3. Source: Open,In Progress,Closed or =$A$2:$A$20 from a lookup table.
  4. Optional: Input Message and Error Alert on the same dialog.

Tips

  • Put lookup values on a hidden sheet for long lists.
  • Name the range (Formulas → Name Manager) for readable sources.
  • For dependent lists (Country → City), use INDIRECT with named ranges or tables.

When validation is not enough

  • Bulk clean invalid entries already typed → export CSV, dedup or Compare.
  • Generate IF/VLOOKUP from rules → Formula AI.
  • how to add a drop down list in excel
  • how to insert a drop down list in excel
  • create drop down list in excel

See also 中文版.