Practice free

How to create a drop-down list in Excel

Draft. Not published yet.

Select the cells, choose Data Validation on the Data tab, set Allow to List and type the items with commas between them, for example Yes,No. Then select OK.

Part of the Data validation guide.

Method 1: Type the items into the list

Ribbon

Best for a short list that rarely changes, such as Yes and No.

  1. Select the cells that should get the drop-down, for example B2:B5.
  2. Select Data > Data Tools > Data Validation.
    Drawing to come: The Data tab with Data Validation in the Data Tools group
  3. On the Settings tab, set Allow to List.
  4. In the Source box, type the items with commas between them: Yes,No.
    Drawing to come: The Data Validation box with Allow set to List and Yes,No in Source
  5. Leave In-cell dropdown ticked and select OK.

Select one of the cells and pick Yes or No from its list. Anything else is refused.

Method 2: Use a list of cells

Ribbon

Best when the items change or the list is long: edit the cells and every drop-down follows.

  1. Type each item in its own cell, one under another, for example E2:E4.
  2. Select the cells that should get the drop-down, for example C2:C5.
  3. Select Data > Data Tools > Data Validation.
  4. Set Allow to List.
  5. In the Source box, type =$E$2:$E$4.
  6. Select OK.

The drop-down in C2:C5 shows whatever E2:E4 holds. Change a word in E3 and the list changes with it.

What goes wrong

The cell refuses an item that looks right

The match is exact, capital letters included. With Yes,No as the items, Excel refuses yes. Pick the item from the arrow instead of typing it, or change the items to the spelling people type.

A new item doesn’t show in the list

With a list of cells, the drop-down reads only the range in Source. An item typed in E5, under =$E$2:$E$4, is not in the list. Open Data Validation again and widen the range to =$E$2:$E$5.

Try it

Live grid coming soon

Your turn: Add a Yes/No drop-down to A2, then pick Yes.

Questions people ask

Can someone type a value that isn't in the list?

Not by default. A new list uses the Stop error style, so Excel refuses any entry that isn't one of the items.

Does the list care about capital letters?

Yes. With Yes,No as the items, Excel refuses yes in lower case.

How do I remove a drop-down list?

Select the cells, choose Data Validation on the Data tab again, select Clear All, then OK.

Part of the Data validation guide.