The Excel UNIQUE function returns a list of unique values in a list or range. Values can be text, numbers, dates, times, etc.
Syntax:= UNIQUE (array, [by_col], [exactly_once])
The UNIQUE function syntax has the following arguments:
- array: the range or array to return values from.
- [by_col]: an optional argument where FALSE = compare by row and TRUE = compare by column. If excluded, it will default to FALSE. I can’t think of many circumstances where this would be useful, but at least the option is there.
- [occurs_once]: This argument depends on your interpretation of the word unique. If you want a list which includes only the items which appear once then use TRUE. If you want a list which contains one instance of each item (i.e., a distinct list), then use FALSE. This is an optional argument and if excluded will default to FALSE.
Example: Let’s look at some Excel UNIQUE function examples and explore how to use the UNIQUE function as a worksheet function in Microsoft Excel:
Let’s say we want to find a list of distinct values from the Item column (C) in the table below:
Syntax: =UNIQUE(B2:B16)
Result:
Extract a list of Unique Values
If you want to find a list of values that only occur once in a list, i.e. a list of truly unique values, the occurs_once argument is set to TRUE. In the example below, you can see ‘Sales’ and ‘Marketing’ is the only unique value in the range A2:A13:
Extracting UNIQUE Rows of Values
In the example below, you can see there are whole rows containing duplicates. Setting the by_col and occurs_once arguments to FALSE we get a list of unique rows, as shown below:
