# UNIQUE Function Examples – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 9, 2023

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

## How to use the 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)``

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 Items from a List

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

``=UNIQUE(A2:A7)``

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

``=UNIQUE(C2:H2,TRUE)``

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

## Return Items Only Appearing Once

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

``=UNIQUE(A2:A7,FALSE,TRUE)``

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

## 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))``

## Ignore Blanks

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

To ignore blanks in a list, we use the UNIQUE Function together with the FILTER Function.

We enter the following formula in D2:

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

## 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 department 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)``

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.

## 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.

### #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 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.