How to Create a Unique List of Data across Multiple Columns in Google Sheets

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.

UNIQUE Function

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:

UNIQUE 1 columns

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:

UNIQUE multiple columns

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:

UNIQUE across multiple columns

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.

Harshita Mathur

My name is Harshita Mathur. I come from Jaipur, Rajasthan. I am a law student. Apart from this I am an amateur writer.

Recent Content