How to show unique values in a drop down list

Answer

What is the need for unique values in a dropdown list?

A dropdown list requires unique values to avoid redundant entries that confuse users during data selection. Raw data columns contain duplicates from multiple records. Extracting distinct values creates a clean, non-repetitive dropdown source for data validation.

How to extract unique values using the UNIQUE function

To extract unique values for a dropdown list, use the UNIQUE function combined with FILTER to create a dynamic array of distinct, non-blank values from your source column.

Setting up the UNIQUE formula

Enter this formula in a helper cell (e.g., A2) on a dedicated "Lists" sheet:

=UNIQUE(FILTER(SourceTable[ColumnName], SourceTable[ColumnName]<>""))

Replace "SourceTable[ColumnName]" with your actual Table and column reference, such as DataTable[Category] or a standard range like Sheet1!B$2: B$1000. The formula spills automatically, displaying all unique values starting from the formula cell.

Handling blanks and errors

The FILTER function nested within UNIQUE excludes blank cells using the criteria <>"". Add an IFERROR wrapper for source data containing #N/A or other errors:

=IFERROR(UNIQUE(FILTER(SourceTable[ColumnName], SourceTable[ColumnName]<>"")),"")

Sorting unique values

Wrap the formula with SORT for alphabetical or numerical order:

=SORT(UNIQUE(FILTER(SourceTable[ColumnName], SourceTable[ColumnName]<>"")))

This presents dropdown options in a user-friendly sequence.

How to reference the spill range in data validation

To use the unique values in a dropdown list, reference the spill range directly in the Data Validation source field.

1. Select the cell or range where you want the dropdown list.
2. Navigate to Data > Data Validation.
3. Select "List" from the Allow dropdown.
4. Enter the spill reference in the Source field: =Lists!$A$2#

The hash symbol (#) after the cell reference indicates the entire spill range. The dropdown updates dynamically as source data changes.

How to create a dynamic named range from the spill range

To create a dynamic named range from the UNIQUE formula output, define the spill range in Name Manager.

1. Navigate to Formulas > Name Manager > New.
2. Enter a name such as "UniqueList" in the Name field.
3. Enter the spill reference in the Refers to field: =Lists!$A$2#
4. Click OK to save.

Use the named range in Data Validation by entering =UniqueList in the Source field. This provides a cleaner reference and allows reuse across multiple dropdown cells.

What is the complete workflow for unique value dropdowns?

The dropdown list now displays distinct values extracted from your source data and updates automatically when new unique entries appear in the source column.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report