UNIQUE in Excel: what it does and how to use it

UNIQUE takes a list and returns it with the repeats removed, one of each value.

Syntax

UNIQUE(array, [by_col], [exactly_once])

When to use it

Finding out how many different customers, products or dates a list actually contains, rather than how many rows it has.

What trips people up

It needs Microsoft 365. On older versions the same answer comes from a COUNTIF, which is slower on a long list but works anywhere.

Watch it built, step by step

Count the distinct values — How many different, not how many rows

Questions

How do I count the unique values?

=COUNTA(UNIQUE(A2:A100)). Point it only at the filled rows: a blank cell in the range comes back from UNIQUE as a 0 and gets counted.

How do I list values that appear exactly once?

Set the third argument to TRUE: =UNIQUE(A2:A100,,TRUE) returns only the values with no repeats at all.

Does UNIQUE work in older Excel?

No, it needs Microsoft 365 or Excel 2021. To count distinct values in older versions, =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) works as long as there are no blank cells.

Related functions

COUNTIF — COUNTIF counts how many cells in a range match one condition.
TEXTJOIN — TEXTJOIN glues several pieces of text together with a separator of your choosing between them, and can skip the empty ones.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.