Return to List of Excel Functions

UNIQUE Function Examples – Excel & Google Sheets

This tutorial demonstrates how to use the Excel UNIQUE Function in Excel to return a list of unique values in a list or range.

UNIQUE Main Function

UNIQUE Function Overview

The UNIQUE Function returns a list of unique values in a list or range.

To use the UNIQUE Excel Worksheet Function, select a cell and type:

UNIQUE Function

(Notice how the formula inputs appear)

UNIQUE function Syntax and inputs:

array – The range or array from which to return unique rows or columns.

[by_col] (optional) – How to compare. TRUE will compare columns against each other and return the unique columns. FALSE (or omitted) will compare rows against each other and return the unique rows

[exactly_once] (optional) – A logical argument that when set to TRUE will return all distinct rows or columns that occur exactly once from the range or array and when set to  FALSE will return all distinct rows or columns from the range or array. When this argument is omitted it defaults to FALSE.

How to use UNIQUE Function

The UNIQUE function is used to extract unique items from a list.

For example, if we wanted to return a list of unique departments from our list in A2:A7, we enter the following formula in D2:

=UNIQUE(A2:A7)

UNIQUE EX 01

It can also be used to extract items that only appear once in a list.

For example, if we wanted to return a list of departments that appear only once in our list in A2:A7, we enter the following formula in D2:

=UNIQUE(A2:A7,FALSE,TRUE)

UNIQUE EX 02

To return unique items from a list

To extract a list of unique Employees we enter the following formula in D2:

=UNIQUE(A2:A7)

UNIQUE EX 3.1

If our list was across columns, we would enter the following formula in C4:

=UNIQUE(C2:H2,TRUE)

UNIQUE EX 3.2

We set the 2nd argument [by col] to ‘TRUE’ to enable the formula to compare the columns against each other.

To return items that only appear once in a list

To extract a list of Employees that appear only once, we enter the following formula in D2:

=UNIQUE(A2:A7,FALSE,TRUE)

UNIQUE EX 04

We set the 3rd argument [exactly_once] to ‘TRUE’ to extract items that only appear once in our list.

To return items in alphabetical order

To extract a list of unique Employees and sort their names in alphabetical order, we use the UNIQUE Function together with the SORT Function. We enter the following formula in D2:

=SORT(UNIQUE(A2:A7))

UNIQUE EX 05

 

To return items with blanks ignored

If a blank value exists in the list (selected range) it will be returned as a ‘0’.

UNIQUE EX 6.1

To ignore blanks in a list, we use the UNIQUE Function together with the FILTER Function  (maybe there could be a link to the FILTER Function article).

We enter the following formula in D2:

=UNIQUE(FILTER(A2:A7,A2:A7<>""))

UNIQUE EX 6.2

To create a dynamic Data Validation drop-down list

You can use the list generated by the UNIQUE function to create a data validation drop-down list.

In this example we’d like to create a drop-down where a user can select an Employee and view the departments they work in.

To first step is to create the list that the drop-down will use, we enter the following formula in D2:

=UNIQUE(A2:A7)

UNIQUE EX 07 New

The next step is to add Data Validation to cell F2. We add the source of our Data Validation list as ‘D2#’ . We use the ‘#’  sign after the cell reference ‘D2’  so that we can refer to the whole list that the UNIQUE Function generates.

 

UNIQUE-EX-07-Data-Validation List

UNIQUE EX 07 Data Validation

Issues

#SPILL!

This error occurs when there is a value in the Spill Range i.e. the range where the UNIQUE Functions lists the unique items.

To correct this error, clear the range that Excel highlights.

UNIQUE EX 8.1

#CALC!

This error occurs when the UNIQUE Function is unable to extract a list of unique or distinct items. In the example below, we’d like to extract a list of Employees that appear only once but because all Employees appear twice the #CALC! error is returned.

UNIQUE EX 8.2

UNIQUE Tips & Tricks

  1. Ensure that the cells below the input cell are blank to avoid the Spill Error, learn more about the Spill Error ‘here’ — add link to Intro to DAFs.
  2. UNIQUE can be used with other Excel Functions such as the FILTER Function and SORT Function to create more versatile formulas.

 

UNIQUE in Google Sheets

The UNIQUE Function works exactly the same in Google Sheets as in Excel:

UNIQUE Google Function