How do I make a dependent (cascading) drop-down list?

Name each sub-list range to match a value in the first list, then set the second cell's Source to =INDIRECT(A2) so it shows only options tied to the choice in A2.

Why is Data Validation grayed out?

The most common cause is a protected sheet — go to Review → Unprotect Sheet first. It is also disabled while you are editing a cell or when multiple sheets are grouped, so click one tab to ungroup.

Can I color drop-down entries differently?

Data Validation has no built-in colors, but you can add Conditional Formatting rules on the same cells so each value paints its own fill — for example green for Done and red for Blocked.

How do I let people type values that are not on the list?

In the Data Validation dialog, open the Error Alert tab and either uncheck Show error alert or set Style to Information or Warning so off-list entries are allowed instead of rejected.

How to Make a Drop-Down List in Excel

Last updated June 2026

Quick answer

To make a drop-down list in Excel, select the cells, go to the Data tab, click Data Validation, and set Allow to List. In the Source box, either type the choices separated by commas or point at a range of cells, then click OK. Every selected cell now shows an arrow with your options.

How do you add a drop-down list?

Select where the list should appear
Highlight the cells that need the drop-down — say B2:B20. Whatever you select now is exactly where the arrow shows up later, so include every row you plan to fill.
Open Data Validation
On the Data tab, click Data Validation in the Data Tools group. In the dialog, open the Allow dropdown and choose List.
Provide the source
In the Source box, type the options separated by commas with no equals sign — for example Yes,No,Maybe (no spaces needed) — then click OK. A leading = would make Excel read the entry as a formula and throw "The Source currently evaluates to an error." This is the fastest route for short, fixed lists.
Or point at a range instead
Rather than typing, click in the Source box and drag-select a column of values on any sheet. Excel fills in the absolute reference for you. Edit those cells later and the list updates automatically.
=$E$2:$E$10
Keep the in-cell arrow on
Leave In-cell dropdown checked (it is by default) so the arrow appears. Under the Error Alert tab you can decide whether off-list typing is blocked or just warned about, then click OK.
Test it
Click any cell in your range. An arrow appears on the right edge — click it and pick a value. Try typing something that is not on the list to confirm your error setting behaves the way you want.

Which list source type should you use?

All three approaches produce the same arrow. The difference is how easily the list changes over time and where the values live.

Source type	What you put in Source	When it wins
Typed list	`Yes,No,Maybe` — the items themselves, comma-separated	Short, fixed lists that rarely change (status, Yes/No).
Range-backed	`=$E$2:$E$10` — a fixed cell range	Longer lists you want to edit on the sheet; add rows by widening the range.
Table-backed	`=INDIRECT("Table1[Category]")` — a structured column	Lists that grow; new rows in the table appear in the drop-down automatically.

The three drop-down source types and when each one wins.

For a list that grows on its own, format your source values as an Excel Table (Ctrl+T), then reference its column. Because tables auto-expand, every value you add at the bottom shows up in the drop-down with no range edits.

How do you edit or remove a drop-down list?

Change an existing drop-down

Reopen the rule
Click a cell that already has the drop-down, then go to Data → Data Validation. The dialog opens with the current Source so you can retype items or widen the range.
Apply the change everywhere at once
Before clicking OK, tick Apply these changes to all other cells with the same settings. That pushes your edit to every matching cell instead of just the one you opened.
Remove the drop-down
Select the cells, open Data Validation, and click Clear All, then OK. The arrow disappears and any value already typed stays in the cell untouched.

Frequently asked questions

How do I make a dependent (cascading) drop-down list?: Name each sub-list range to match a value in the first list, then set the second cell's Source to =INDIRECT(A2) so it shows only options tied to the choice in A2.
Why is Data Validation grayed out?: The most common cause is a protected sheet — go to Review → Unprotect Sheet first. It is also disabled while you are editing a cell or when multiple sheets are grouped, so click one tab to ungroup.
Can I color drop-down entries differently?: Data Validation has no built-in colors, but you can add Conditional Formatting rules on the same cells so each value paints its own fill — for example green for Done and red for Blocked.
How do I let people type values that are not on the list?: In the Data Validation dialog, open the Error Alert tab and either uncheck Show error alert or set Style to Information or Warning so off-list entries are allowed instead of rejected.