If you need to locate the unique values in your spreadsheet across many columns, you can use the UNIQUE function in Google Sheets.
In this article, I’ll teach you how to use Google Sheets to construct a unique list of data across various columns.
We’ll use UNIQUE, a built-in function in Google Sheets, to construct our one-of-a-kind data set.
When given a range of data, the UNIQUE function returns unique rows and removes duplicates.
UNIQUE has the following syntax:
= UNIQUE(range) here,
range – this is the data for which you want unique values returned.
How to use UNIQUE on one Column
TO USE THE WORD UNIQUE Simply enter the range of columns into the formula on one column, and it will return the unique values from that column.
Here’s an example of how this can be done:
My formula is set up in the preceding example to only return the unique values from column A.
How to Use UNIQUE on Multiple Columns
UNIQUE can also yield a whole unique row when applied to several columns.
To accomplish this, simply enter the complete range into the formula for which duplicates should be removed.
This is how it would appear:
As you can see in the example above, all duplicate rows have been deleted, and each unique row has been returned.
How to Use UNIQUE across Multiple Columns
The preceding code will provide the unique rows from your range, however you may use UNIQUE and the FLATTEN function to retrieve the unique values across several columns.
The FLATTEN function will combine your range or ranges into a single column.
The syntax for combining UNIQUE and FLATTEN will be:
=UNIQUE(FLATTEN(range1, [range2…]) here,
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
In a spreadsheet, this will look like this:
As you can see, any duplicate values in any of my cells across my whole range are deleted using this method, leaving only one single unique value.
In Google Sheets, there are a variety of approaches to get the unique values. If you’re a regular spreadsheet user, you should absolutely learn how to use the UNIQUE function.
Take the time to experiment with each of the approaches in your own sheet to ensure that you fully get how this works. It will undoubtedly come in helpful at some point in the future.