How to Build a Dynamic Dropdown Source with SORT and UNIQUE in Excel

Create self-updating dropdown lists: sorted unique categories, a dependent vendor list and a list that skips blanks.

You will learn to

• Build an alphabetical list of distinct values
• Build a dependent list that changes with a selection
• Exclude blanks from a dropdown source

How it works

A dropdown list is only as good as its source. =SORT(UNIQUE(B2:B12)) produces an alphabetical list of every category in the data, with no duplicates, and it grows automatically when a new category appears. In Data Validation, choose List and set the source to the spill reference, such as =F2#, so the dropdown always matches the formula.

For a dependent dropdown, filter first: =SORT(UNIQUE(FILTER(C2:C12,B2:B12=I1))) lists only the vendors for the category chosen in I1. If the column has empty cells, filter them out with D2:D12<>"" so no blank entry appears in the list.

=SORT(UNIQUE(B2:B12))