Drop-Down Lists with Data Validation

Excel Guides / Dashboard Building

Dashboard Building

Build drop-downs from typed lists, ranges and tables, make them update themselves and link one list to another.

Updated 12 Oct 2026 · 7 min read · By Sky Dashboards

A drop-down list is the cheapest data cleaning you will ever do. If every entry in a log comes from a list, there are no typos, no 'Insta' in one row and 'Instagram' in the next, and every SUMIFS and PivotTable built on it adds up. This guide covers building the list, keeping it up to date and catching entries that slip past it.

Create a drop-down list

1. Select the cells that should get the list, for example the whole Status column of a log.
2. Go to Data > Data Validation (in the Data Tools group), or press Alt A V V.
3. On the Settings tab, set Allow to List.
4. In Source, type the items or point to a range (see below).
5. Leave In-cell dropdown ticked and click OK.

Select one of the cells and an arrow appears beside it. Click it, or press Alt + Down Arrow to open the list from the keyboard. Anything typed that is not on the list is rejected.

Where the list comes from

A typed list

Type the items separated by commas: Paid,Unpaid,Refunded. It is quick for short lists that never change, but editing it means reopening the dialog, and the Source box holds 255 characters at most. If your region uses a semicolon as the list separator, use that instead.

A range of cells

Type = and select the cells holding the items, for example =Lists!$A$2:$A$6. The range can sit on another sheet. Change a cell in it and every drop-down shows the change, but an item added below the range is not picked up.

A table column, which grows

Put the items in an Excel Table and the list grows whenever you add to it. The Source box will not accept a structured reference such as =tblStatus[Status] on its own, so use one of these:

• A named range. Go to Formulas > Name Manager > New, call it StatusList and set Refers to to =tblStatus[Status]. Then use =StatusList as the source.
• INDIRECT. Use =INDIRECT("tblStatus[Status]") as the source. It works the same way, but because the reference is just text it breaks if the table or column is renamed.

A list that builds itself with UNIQUE

In Excel 2021 and Microsoft 365 a formula can build the list from data you already have. UNIQUE returns each client once, SORT puts them in order, and the drop-down points at the result.

Now set the drop-down's source to =$E$2#. The # means 'the whole spill range that starts in E2', so a new client added to the table appears in the list on its own, and the list shrinks if one disappears.

Dependent drop-downs

A dependent list changes with the choice in another cell: pick Candles in column A and column B offers only candle products. There are two ways to build one.

INDIRECT with named ranges (works on every row)

1. Select A1:C4 and choose Formulas > Create from Selection with Top row ticked. Excel names A2:A4 Candles, B2:B4 Melts and C2:C4 Gifts.
2. In the log, give the category column a normal list: Candles,Melts,Gifts.
3. Select the product column starting at B2 and set its source to =INDIRECT(A2). Keep A2 relative so each row reads its own category.

FILTER (one selector cell)

For a single selector on a dashboard, a spill formula is tidier. With the category picked in B1 and products in a table called tblProducts, put =FILTER(tblProducts[Product],tblProducts[Category]=B1) in a helper cell such as H2 and use =$H$2# as the second drop-down's source. See FILTER. This only suits one selector, because the helper reads one cell.

Input messages and error alerts

The other two tabs of the Data Validation dialog control what people see. Input Message shows a short note whenever the cell is selected, such as 'Pick the payment status'. Error Alert decides what happens when someone types something that is not on the list.

Check, copy and remove validation

• Find bad entries: Data > Data Validation > Circle Invalid Data draws a red circle round every cell that breaks its rule. Clear Validation Circles, in the same menu, removes them.
• Copy a drop-down without its contents: copy the cell, select the targets, press Ctrl + Alt + V, choose Validation and press Enter.
• Change every copy at once: open the dialog on one cell, make the change and tick Apply these changes to all other cells with the same settings.
• Find validated cells: Home > Find & Select > Data Validation selects every cell on the sheet that has a rule.
• Remove a drop-down: select the cells, open Data Validation and click Clear All.

Drop-down logs in dashboards

Sky Dashboards templates run on drop-down logs: a table where each row is a date, an amount and a few columns picked from lists, such as channel, category and status. The dashboard then summarises with SUMIFS and PivotTables keyed on those exact words. A few habits keep that reliable:

• One Lists sheet. Keep every list in its own table on one sheet, so changing a category is a single edit.
• Validate the whole table column. Apply the rule to every data cell in the column and new rows inherit it.
• Build summary labels from the same list. Then a summary row can never be spelt differently from the entries it counts.
• Check before you trust a total. Run Circle Invalid Data after any large paste.

Frequently asked questions

How do I add an item to an existing drop-down list?

If the source is typed, edit the Source box. If it is a range, add the item inside the range or extend the reference. If it is a table column behind a named range, type the item in the next row of the table and it appears straight away.

Why does my drop-down list not show new items?

The source is a fixed range and the new item sits below it. Move the list into an Excel Table and point the drop-down at it through a named range, or use a UNIQUE formula and the # spill reference.

Can a drop-down list allow more than one choice?

Not with Data Validation alone. Each cell holds one value. Multiple selections need VBA, or a layout with one column per option.

Can I make the drop-down text bigger?

Not directly. The list uses a fixed font size, but zooming the sheet in makes it larger on screen.

Related guides

All 32 Excel guides