How to Create a Drop-Down List in Excel (Data Validation)

To create a drop-down list in Excel, select the cells, go to Data → Data Validation, choose “List”, and type your options separated by commas (or point to a range of cells). The drop-down then appears when you click a cell — and it prevents anyone typing something that doesn’t belong.

The quick way: type the options in

Best for a short, fixed list like Yes/No or Low/Medium/High.

1. Select the cells where you want the drop-down (drag over a range, or click the column letter).
2. Go to the Data tab → Data Validation.
3. Under Allow, choose List.
4. In the Source box, type your options separated by commas:

   Low,Medium,High

5. Make sure In-cell dropdown is ticked.
6. Click OK.

Click any of those cells and a small arrow appears — pick from the list.

You can’t use commas inside an option. “Medium, rare” would become two separate choices. If your options contain commas, use the range method below instead.

The better way: reference a range

For longer lists, or lists you’ll want to change later, put the options in cells and point at them.

1. Somewhere out of the way (or on another sheet), type your options in a column — one per cell.
2. Select the cells you want the drop-down in.
3. Data → Data Validation → Allow: List.
4. Click the arrow in the Source box and select your list range (e.g. Sheet2!$A$1:$A$8).
5. Click OK.

Now editing a cell in that source list updates the drop-down everywhere it’s used. That’s the big advantage — no re-editing the validation.

Name the range for clarity: select your options and type a name in the Name Box (top-left, left of the formula bar), then use =MyList as the source. Much easier to read than Sheet2!$A$1:$A$8.

Use a list on another sheet

Current Excel accepts a range on another sheet. Select your cells, open Data Validation, and either type the reference (e.g. Sheet2!$A$1:$A$8) or click the sheet tab and drag to select the range — both work.

On Excel 2007 or earlier, direct cross-sheet references aren’t allowed. Create a named range for your list and enter =MyList in the Source box instead.

Naming the range is worth doing anyway: =MyList is far easier to read than Sheet2!$A$1:$A$8.

Copy the drop-down to other cells

1. Click the cell that has the drop-down.
2. Copy (Ctrl + C).
3. Select the cells you want it in.
4. Paste → Paste Special → Validation (Ctrl + Alt + V, then N — or the legacy sequence Alt + E → S → N).

That copies only the validation rule, not the contents. Or just drag the fill handle — Excel carries validation along with formatting.

Common problems

Let people type something else

By default, Data Validation blocks any value not in your list. That’s usually what you want — but not always.

Go to the Error Alert tab and set Style to Warning (you can still type other values, with a prompt) or Information (a gentle note only). Setting it to Stop, the default, refuses anything off-list.

Related Excel skills

Once your data’s tidy, how to sort in Excel keeps the lists in order, and conditional formatting can highlight the rows that matter. If you’re building something bigger than a single list, see how to make a dashboard in Excel.

FAQ

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

Select the cells → Data → Data Validation → Allow: List → type options separated by commas, or point the Source at a range of cells → OK.

Why won’t Excel accept my list from another sheet?

Current Excel does accept cross-sheet references — type Sheet2!$A$1:$A$8 or select the range directly. Only Excel 2007 and earlier refuse it, and there you’d use a named range (=MyList) instead.

How do I add a drop-down to many cells at once?

Select the whole range (or the column) before opening Data Validation. The rule applies to every selected cell in one go.

Can I make a drop-down list depend on another drop-down?

Yes — that’s a dependent (cascading) list. It needs named ranges that match the options in the first list, plus an INDIRECT() formula as the source. It’s an advanced setup, but fully doable.

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

Select the cells → Data → Data Validation → Clear All → OK. That removes the validation without touching the cell contents.