Create a Drop-Down List in Excel

An Excel drop-down list lets people pick a value from a short menu instead of typing it. You build it with Data Validation: select the cells, choose List, and give Excel the items. Drop-downs stop typos like "Nrth" and "north " from splitting your reports, so they're worth adding to any sheet other people fill in.

How to create a drop-down list in Excel#

A drop-down list is a Data Validation rule with Allow set to List. It takes about 30 seconds.

1. Select the cells that should get the drop-down, for example C2:C20.
2. Go to Data > Data Validation (in the Data Tools group). On Windows, the ribbon keys are Alt, A, V, V.
3. On the Settings tab, open Allow and choose List.
4. In Source, type the items separated by commas, such as Open,In progress,Closed, or select a range of cells that holds them (see the next section).
5. Leave In-cell dropdown ticked, then click OK.

Click one of the cells and an arrow appears on its right. Click it to pick an item. From the keyboard, select the cell and press Alt+↓ on Windows or Option+↓ on a Mac to open the list, then use the arrow keys and Enter.

Typing still works: you can type Closed instead of picking it. But if someone types a value that isn't on the list, Excel shows an error and refuses it.

Use a range of cells as the source#

For more than a handful of items, keep them in cells and point the list at them. Then you edit the cells, not the rule.

In Source, click the box, then select H2:H5. Excel writes =$H$2:$H$5. The dollar signs keep the source fixed for every cell that has the rule.

The items can live on another sheet. Click in Source, then click the other sheet's tab and select the cells. Many people keep all their lists on a sheet called Lists and hide it.

Name the list for clearer rules#

Select H2:H5, click the Name Box to the left of the formula bar, type Departments and press Enter. Now the Source can be =Departments. That's easier to read and works from any sheet.

Make the drop-down list update automatically#

A fixed range like $H$2:$H$5 won't include an item you add in H6. Two ways around that:

• Use an Excel table. Click in the list and press Ctrl+T (⌘T on a Mac). Then select the table's data cells as the Source. When you type a new item directly below the table, the table grows and so does the drop-down. Our guide to Excel tables explains why tables grow.
• Use a dynamic array (Microsoft 365 and Excel 2021 or later). In J2, type =SORT(UNIQUE(B2:B50)) to build a sorted list of the categories already in column B. Then set the Source to =$J$2#. The # means "the whole spill range", so the list grows and stays sorted.

Control what happens with wrong entries#

The Error Alert tab decides how strict the list is. Open Data > Data Validation again and pick a Style:

You can write your own Title and Error message, such as "Pick a department from the list." On the Input Message tab you can also add a hint that appears when someone selects the cell.

Two limits to know. Validation only checks values when they're typed: data that was already there, or that someone pastes in, isn't checked. To find bad entries, use Data > Data Validation ▾ > Circle Invalid Data. And pasting a normal cell over a validated one removes the drop-down.

Use a drop-down with VLOOKUP#

A drop-down becomes really useful when the choice drives a formula. Pick a product, and a lookup fills in its price.

Give F2 a drop-down with Source =$A$2:$A$6. Then in G2:

=VLOOKUP(F2,A2:C6,3,FALSE)

Pick Toner and G2 shows 88. Because the drop-down only allows names from column A, the lookup can't fail on a typo. If VLOOKUP is new to you, our step-by-step VLOOKUP guide and the VLOOKUP function reference cover each argument.

Drop-downs also make filters and charts cleaner. Consistent categories mean the filter buttons show one "Finance" instead of three spellings, and a chart of totals by category has one bar per category.

Copy, find and remove drop-down lists#

• Copy a drop-down to more cells: copy a cell that has it, select the new cells, then use Paste Special and choose Validation. That copies only the rule, not the value.
• Find every drop-down on a sheet: Home > Find & Select > Data Validation.
• Remove a drop-down: select the cells, open Data > Data Validation, click Clear All, then OK. The values stay in the cells.

To catch repeated entries in a list that should be unique, such as invoice numbers, combine validation with highlighting duplicates.

Drop-down lists in job tests#

Tests for office and admin roles often ask you to "restrict a column to these values" or "add a drop-down for status". The task checks you know where Data Validation is and that you point the Source at a range, not a pasted copy. It sits alongside the other skills in Excel skills for admin and office jobs and in our guide on how to pass an Excel test for a job interview.

Practise it#

Try the exercise on this page: look up the price of the item picked in a drop-down. Then practise VLOOKUP in a live spreadsheet, and practise filtering a table to see how clean, consistent categories make filters easier.