How to Create a Drop-Down List in Excel

Drop-down lists keep data entry clean and consistent — instead of letting people type anything, you give them a fixed set of choices. They're built with Excel's Data Validation feature, and the basic version takes about a minute.

The quick method: type the items

For a short, fixed list:

1. Select the cell (or cells) where you want the drop-down.
2. Go to the Data tab and click Data Validation.
3. In the dialog, under Allow, choose List.
4. In the Source box, type your items separated by commas: Yes, No, Maybe.
5. Click OK.

Now each selected cell has a little arrow, and clicking it shows your choices. That's the whole basic process.

The better method: reference a range

Typing items works for short lists, but if your options might change, it's better to base the list on a range of cells:

1. Somewhere on your sheet (often a separate area or another tab), type your list items down a column — one per cell.
2. Select the cell where you want the drop-down.
3. Data > Data Validation > Allow: List.
4. In Source, click the range selector and highlight your list of items, or type the range like =Sheet2!$A$1:$A$10.
5. Click OK.

The advantage: when you edit the items in that range, the drop-down updates automatically. No need to reopen Data Validation.

Applying it to many cells

To put the same drop-down down a whole column, select all the target cells before opening Data Validation, and the list applies to all of them at once. Or set it up in one cell and copy that cell down — copying carries the validation with it.

Making the list expand automatically

If you'll add items to your source list over time, base the drop-down on an Excel Table so it grows automatically:

1. Select your list of items and press Ctrl+T to make it a Table.
2. When setting the Source, reference the Table column.

As you add rows to the Table, the drop-down picks them up without any further changes.

Adding an input message or error alert

The Data Validation dialog has two more tabs:

• Input Message — shows a tooltip when the cell is selected (e.g. "Choose a status").
• Error Alert — controls what happens if someone types a value not on the list. You can block it entirely, warn, or just inform.

By default, entries outside the list are rejected, which is usually what you want — that's the whole point of restricting input.

Removing a drop-down

Select the cells, go to Data > Data Validation, and click Clear All, then OK. The drop-down is removed.

The takeaway

Drop-down lists come from Data > Data Validation > List. Type the items for a quick fixed list, or point to a range (ideally an Excel Table) so the list updates itself. They're one of the simplest ways to keep a spreadsheet's data clean and consistent, especially when other people are entering data.