Excel: how to create a drop-down list in a cell

A drop-down list lets people pick from a set of choices instead of typing. This keeps spellings consistent, which helps when you filter, count or sum your data later.

Create a simple list

1. Click the cell, or select several cells, where you want the drop-down.
2. Click the Data tab, then Data Validation.
3. On the Settings tab, change Allow to List.
4. In the Source box, type your choices separated by commas, for example Yes,No,Maybe.
5. Make sure In-cell dropdown is ticked.
6. Click OK.

A small arrow now appears when you click the cell.

Use a list stored on the sheet

This is better for longer lists or lists that change.

1. Type your choices down a column, for example in cells H2 to H6.
2. Click the target cell, open Data Validation and choose List.
3. Click in Source, then click and drag over H2 to H6 on the sheet.
4. Click OK.

If you want the list to grow, turn it into a table first by clicking inside and pressing Ctrl+T. Using a table as the source is a bit more advanced, so ask us if you get stuck.

Show a message or a warning

• Input Message tab: shows a helpful hint when the cell is selected.
• Error Alert tab: decides what happens if someone types something not on the list. Stop refuses it. Warning and Information allow it after a prompt.

Remove a drop-down

Select the cell, open Data Validation and click Clear All.

Common problems

• The arrow does not show: In-cell dropdown might be unticked, or the sheet is protected. Click the cell to see if the arrow appears.
• Copying and pasting removes it: pasting a normal cell over it removes the rule, so use Paste Special, then Values to avoid this.
• People can still type anything: the Error Alert style is set to Warning or Information.
• Mac: it is the same under Data, then Data Validation.

When to get more help

If you want a list that feeds from another file or many linked lists, ask us for a hand to set it up properly.