Dynamic Drop-Down Lists in Excel That Update Themselves

A dynamic drop-down list in Excel is a drop-down whose choices update by themselves when the data changes. You get one in two ways: use an Excel table as the source, or point the list at a spill range built with SORT and UNIQUE. This guide shows both, plus a dependent list where the second drop-down changes with the first choice.

What makes a drop-down list dynamic?#

A drop-down list is a Data Validation rule set to List. The rule has a Source: the cells that hold the choices. A normal source like $H$2:$H$5 is fixed, so an item typed in H6 never shows up. A dynamic source grows or changes with the data, so you never have to edit the rule again.

If you have never made a drop-down, start with the step-by-step guide to Data Validation drop-downs. The examples below use this product list in A1:C9:

Method 1: Use an Excel table as the source#

An Excel table grows when you type in the row just below it, and a drop-down based on the table grows with it. This works in every version from Excel 2007 on.

1. Type your choices in one column with a header, for example Status in K1 and Open, Pending, Closed below it.
2. Click inside the list and press Ctrl+T (Mac: ⌘T). Tick My table has headers and click OK.
3. Select the cell that needs the drop-down and go to Data > Data Validation (Windows keys: Alt, A, V, V).
4. In Allow, choose List. Click in Source and select the table's data cells, K2:K4, without the header.
5. Click OK.

Now type "On hold" in K5. The table expands to K5, and the drop-down shows four choices. This method is best for a short list of options you type by hand. It doesn't remove repeats or sort anything.

Method 2: Use SORT and UNIQUE with a spill range#

In Microsoft 365, Excel 2021 or Excel 2024, you can build the list from the data itself. The choices are always the distinct values already in a column, sorted A to Z.

1. In an empty helper cell, say F2, type:

   Excel formula

   =SORT(UNIQUE(A2:A50))

   It spills Bakery, Dairy, Fruit. The range runs to row 50 to leave room for new rows.

2. Select the cell for the drop-down and open Data > Data Validation.
3. Choose List, and in Source type =$F$2#.
4. Click OK.

The # after F2 is the spill range operator. It means "every cell the formula in F2 spilled into", however many that is today. Add a row with the category "Frozen", and both the helper list and the drop-down show four items. Read more in the guide to the spill range operator.

Data Validation won't accept a formula like UNIQUE typed straight into the Source box, so the helper cell is needed. You can put it on a separate sheet, such as Lists, and use =Lists!$F$2# as the source.

Because A2:A50 includes empty rows, UNIQUE adds a 0 to the list for the blanks. Remove it by filtering first:

=SORT(UNIQUE(FILTER(A2:A50,A2:A50<>"")))

The UNIQUE function guide explains its optional arguments, and the guide to a sorted list of unique values shows more ways to shape the list.

Method 3: Make a dependent drop-down with FILTER#

A dependent drop-down changes its choices based on another cell. Pick Dairy in the first list, and the second list shows only Milk, Cheese and Yogurt.

1. Make the first drop-down in E2 using Method 2, so it offers Bakery, Dairy and Fruit.

2. In a helper cell, say G2, type:

   Excel formula

   =FILTER(B2:B9,A2:A9=E2)

   With Dairy in E2, it spills Milk, Cheese, Yogurt.

3. Select E3 (or wherever the second choice goes) and add a Data Validation List with the source =$G$2#.

When someone changes E2 to Fruit, the helper list becomes Apple, Banana, Cherry, and so does the second drop-down. If E2 is empty, FILTER finds no match and returns #CALC!. Add a third argument to show a friendly message instead: =FILTER(B2:B9,A2:A9=E2,"Pick a category").

Why isn't my dynamic drop-down working?#

Most problems come from the helper cell or the source reference. Check these:

• The helper shows #SPILL!. Something blocks the cells below it, so the drop-down has no list. Clear the cells under the formula. The guide to the #SPILL! error lists every cause.
• You forgot the #. =$F$2 gives a drop-down with only the first item. Add the #.
• The helper is inside a table. Spilling formulas don't work in Excel tables. Put the helper in normal cells.
• The list has repeats. You pointed the source at the raw column. Point it at the UNIQUE helper instead.
• Old entries stay. Data Validation only checks values as they are typed. A cell that says "Frozen" keeps it even after Frozen leaves the list. Use Data > Data Validation > Circle Invalid Data (Windows) to find them.

Dynamic drop-downs in older Excel#

Without dynamic arrays, use Method 1, or a named range built with OFFSET and COUNTA, such as =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1). That formula is harder to read and recalculates often, so a table is usually the better choice. To see why spilling changed so much, read dynamic arrays explained.

Practise it#

Helper lists for drop-downs are just UNIQUE, SORT and FILTER formulas, and those are skills employers test. Practise the UNIQUE function in a live spreadsheet: you type the formula and it checks it instantly. Then practise spill references like A2# and other modern Excel functions. Try the FILTER exercise on this page first.