What does the UNIQUE function do in Excel?

Updated

The UNIQUE function extracts the distinct values from a range or array and returns them as a dynamic array. It removes duplicates, is not case-sensitive, and works with text, numbers, dates, and other data types. Because the result is a dynamic array, it automatically updates when the source data changes — you don't need to re-enter or drag the formula.

How UNIQUE works

You give UNIQUE a range or array, and it returns one instance of each distinct value it finds. The output "spills" into the cells below (and to the right, if the input is multi-column), so the formula lives in a single cell but fills a block of results.

Key behaviors:

  • Duplicates collapse to one entry. If a value appears five times, it appears once in the output.
  • Not case-sensitive. "Apple" and "apple" are treated as the same value, so only one survives.
  • Works across data types. Text, numbers, dates, and mixed arrays are all handled.
  • Dynamic. Edit or add to the source range, and the spilled result resizes on its own.

Basic usage

The simplest form takes a single argument — the range or array to deduplicate:

=UNIQUE(A2:A100)

Input: the range A2:A100. Action: UNIQUE scans the range and keeps one copy of each distinct value. Expected result: a vertical list of unique values that spills downward from the cell holding the formula.

If the source is a two-column range like A2:B100, UNIQUE returns unique rows (combinations), not unique values per column.

Optional arguments

UNIQUE accepts two optional arguments after the array:

Argument Purpose Values
[by_col] Compare columns instead of rows FALSE (default) = compare rows; TRUE = compare columns
[exactly_once] Return only values that appear exactly once FALSE (default) = all distinct values; TRUE = only non-repeating values

For example, to list only values that occur exactly once:

=UNIQUE(A2:A100,,TRUE)

The empty middle argument keeps the default row comparison while setting exactly_once to TRUE.

Practical example

Suppose column A holds a list of customer cities with many repeats. =UNIQUE(A2:A100) in cell D2 produces a clean, de-duplicated list in D2 downward. Add a new city to column A, and it appears in the spilled list automatically — no copying or re-running needed.

Common points to watch

  • Spill range must be empty. If cells below or beside the formula already contain data, you'll get a #SPILL! error. Clear the blocking cells.
  • Dynamic arrays require a compatible Excel version. UNIQUE is part of the dynamic array family, alongside functions like SORT and FILTER.
  • Case differences are ignored, so if you need case-sensitive uniqueness, UNIQUE alone won't distinguish "Apple" from "apple."

UNIQUE is best thought of as a live, self-updating de-duplication tool: point it at a range, and it keeps a clean distinct list in sync with your data.