How to use the UNIQUE formula in Google Sheets

Image for unique formula in google sheets

Written by Laura Tennyson

Jul 8, 2020

As the name suggests, the Google Sheets UNIQUE formula 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) occur only once in the dataset.

To understand how UNIQUE works, take a look at the following list of names. You can see there are two duplicated entries: Eric and Caroline.

UNIQUE Formula: List of Names in Google Sheets

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

Syntax

UNIQUE(range)

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

How to use UNIQUE formula

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

Note that UNIQUE formula is not one-dimensional, meaning 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:

UNIQUE Formula: Name and Ages in Google Sheets

When you type the formula into cell C2 and hit the Enter key, the UNIQUE formula returns all the unique values from range A2:A15. Results start appearing from C2 (the cell where the formula is) and flow downwards in the sheet, as you can see in the image below. You’ll notice that the duplicated results, Eric and Caroline, are automatically removed from the result column.

UNIQUE Formula: Example in Google Sheets

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:

UNIQUE Formula: Extending Input Range

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.

UNIQUE Formula: Usage Results

In the example above, although there are duplicated results for Name (Eric and Caroline), when combined with Age they turn out to be unique. 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, had there been another Eric aged 23, the formula would have read that as a duplicate and it would not appear in the output.

Remember that UNIQUE formula needs space

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

UNIQUE formula: key tips

  • Google Sheets does not allow you to delete the values returned by the UNIQUE formula 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 the UNIQUE formula 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 and the values are retained.

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

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

You may also like…

Share This