Home › Excel basics › Drop-down lists

How to Create a Drop-Down List in Excel (Data Validation)

A drop-down list lets people pick a value instead of typing it. That prevents typos like "Nort" and "north " that break lookups and pivot tables later. You build one with Data Validation in under a minute.

Excel CSE · 3 min read

Option 1: a quick list of items

1. Select the cells that should get the drop-down, for example B2:B100.
2. Go to Data › Data Validation.
3. Under Allow, choose List.
4. In Source, type the items separated by commas: Open,In progress,Done
5. Click OK.

Each selected cell now shows a small arrow with the three options. Good for short lists that rarely change.

Option 2: a list from a range of cells

For longer lists, put the items in cells (for example on a separate sheet called Lists, in A2:A6) and point to them:

1. Select the target cells and open Data › Data Validation.
2. Allow: List. Source: =Lists!$A$2:$A$6, or click the arrow and select the range.
3. Click OK.

Change an item in the source range and the drop-down changes with it.

A list that grows automatically

With a fixed range, new items added below A6 won't appear. Two ways to fix that:

• Use a table: select the list, press Ctrl+T, then use the column as the source. The easiest way is to name the column range (Formulas › Define Name, refers to =Table1[Status]) and use =StatusList as the source. New rows are included automatically.
• Use UNIQUE (Microsoft 365, Excel 2021+): create a sorted list of existing values with =SORT(UNIQUE(Data!B2:B500)) in, say, H2, and use =$H$2# as the source. The # includes the whole spilled range. See UNIQUE.

Input messages and error alerts

In the same Data Validation dialog:

• Input Message tab: a tooltip that appears when the cell is selected, such as "Pick the order status".
• Error Alert tab: what happens when someone types a value that isn't in the list. Stop blocks it, Warning and Information allow it after a prompt.

Edit or remove a drop-down

• Edit: select a cell with the drop-down and open Data › Data Validation again. Tick "Apply these changes to all other cells with the same settings" to update them all.
• Remove: select the cells, open Data Validation and click Clear All.
• Copy to other cells: copy a cell with the drop-down and use Paste Special › Validation.

Dependent drop-downs

A second list that depends on the first (choose a country, then only see that country's cities) is possible with named ranges and INDIRECT, or in Microsoft 365 with FILTER. Name each city list after its country, then use =INDIRECT(A2) as the source of the second drop-down, where A2 holds the chosen country. Step-by-step: INDIRECT and dependent drop-downs.