Skip to Content

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

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.

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:

UNIQUE 1 columns

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:

UNIQUE multiple columns

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:

UNIQUE across multiple columns

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