How to use the UNIQUE function in Google Sheets

As the name suggests, the Google Sheets UNIQUE function enables you to pull out unique rows from a range, discarding any data that’s duplicated. This is really handy when you have a large volume of data, such as responses from a Google Form. UNIQUE allows you to quickly identify which values (e.g., a person, or a product name) appear only once in the dataset.

To get a better idea of UNIQUE works, take a look at the following list of names.

You can see there are three duplicated entries: David, Eric, and Caroline.

If you’re familiar with Microsoft Excel, you probably know the Remove Duplicates option that enables you to find and delete any repeated values in a range of data. Google Sheets does not have the same feature but it has an alternative for removing duplicates: you guessed it, the UNIQUE function.

Syntax

• range – this is the group of cells that the function references and returns unique or distinct values from

How to use Google Sheets UNIQUE function

I will use a similar dataset to the example in the first image.

Note that the UNIQUE function is not one-dimensional. This means it can accept ranges with two or more rows/columns. But let’s start with a single-dimensional column, just to grasp the concept better.

Here’s an example:

When you type =UNIQUE(A2:A15) into cell C2 and hit the Enter key, the UNIQUE function returns all the unique values from range A2:A15.

Results start appearing from C2 (the cell where the function is) and flow downwards in the sheet, as you can see in the image below.

You’ll notice that the duplicated results, David, Eric and Caroline, are automatically removed from the result column.

UNIQUE function with multiple columns

What happens if you extend the input range to multiple columns? Let’s find out.

Here I’ve used both the Name and Age columns, as shown in the image below:

Just like before, UNIQUE returns unique values from the range. The results are displayed from cell C2 onwards.

You can see that the output values flow downwards in the spreadsheet and also extend one column to the right. That’s because Google Sheets returns the same number of columns as those in the input range. This may not happen with rows, because duplicates are eliminated.

In the example above, two of the duplicated results for Name (Eric and Caroline), turn out to be unique when combined with Age. Eric in row 3 is 23 years old, whereas Eric in row 15 is 25 years old. These are different data entries and not duplicates. However, there are two Davids aged 21, which the function reads as duplicates, so it eliminates one of them.

The fact that the UNIQUE function can filter data across multiple columns is extremely useful, but what if you have data across multiple columns, but you want to filter based on only one of the columns?

For example, what if you want to get rid of all the duplicated names in the example above while still keeping the ages in the output? If this is the case, I encourage you to look into the SORTN function.

UNIQUE function with SORT function

A helpful trick to clean up your spreadsheet and make the data easier to interpret is to use the SORT function in combination with the UNIQUE function. This will filter out all the duplicates and simultaneously sort the data so that the result is a sorted list of unique entries. In the example below, I do this with the formula =Sort(Unique(A2:B15)).

UNIQUE function with horizontal data

In the last example I’ll show you how to use the UNIQUE function for horizontal data in a row.

Then once the UNIQUE function has done its work, you can transpose it again back to a horizontal format. That’s why the TRANSPOSE function is used twice in the example above. The formula here is =Transpose(Unique(Transpose(B1:O1))).

Remember that UNIQUE needs space

• You must give the function sufficient empty space in the sheet to display its output correctly. If you have data in column D, the UNIQUE function does not have room to expand. If this happens, Google Sheets displays a #REF! error.

UNIQUE function: key tips

• Google Sheets does not allow you to delete the values returned by the UNIQUE function unless you do so in the cell where you entered the formula (in the above examples, that was cell C2).
• You may need to copy-paste the distinct values that UNIQUE returned. Copy the distinct values, and select a destination cell. Now, navigate to Edit > Paste > Paste Special > Paste values only. That way, the formula is removed but the values are retained.

If you simply want to count how many unique values appear in a specific range, learn how to use COUNTUNIQUE in our guide How to use the COUNTUNIQUE function in Google Sheets.