Create a Drop-Down List

All versionsData Validation

A drop-down list turns a free-text cell into a pick-from-a-menu cell — fewer typos, consistent data, faster entry. It’s built with Data Validation, and the best version reads from a range so the choices update themselves.

Quick formula: Select the cell(s), then Data → Data Validation → Allow: List, and set Source to your list range:

=$F$2:$F$6

Point Source at a column of choices (or type them comma-separated). Excel adds the drop-down arrow to every selected cell.

How to set it up

Put your allowed values somewhere (a column works best), then point a Data Validation List rule at them. Here the Region cells become drop-downs sourced from F2:F4.

	A	B	F
1	Order	Region	Valid regions
2	1001	West ▾	East
3	1002	East ▾	West
4	1003	▾	South

The formula

The Source box for the validation rule:

=$F$2:$F$4 // the list of allowed regions

How it works

Five clicks and you have a controlled-entry cell:

Type your allowed values into a range — e.g. F2:F4 = East, West, South. (A separate area or sheet keeps it tidy.)
Select the cell(s) that should have the drop-down (the Region column).
Go to Data → Data Validation, set Allow to List.
In Source, point at the range: =$F$2:$F$4 (or type East,West,South directly). Click OK — every selected cell now has a drop-down arrow.

Make the list grow automatically. Convert the source to an Excel Table (Ctrl+T) and reference its column, or in Excel 365 point Source at a spill range with =$F$2#. New entries then appear in the drop-down with no edits.

Try it: interactive demo

Live demo

This is what the validated cell behaves like — pick a value:

Region

Cell value: (empty)

Variations

Type the list directly

For a short, fixed list, skip the range and type into Source:

East,West,South

Add an input message / error alert

On the Data Validation dialog’s other tabs, add a prompt that appears on selection and a custom “invalid entry” message.

List from another sheet

Reference a named range so the source can live anywhere:

=Regions

Pitfalls & errors

“The Source currently evaluates to an error.” Usually a typo in the range or a missing =. The Source must be a valid reference (or a literal comma list with no leading =).

Validation doesn’t block pasted values. Pasting into a validated cell can bypass the rule. Use Data → Data Validation → Circle Invalid Data to catch offenders.

A fixed range won’t grow. If you add a 4th region below the range, the drop-down won’t show it. Use a Table or spill reference so the list expands.

Practice workbook

📊

Download the free Create a Drop-Down List practice workbook

A live drop-down already set up on the Region column (from a source range), plus a Table-based auto-expanding example and practice steps. No sign-up required.

Frequently asked questions

How do I create a drop-down list in Excel?

Select the cells, go to Data > Data Validation, set Allow to List, and put your choices in the Source box, either as a range like =$F$2:$F$6 or a comma-separated list like East,West,South.

How do I make the drop-down list update automatically?

Base the Source on an Excel Table column or, in Excel 365, a spill range using the # operator (=$F$2#). New entries then appear in the drop-down without editing the rule.

Why does my drop-down source show an error?

The Source reference is invalid, usually a typo or a missing equals sign. It must be a valid range or named range, or a literal comma list with no leading =.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class