The UNIQUE function
The list of a column's values, each once: the customers, the regions, the products. Kept up to date when the source changes.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
In a French Excel: =UNIQUE(tableau; [par_col]; [une_seule_fois]).
Arguments
- array: the range.
- by_col: TRUE to compare columns rather than rows.
- exactly_once: TRUE to keep only the values present exactly once.
An example
=SORT(UNIQUE(A2:A500))
The list of customers, without duplicates, in alphabetical order.
Pitfalls
- Case does not matter: "Smith" and "SMITH" count as one.
- Extra spaces make two values out of one.
- Missing from Excel 2019 and earlier.
Going further
- FILTER.
- Remove duplicates: remove the duplicate rows of a file.
Frequently asked questions
- How do I count distinct values?
=COUNTA(UNIQUE(A2:A500)), or in every version=SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&"")).