Skip to content

Learn About the UNIQUE Function

This article will teach you how to use the UNIQUE function.

This function is available in both Microsoft Excel and Google Sheets, and it works the same way regardless of which spreadsheet program you use it in.

What Does It Do?

The UNIQUE function allows you to find and return unique values from a list.

For example, let’s say you have the following list of values.

  • Red
  • Green
  • Blue
  • Red

You can use the UNIQUE function to return the unique values from the list, which are:

  • Red
  • Green
  • Blue

How Does It Work?

The UNIQUE function accepts three arguments.

=UNIQUE( Array, By_col, Exactly_once )

Array – The range of cells you want to get unique values from

By_col – A TRUE or FALSE value indicating if you want to return unique rows or columns – (optional)

Exactly_once – A TRUE or FALSE value indicating whether you want a unique list with no duplictes or only the values that occur once – (optional)

The UNIQUE function can work in various ways depending on how you configure these three arguments. Take a look at the different examples below.

Get Distinct Values from a Single Column

The UNIQUE function below gets a list of distinct values from a single column of data.

Only the first argument is required, which is the range of data. The UNIQUE function then returns a list of unique values.

Get UNIQUE Rows from Multiple Columns

The UNIQUE function below gets a list of unique rows from two columns of data.

Once again, only the first argument is required. The UNIQUE function then returns a list of unique rows.

Get UNIQUE Columns from Multiple Rows

The UNIQUE function below gets a list of unique columns from two rows of data.

This time, two arguments are required. The data range for the first argument is always required.

The second argument must be TRUE to force the UNIQUE function to return unique columns rather than unique rows.

The UNIQUE function then returns a list of unique columns.

Get Only Values That Occur Exactly Once

The UNIQUE function below gets a list of values that occur only once.

Two arguments are required once again. The data range for the first argument is always required.

The second argument can be omitted, and the third argument must be TRUE. This will force the UNIQUE function to return only values which occur exactly once.

The UNIQUE function then returns a list of values occurring only once.

And those are the different ways that you can use the UNIQUE function in either Google Sheets or Microsoft Excel.

This Post Has One Comment

  1. I use unique all the time, it’s one of my favorites! I didn’t know that you could use it to get a list of items that only occur once, that’s going to be so helpful!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top