Dynamic Drop-Down List in Excel: Table, UNIQUE, OFFSET

Part of the free Module 6: Data Validation · Lesson 12 of 14 · Full Excel course

Updated on 5 September 2026 · The Excel Table and OFFSET methods work in Excel 365, 2021, 2019 and 2016; the UNIQUE method needs Excel 365 or Excel 2021.

A dynamic drop-down list in Excel is a Data Validation list whose source grows and shrinks automatically as you add or delete items, so you never reopen the rule to fix the range. You build one with an Excel Table and a named range (every version), a UNIQUE spill range (Excel 365 and 2021), or an OFFSET and COUNTA formula for older files.

Table of Contents

Why a fixed range source goes stale

The basic drop-down from the earlier lesson uses a Source such as =Lists!$A$2:$A$6. That works until somebody types a sixth item in A7. Data Validation does not notice: the new item is invisible in the drop-down, users start typing it by hand, and the sheet fills with spelling variants. Deleting an item is just as bad, because the list now shows an empty entry at the bottom. A dynamic source fixes both problems by describing the list in a way that Excel re-evaluates every time the drop-down opens.

Source type	Example	What happens when you add an item
Fixed range	`=Lists!$A$2:$A$6`	Not shown until you edit the rule
Excel Table column	Name that refers to `=Table1[Item]`	Shown as soon as the Table row is added
Spill range	`=Lists!$E$2#`	Shown as soon as the formula recalculates
OFFSET formula	`=OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A)-1,1)`	Shown as soon as the cell is filled

Method 1: Excel Table with a named range (all versions)

An Excel Table expands by itself when you type in the row directly under it, and any reference to a Table column expands with it. The catch is that the Data Validation Source box does not accept a structured reference such as =Table1[Item] typed directly; Excel reports an error. The fix is to go through a defined name or through INDIRECT.

On a sheet named Lists, type the heading Item in A1 and your items below it.
Click any item and press Ctrl+T. Tick My table has headers and click OK. Rename the table in Table Design > Table Name to something meaningful, for example tblItems.
Go to Formulas > Defined Names > Define Name. Name it ItemList and set Refers to to =tblItems[Item]. Click OK.
Select the cells that need the drop-down, open Data > Data Tools > Data Validation, choose List and type =ItemList in the Source box. Click OK.

If you prefer not to create a name, type =INDIRECT("tblItems[Item]") as the Source instead. The text inside the quotation marks is evaluated as a reference, which sidesteps the restriction. Either way, the drop-down now lists exactly the rows in the Table. Type a new item in the first empty row under the Table, or press Tab from the last cell, and it appears in the drop-down immediately.

Method 2: UNIQUE and SORT in a helper cell (Excel 365 and 2021)

Dynamic array functions let you build the list from raw data instead of maintaining it by hand. Put a formula in a spare column and point the validation rule at its spill range with the # operator.

On the Lists sheet, click E2 and enter =UNIQUE(A2:A200). The distinct items spill down column E.
For a tidy list with blanks removed and alphabetical order, use =SORT(UNIQUE(FILTER(A2:A200,A2:A200<>""))) instead.
Select the input cells, open Data Validation, choose List and type =Lists!$E$2# in Source.
Click OK. The drop-down shows the spilled values and resizes whenever the source data changes.

The source range in step 1 can be a Table column too: =SORT(UNIQUE(tblSales[Product])) turns a transaction list into a product picker without any manual list at all. This is the best method when the drop-down should reflect data that other people keep adding to.

Method 3: OFFSET with COUNTA for older versions

If the file must open in Excel 2016 or 2019 and you do not want a Table, OFFSET can size the range by counting the entries. With the heading in A1 and items from A2 down, the Source is:

=OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A)-1,1)

Argument	Value	Meaning
reference	`Lists!$A$2`	Starting cell, the first item
rows, cols	`0, 0`	Do not move away from A2
height	`COUNTA(Lists!$A:$A)-1`	Number of filled cells in column A minus the heading
width	`1`	One column

Type the formula in the Source box, or store it as a defined name and refer to the name. The count includes every non-empty cell in column A, so keep the column free of notes, totals or blank gaps. OFFSET is a volatile function: it recalculates every time anything in the workbook changes. One drop-down is harmless; hundreds of OFFSET-based names in a large model can make every keystroke slow, which is the main reason to prefer a Table when you can.

Which method to choose

Feature	Excel Table + name	UNIQUE spill range	OFFSET + COUNTA
Excel versions	2007 and later	365 and 2021	All versions
Grows automatically	Yes, when rows are added to the Table	Yes, on recalculation	Yes, when cells are filled
Handles blanks in the list	Blank rows appear as blank entries	Yes, with FILTER	No, blanks shorten the range
Removes duplicates	No	Yes	No
Sorted output	Only if the Table is sorted	Yes, with SORT	Only if the column is sorted
Performance	Non-volatile, fast	Non-volatile, fast	Volatile, slows big workbooks
Best for	Shared files, maintained lists	Lists built from raw data	Legacy files without Tables

Worked example: a growing product list

An order sheet needs a Product drop-down in column C. The products are kept on the Lists sheet in a Table named tblProducts with one column, Product.

Row	A: Product
1	Product
2	Keyboard
3	Mouse
4	Monitor
5	Webcam

