Excel Dynamic Dropdown Lists: Remove Duplicates, Sort, and Create Dependent Lists with UNIQUE and SORT
Summary: The keys to dropdown lists are tables (ListObjects) and dynamic arrays. Convert the source data to a table, then use UNIQUE to remove duplicates, SORT to sort, and FILTER to exclude blanks, creating a clean named range. When the Data Validation list refers to that name, the dropdown updates automatically as the source grows. You can extend the same approach to parent-child dependent lists.
1) Why use tables and dynamic arrays?
With a standard range, adding rows can leave your Data Validation list incomplete and cause errors. In contrast, a table created with Ctrl+T expands automatically, and the UNIQUE/SORT/FILTER combination updates the list immediately as data changes. Save this dynamic range as a name and connect it to Data Validation for virtually maintenance-free management.
2) Exclude blanks, remove duplicates, and sort
=SORT(UNIQUE(FILTER(ItemsTbl[Item], ItemsTbl[Item]<>""))) Place this formula in a helper area of the worksheet, or define it as dd_Items in Name Manager. For reverse alphabetical order, use SORT(…,-1).
3) Connect to Data Validation
Select the target input range, then go to Data → Data Validation → Settings → Allow: List and enter =dd_Items. The dropdown now updates automatically whenever values are added to or removed from the source table.
4) Dependent dropdown lists
If the parent is Category and the child is Detail, create the following name. (This assumes the parent selection cell is B2.)
dd_Sub := SORT(UNIQUE(FILTER(Tbl[Detail], Tbl[Category]=B2)))Enter =dd_Sub as the source for the child dropdown’s Data Validation rule, and it will change automatically based on the parent selection. Handle empty results with IFERROR(…, "").
5) Alternatives for older versions (without UNIQUE)
- Advanced Filter: Go to Data → Advanced → select Unique records only → assign the result range the name
dd_Items. - PivotTable: Output the row label list as values, then assign it a name.
- Power Query: Go to Data → From Table/Range → Remove Duplicates → Close & Load (Connection Only) → refer to it by name.
6) Practical tips and error prevention
- Cleaning blank rows and extra spaces in the source table in advance (with Data Validation and
TRIM/CLEAN) keeps the dropdown list tidy. - If the list includes formulas, use Paste Values to finalize the values before using it.
- Merged cells are a common cause of Data Validation errors. Avoid them where possible, and use Center Across Selection instead.