Data that has been imported from multiple sources often has the tendency to contain multiple copies of the same entries. As such, one of the prime tasks in data cleaning is to clear duplicate values, ensuring that you are only left with a clean set of unique records.
That’s where the UNIQUE function comes in. There are a number of ways to remove duplicates in Google Sheets, but in this tutorial, we are going to focus on the Google Sheets UNIQUE values function. This Google Sheets function helps extract unique values from a given range.
We will take a close look at what it does, its syntax, as well as different ways in which it can be used.
What Does the UNIQUE Function Do?
The UNIQUE function takes a range of cells in Google Sheets and returns the unique values in the range, after getting rid of duplicates.
The rows are returned in the order in which they appeared in the source range of cells. The great thing about the UNIQUE function, as opposed to other methods of duplicate removal, is that this function lets you remove duplicates without messing with the original data.
It also works dynamically, so any changes made to the original data also get reflected in the UNIQUE function’s result.
Syntax for the UNIQUE Function
The syntax for the UNIQUE function is as follows:
UNIQUE(range, filter_by_column, exactly_once)
- range is the data that we want to extract unique entries from
- filter_by_column is an optional parameter. It specifies if we want the data filtered by row or column. A FALSE value means we want it filtered by row, while a TRUE value means we want it filtered by column. By default this value is FALSE
- exactly_once is also an optional parameter. It specifies if we want only entries with no duplicates. A FALSE value means we want to include entries that have duplicates exactly once, while a TRUE value means that we want to completely eliminate any entry that has duplicates.
The range parameter can include either a range of column names or a range of cell references. The function will show the result starting from the cell in which you entered the formula.
Use Cases for the UNIQUE Function
To understand how to use the UNIQUE function, let us use a few examples. For the first few examples, we are going to be using the following data:
Using Google Sheets UNIQUE Function to Find Unique values in a Single Column
Let us consider the case where we want only the First names that are unique. In other words, we want to display all First names in the data set with each name appearing only once.
For this we will use the UNIQUE function as follows:
As you can see from the above screenshot, copies of the names John and Paul have been removed from the result, and we are left with just 4 first names.
Using Google Sheets UNIQUE Function to Find Unique Values in Multiple Columns
Now let us consider the case where we want to work with more than one column and we want to make sure that no two rows have the same combination of first and last names.
For this we will use the UNIQUE function as follows:
Notice that we used both columns A and B inside the UNIQUE function.
From the above screenshot we can see that individually, a first or a last name may repeat in a column. However, we cannot find any combination of first and last names appearing more than once.
This means John Smith’s name is not repeated, although it appears three times in the original dataset.
Using UNIQUE Function in Google Sheets to Filter Data by Unique Values
The UNIQUE function sometimes also works as a duplicate filter. This is because we can also use this function to filter out repeating values. In other words, any value that is repeating in the dataset can be removed from the output simply by changing the third parameter of the UNIQUE function to TRUE.
Here’s how we can use the UNIQUE function to retain only non-recurring rows in a dataset:
Notice that we also specified the second parameter as FALSE (even though it is already FALSE by default). Whenever you want to specify the third parameter, you have to also explicitly specify the other parameters that come before it, even if they are optional.
Using UNIQUE Function to Filter Data by Column
We can use the UNIQUE function to filter both by row and column. So far we have seen only examples of filtering unique values by row. Let us now look at an example where we use the UNIQUE function to filter entries by column.
Consider the following dataset of 4 columns.
Out of these, two columns have exactly the same sequence of values, while the rest 2 have a different sequence of values. If we want to retain only the unique columns, we can use the UNIQUE function as follows:
As you can see, the third column has been removed since it was a duplicate of the first column.
Points to Remember About the UNIQUE Function
Here are a few points to keep in mind when using the Google Sheets UNIQUE values function:
- The UNIQUE function removes duplicates dynamically, so any changes you make to the source data get automatically reflected in the output of the UNIQUE function.
- You cannot make any changes to the output of the UNIQUE function. If you try to do so, you will end up getting a #REF! error.
- If you want to work on the output of the UNIQUE function, you can select the cells that comprise the output of the function, copy them by pressing CTRL+C and then paste them by value in the same place by pressing CTRL+SHIFT+V.
The UNIQUE function works great if you want to remove records where all the values are repeating. However, consider the following case, where you want to remove a record when a part of it is repeating:
In the above example, we want to retain just one copy of John Smith’s record, but we also want to retain the sum of all his sales amounts. In such cases, the UNIQUE function might not do a very good job.
As an alternative, though, we could use a Google Sheets QUERY function to extract unique rows, while keeping some of the columns intact. However, the explanation of this technique is beyond the scope of this tutorial. To learn more about the Google Sheets QUERY function, click here.
In this tutorial we went over the syntax and usage of the UNIQUE function. We also demonstrated different ways and use-cases in which the Google Sheets UNIQUE function can be applied to get different types of results involving unique values. We hope that through this tutorial we were successful in giving you a well-rounded idea about the Google Sheets UNIQUE function.
You might also like to read this article on two ways to find unique values in Google Sheets.