If you need to find the unique values in your spreadsheet across multiple columns, there is a way to do that in Google Sheets with this UNIQUE function.
In this tutorial, I will show you how to create a unique list of data across multiple columns in Google Sheets.
Table of Contents
UNIQUE Function
Google Sheets has a built-in function called UNIQUE that we will be using to create our unique list of data.
The UNIQUE function returns unique rows and removes duplicates when provided a range of data.
The syntax of UNIQUE is:
=UNIQUE(range)
- range – this is the data that you want the unique values to be returned for
Using UNIQUE on One Column
To use UNIQUE on one column you simply put in the range of the columns into the formula and it will return the unique values from the single column.
Here is an example of this will work:
You can see in the above example, my formula is set up to only return the unique values from column A.
Using UNIQUE on Multiple Columns
UNIQUE can also be used on multiple columns to return an entire unique row.
To do this you would simply put the entire range into the formula that you want to remove the duplicates for.
Here is how this would look:
You can see from the above example, all of the duplicates are removed and each unique row is returned.
Using UNIQUE Across Multiple Columns
The above example will return the unique rows from your range, but if you want to return the unique values across multiple columns, this can be done by combining UNIQUE with the FLATTEN function.
The FLATTEN function will take your range or ranges and return them into a single column.
To combine UNIQUE and FLATTEN the syntax will be:
=UNIQUE(FLATTEN(range1,[range2,…])
- range – this is the range that contains the data that you want to return the unique values for
- range2 – you can add additional ranges here. This argument is optional
Here is how this will look in a spreadsheet:
You can see that with this method if there are any duplicate values in any of my cells across my entire range, they are removed so that one single unique value remains.
Closing Thoughts
There are many different ways to return the unique values in Google Sheets. The UNIQUE function is one that you definitely take the time to learn if you are a regular spreadsheet user.
Take the time to try out each of the methods in your own sheet so that you can really understand how this works. It will surely come in handy somewhere down the line.
More Google Sheets Tutorials:
How to Count Unique Values
How to Check if a Value is in a Range