Excel: Create a Drop-Down List with Data Validation

You want to restrict a cell so people pick from a set list instead of typing free text.

Why: A drop-down keeps entries consistent (no “NY” vs “New York” vs “new york”), which means your sorting, filtering, and formulas all behave. Excel builds drop-downs through Data Validation, so the same dialog also lets you show a help message and block invalid typing.

Step 1: Create the drop-down

You can either type the choices or point at cells that already hold them.

1. Select the cell (or cells) where you want the drop-down to appear.
2. Go to Data > Data Validation.
3. On the Settings tab, in the Allow box, choose List.
4. In the Source box, supply the choices one of two ways:

   • Type them, separated by commas, with no spaces unless you want them:

     Open,In Progress,On Hold,Done

   • Or select a range that already contains the values, for example by clicking and dragging over $F$2:$F$5. Exclude any header cell.

5. Make sure In-cell dropdown is ticked so the little arrow shows. Leave Ignore blank ticked if empty cells are allowed.
6. Click OK.

Click the cell and you’ll see the arrow; click it to choose a value.

Step 2: Add a help message and an error alert (optional)

This makes the cell self-explanatory and stops bad entries.

1. Reopen Data > Data Validation on the same cells.
2. On the Input Message tab, tick Show input message when cell is selected and type a short note (for example, “Pick a status from the list.”). This pops up whenever the cell is selected.
3. On the Error Alert tab, pick a Style:
   • Stop blocks anything that isn’t on the list.
   • Warning or Information allows it but flags it.
4. Click OK.

Step 3: Add or remove items later

How you edit the list depends on how you built it.

• If you typed the values: reopen Data > Data Validation and edit the comma-separated text in the Source box.
• If you pointed at a range: edit the cells in that range, then widen the Source reference if you added rows below it. Tip: put the source list inside an Excel table (Insert > Table) and reference the table column — the drop-down then grows and shrinks automatically as you add or remove items.

FAQ

The drop-down arrow doesn’t show. Reopen the rule and confirm In-cell dropdown is ticked. The arrow only appears while the cell is selected.

Can I let people type a value that isn’t on the list? Yes — set the Error Alert style to Warning or Information instead of Stop, or uncheck “Show error alert” entirely.

How do I remove the drop-down? Select the cells, open Data > Data Validation, and click Clear All, then OK.

Sources: Microsoft Support — Create a drop-down list · Microsoft Support — Add or remove items from a drop-down list