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.
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,
- 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:
When you type the formula into cell C2 and hit the Enter key, the UNIQUE formula returns all the unique values from range A2
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
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
Remember that UNIQUE formula needs space
- You must give the formula sufficient empty space in the sheet to display its output correctly.
If you havedata in column D, the UNIQUEformula does not have room to expand. If this happens, Google Sheets displays a #REF! error.
UNIQUE formula: key tips
- Google Sheets does not allow you to delete the values returned by the UNIQUE
formulaunless 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.