The Google Sheets Unique Function: Comprehensive 2024 Guide

Data imported from multiple sources often contains multiple copies of the same entries or other errors. 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 Google Sheets unique values in the column.

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 (aptly named) function helps extract unique values from a given range.

We will take a close look at what it does, its syntax, and different ways you can use it.

What Does the UNIQUE Function Do?

The UNIQUE function is used to filter unique values in Google Sheets by taking a range of cells and returning 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.

When to Use the UNIQUE Function

The UNIQUE function can be used in various situations:

• You can use Google Sheets to get unique values from columns from your data into a separate table.
•  You can also use the UNIQUE function to remove duplicates from your data.

The Syntax for the UNIQUE Function

The syntax for the UNIQUE function is as follows:

UNIQUE(range, filter_by_column, exactly_once)

Here,

• range is the data that we want to extract unique values 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 eliminate any entry that has duplicates completely.

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 will use the following data:

You can find our example sheet here.

Using the 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 Google Sheet function as follows:

=UNIQUE(A2:A8)

1. In a new column, select a cell
2. Type in the equal sign and the UNIQUE formula
3. Select the cells in the column with the data
4. Close the brackets and hit Enter

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. We can use Google Sheets’ unique with multiple columns.

For this, we will use the UNIQUE Google Sheets function as follows:

=UNIQUE(A2:B8)

1. In a new column, select a cell
2. Type in the equal sign and the UNIQUE formula
3. Select the cells in the two columns with the data
4. Close the brackets and hit Enter

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.

UNIQUE Function with the SORT Function

You can also make an advanced UNIQUE formula in Google Sheets by combining it with the SORT function in Google Sheets. With this combination, you can remove duplicates and arrange your data in a specific order simultaneously.

For example, in our example sheet, we can replace the UNIQUE formula with the following:

=SORT(UNIQUE(A2:B8))

The result is a list of the unique names arranged in Alphabetic order.

The SORT function arranges things in alphabetical order from A to Z. If you want any other order, you can, for example, from Z and A, you can use the 2nd argument and 3rd argument.

Using UNIQUE Function in Google Sheets to Filter Data by Unique Values

The UNIQUE function of Google Sheets 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:

=UNIQUE(A2:B8,FALSE,TRUE)

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 also have to explicitly specify the other parameters that come before it, even if they are optional.

Using the 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 Sheet UNIQUE values function:

• The UNIQUE function is used for discarding 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 filter Google Sheets 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.

Conclusion

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. I like simplifying complex topics (like I did with my guide to the OFFSET function) and am happy to answer any lingering questions in the comments.

You might also like to read this article on two ways to find unique values in Google Sheets.

Related:

• How to Hide Zero Values in Google Sheets
• How to Filter By Color in Google Sheets
• How to Delete Empty Rows in Google Sheets
• How to Count Cells with Specific Text in Google Sheets
• How to Search in Google Sheets
• Conditional Formatting Based on Another Cell