Excel Function: UNIQUE

The Excel function UNIQUE returns the list of unique values from a range of cells or a table.




=UNIQUE(array, by_column, unique_values)

List of Unique Values

In this example, the aim is to obtain the unique list of countries from column A:

excel list countries unique

Select the UNIQUE function and simply enter the cell range of the countries to obtain the unique list of countries:

excel unique function country list

To obtain a sorted list of countries, you can add the SORT function:

excel unique function sorted country list

If you select a range containing several columns, the UNIQUE function will return the list of unique rows:

excel unique function table rows

Search by Column

By default, the search for unique values is carried out row by row.

To list the unique columns, enter the value 1 (or TRUE) as the second argument:


excel unique function table columns png

Search for Unique Values

To get only the values present once in the table, enter the value 1 (or TRUE) as the third argument:

excel unique function search values

For instance, the row Brazil No is present more than once in the table, so it is not listed here.

If needed, you can download the Excel file used here: unique.xlsx
Note: this function is only available with Office 365.