How to Create a Drop-Down List in Excel

A drop-down list lets people pick a value from a set of options instead of typing it. It keeps your data consistent: no more "Done", "done" and "Completed" meaning the same thing in one column. In Excel, drop-down lists are created with Data Validation.

Create a simple drop-down list

Select the cell or cells where you want the drop-down, for example C2:C100.
Go to the Data tab and click Data Validation (in the Data Tools group).
On the Settings tab, open the Allow list and choose List.
In the Source box, type your options separated by commas:
```
To do,In progress,Done
```
Make sure In-cell dropdown is ticked, then click OK.

Click one of the cells and a small arrow appears on the right. Click it to choose an option.

Tip: depending on your regional settings, Excel may expect a semicolon instead of a comma between items (To do;In progress;Done). If all your options appear as one item, switch the separator.

Use a list of cells as the source

Typing options works for short lists. For longer ones, keep the options in cells so they're easy to edit.

Type your options in a column, for example F2:F6.
Select the cells for the drop-down and open Data → Data Validation.
Choose Allow: List, click in Source, and select F2:F6 with the mouse. Excel writes =$F$2:$F$6.
Click OK.

Keep the options on another sheet

It's cleaner to store option lists on a separate sheet, for example one named Lists. In the Source box, point to it like this:

=Lists!$A$2:$A$10

You can also just click the other sheet's tab while the Source box is active and select the range.

Make the list update automatically

With a fixed range like $F$2:$F$6, new options added in F7 won't appear in the drop-down. To make the list grow on its own:

Click inside your list of options and press Ctrl + T to turn it into a table. Tick My table has headers if the first cell is a title.
Select the option cells (without the header), click in the Name Box to the left of the formula bar, type a name such as StatusList and press Enter.
In Data Validation, set the Source to:
```
=StatusList
```

Because the name points to a table column, it expands whenever you add a new row to the table, and the drop-down updates with it.

Add an input message and an error alert

The Data Validation window has two more tabs worth using:

Input Message shows a small hint when someone selects the cell, like "Choose the task status".
Error Alert controls what happens when someone types a value that isn't in the list. Stop blocks it, Warning asks for confirmation, and Information just informs and lets the value through.

Copy or remove a drop-down list

Copy: copy a cell that has the drop-down, select the destination cells, then use Paste Special → Validation. This copies the drop-down without overwriting the cells' values or formatting.
Remove: select the cells, open Data → Data Validation and click Clear All, then OK. The values already chosen stay in the cells.

Frequently asked questions

Why don't I see the drop-down arrow?

Check that In-cell dropdown is ticked in the Data Validation settings. The arrow only appears when the cell is selected.

Can I do the same in Google Sheets?

Yes. In Google Sheets, select the cells and go to Insert → Dropdown, then type your options or choose Dropdown (from a range).

Does a drop-down stop people pasting other values?

No. Data Validation checks typed values, but pasting over the cell can replace both the value and the validation rule. If that's a risk, protect the sheet under Review → Protect Sheet.