Create the name ProductList with Refers to =tblProducts[Product].
Select C2:C500 on the order sheet, open Data Validation, choose List, Source =ProductList, and click OK. The drop-down shows four products.
Go back to the Lists sheet, click A6 and type Headset. The Table border extends to row 6.
Return to the order sheet and open any drop-down in column C. Headset is now the fifth entry, and the rule was never edited.

In Excel 365 you can skip the Lists sheet entirely by entering =SORT(UNIQUE(tblOrders[Product])) in a helper cell and using its spill reference as the Source; every product that has ever been ordered becomes an option in alphabetical order.

Tips and common mistakes

No blanks in the middle. COUNTA counts filled cells, not the last row, so a gap makes the OFFSET range stop short. Tables show gaps as empty drop-down entries.
Remove duplicates from the source. A Table lists Mouse twice if it is typed twice. Wrap the source in UNIQUE, or use Data > Remove Duplicates on the list.
Keep Ignore blank ticked. Unticking it in the Data Validation dialog forces users to choose a value before moving on, which is rarely what a form wants.
Keep In-cell dropdown ticked. If the arrow disappears, this box was cleared. In Excel 365 the drop-down also offers autocomplete as you type, which makes long dynamic lists easy to use.
Put the list on another sheet. Name it Lists, hide it if you like, and protect it so nobody overwrites the source. Names and spill references work across sheets in every version since Excel 2010.
Type new items inside the Table. A value typed two rows below the Table, or in a different column, does not extend it. Use the row directly under the last row or press Tab from the last cell.
Avoid volatile names in big models. Prefer the Table method to OFFSET when a workbook already recalculates slowly.

Errors and how to fix them

Symptom	Cause	Fix
#SPILL! in the helper cell	Something sits in the cells the UNIQUE formula needs	Clear the column below the formula, or move the formula to an empty column
The Source box rejects =Table1[Item]	Data Validation does not accept structured references directly	Create a name that refers to the Table column, or use INDIRECT with the reference in quotation marks
Drop-down is empty	COUNTA returns 0 or 1, or the spill formula returns an error	Check the heading is in row 1 and the items start in row 2; fix the helper formula
Blank items at the end of the list	The Table or spill range includes empty rows	Delete the empty Table rows; wrap the source in FILTER(range,range<>””)
New item does not appear	It was typed outside the Table, or the rule still points at a fixed range	Move the item into the Table; reopen the rule and confirm the Source is the name or spill reference
Error: The list source must be a delimited list or a reference	A spill reference was used in Excel 2019 or earlier	Switch to the Table or OFFSET method

Practice exercise

On a new sheet, type the heading Department and five department names, convert the list to a Table, name the column with a defined name and apply it as a drop-down on another sheet.
Add two more departments under the Table and confirm they appear without touching the rule.
In Excel 365, enter a list of twenty city names with a few duplicates and blanks, build a SORT(UNIQUE(FILTER())) helper formula and use its spill range as a drop-down source.
Rebuild the same city drop-down with OFFSET and COUNTA, then insert a blank row in the middle of the list and observe what happens to the last entry.
Compare the three lists side by side and note which one you would hand to a colleague who still uses Excel 2016.

Key takeaways

A fixed range source misses new items; a dynamic source re-evaluates every time the drop-down opens.
Excel Tables expand automatically, but the Source box needs a defined name or INDIRECT to reach a Table column.
UNIQUE, SORT and FILTER build a clean, sorted list from raw data in Excel 365 and 2021; refer to it with the # spill operator.
OFFSET with COUNTA works everywhere but is volatile and breaks on blank gaps.
Keep the list on a separate sheet, avoid blanks and duplicates, and add new items inside the Table.

Related lessons

Frequently asked questions

How do I make a drop-down list in Excel update automatically?

Store the items in an Excel Table (Ctrl+T), create a defined name whose Refers to box contains the Table column, such as =tblItems[Item], and use that name as the Data Validation Source. Every item typed in the row under the Table joins the drop-down at once. In Excel 365 you can instead point the Source at a UNIQUE spill range with the # operator.

Why does Data Validation not accept a Table reference?

The Source box only accepts a cell reference, a defined name or a delimited list, so a structured reference typed directly returns an error. Wrap it in a name, or type =INDIRECT with the Table column text in quotation marks. Both are evaluated to the live Table column, so the drop-down still grows with the Table.

Can I use UNIQUE directly in the Data Validation Source box?

No. Data Validation cannot evaluate array formulas in the Source box, even in Excel 365. Enter =UNIQUE(range) in a helper cell on the list sheet and refer to that cell with the spill operator, for example =Lists!$E$2#. The rule then follows the spill range as it grows or shrinks.

Is OFFSET bad for a dynamic drop-down list?

It works in every Excel version, but OFFSET is volatile, so it recalculates with every change in the workbook. For a handful of lists the cost is unnoticeable. In a large model with many volatile names, recalculation becomes slow, so use an Excel Table with a defined name whenever the file will be opened in Excel 2007 or later.

How do I remove blank entries from a dynamic drop-down?

Blanks appear when the Table or range contains empty rows. Delete empty Table rows, or in Excel 365 wrap the source in FILTER(range,range<>””) before UNIQUE so blanks never reach the list. With OFFSET, blanks in the middle are worse: they shorten the range and cut off the last items, so keep the column contiguous.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems with dynamic drop-downs are available at NextGenTemplates.com.