Excel UNIQUE Function

The UNIQUE function returns a de-duplicated list from a range with one formula: =UNIQUE(A2:A100). Unlike Remove Duplicates, it never touches your source data and refreshes automatically when the source changes.

How Do You Get Unique Values From a List in Excel?

Point UNIQUE at the range and it spills the distinct values into the cells below, in the order they first appear.

=UNIQUE(A2:A100)
' A2:A100 = West, East, West, North, East...
' Result: West, East, North

=UNIQUE(A2:B100)
' Multi-column ranges dedupe on whole rows, not single cells

What Does the by_col Argument Do?

The second argument switches comparison from rows (FALSE, the default) to columns (TRUE) — use it when your data runs sideways across a row.

=UNIQUE(B1:H1, TRUE)
' Dedupes values laid out horizontally in row 1

What Does exactly_once Do?

The third argument set to TRUE returns only values that appear exactly one time, which is how you find non-repeating entries rather than a distinct list.

=UNIQUE(A2:A100, FALSE, TRUE)
' A2:A100 = West, East, West, North
' Result: East, North   (West appears twice, so it's excluded)

How Do You Combine UNIQUE With SORT and FILTER?

Nest UNIQUE inside SORT for an alphabetized distinct list, and feed it a FILTER to dedupe only rows that match a condition.

' Sorted unique list
=SORT(UNIQUE(A2:A100))

' Unique customers with orders over 500
=UNIQUE(FILTER(A2:A100, C2:C100>500))

' Sorted, filtered, deduped in one formula
=SORT(UNIQUE(FILTER(A2:A100, C2:C100>500)))

This pattern is the standard way to build self-updating dropdown sources: point Data Validation at the spill range with =$E$2#.

Common Pitfalls

• #SPILL! errors: UNIQUE needs empty cells below (or beside) the formula. Clear the spill zone.
• Case is ignored: "west" and "West" count as the same value. UNIQUE is not case-sensitive.
• Trailing spaces create duplicates: "West " and "West" are different values. Wrap the range in TRIM if the data is dirty: =UNIQUE(TRIM(A2:A100)).
• Confusing exactly_once with distinct: exactly_once=TRUE drops anything that repeats. If you want each value once, leave it FALSE.
• Blank cells return 0: empty cells in the range show up as a 0 in the output. Filter them out first: =UNIQUE(FILTER(A2:A100, A2:A100<>"")).