How to use UNIQUE Function in Excel

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)


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:

Add a Comment

Your email address will not be published. Required fields are marked *