How can I automatically expand a dropdown when the source data grows?

Create the source data as a Table, save the structured reference as a name, and use =Name in the Data Validation source.

How do I create a dependent list where items change by category?

Use FILTER to extract only the items for the selected category, define the spill range as a name, and use that name as the Data Validation source.

How do I fix duplicates or blanks appearing in a dropdown?

Use the results of UNIQUE to remove duplicates, SORT to sort, and TRIM/CLEAN to remove spaces and control characters as the list source.

CONTENTS

Excel Data Lists Part 1: Master Basic, Dynamic, and Dependent Dropdowns

The key to creating Excel data lists is using Data Validation to create dropdowns and managing the source data with Tables and dynamic arrays (UNIQUE/FILTER/SORT). This guide covers everything from basic lists to dynamic and dependent lists with practical examples.

Quick Fix — 60-Second Dropdown

Fixed list: enter Apple,Banana,Grape as the source
Range: =$A$2:$A$20

Dynamic Dropdown (Table + Named Range)

Create a table with Ctrl+T (for example, tblFruit)
Name FruitList = tblFruit[Fruit]
Validation source: =FruitList

To remove duplicates and sort: =SORT(UNIQUE(tblFruit[Fruit])) → define the spill range as a name.

Dependent Dropdown (Category → Item)

=SORT(UNIQUE(FILTER(tblItems[Item], tblItems[Category]=E2)))

Define the spill range as a name (for example, ItemList) and use =ItemList as the validation source.

Three Levels (Team → Assignee → Task Code)

=SORT(UNIQUE(FILTER(tblTask[Assignee], tblTask[Team]=B2)))

=SORT(UNIQUE(FILTER(tblTask[Task Code], (tblTask[Team]=B2)*(tblTask[Assignee]=C2))))

Alternative Methods Compared

Method	Advantages	Disadvantages
Table + structured references	Expands automatically	Duplicates and sorting require separate handling
OFFSET	Compatible with older versions	Volatile; may affect performance
INDIRECT	Simple for dependent lists	Vulnerable to renaming and moving
UNIQUE/FILTER	Cleanup and conditional filtering	Not supported in older versions

Troubleshooting

Issue	Cause	Solution
Does not update	Fixed range	Switch to a table and named range
Duplicates or blanks	Source data is not sufficiently cleaned	Use UNIQUE/SORT and TRIM/CLEAN
Dependent list is blank	Spill reference error	Check the `#` reference and Name Manager
Slow performance	Too many OFFSET/INDIRECT formulas	Replace with structured references or dynamic arrays

엑셀 이미지 전체 선택: Ctrl+A·선택 창·이동 옵션 1분 완성

Excel Data Lists: Master Basic, Dynamic, and Dependent Dropdowns