Skip to content

Create a List of Unique Values

This article will show you how to create a list of unique values in Google Sheets.

What does that mean? Take a look at the example below. Let’s say that you have the following list of data.

Now let’s say you only want the unique values from this list. You can use the UNIQUE function and end up with a list of only the unique values with no duplicates.

If you would like to learn how this all works, read on!

Use the UNIQUE Function

To get a list of unique values for a list, you can use the UNIQUE function. The UNIQUE function can be used in a few different ways, but we will cover its basic use for now.

To get the unique values from a list, set up the UNIQUE function with the following argument.

=UNIQUE(list_of_values)

The UNIQUE function will take the list of values and return each specific value only once. This gives you a list of unique values.

Take a look at the color list below.

The UNIQUE function references the list of colors and then returns each specific color only once.

Use the UNIQUE Function Across Columns

What if your list happens to be in a single row across many columns instead?

The UNIQUE function can still work in this case. The only difference is that you need to include a second argument. You must set the second argument in the UNIQUE function to TRUE.

=UNIQUE(list_of_values, TRUE)

Setting the second argument to TRUE will trigger the UNIQUE function to look across columns rather than down rows.

Look at how the UNIQUE function is used in the example below.

The second argument of the UNIQUE function is set to TRUE, so it looks for unique values across the many columns.

Get Unique Values from Many Columns and Rows

What happens if you have lists of items in more than one column?

Can the UNIQUE function get a list of unique values from this setup?

By itself, the UNIQUE value will not work in this scenario. The UNIQUE function can only work with a single column or a single row of values at a time.

To get a list of unique values from this setup, you must combine the UNIQUE function with the FLATTEN function.

What is the FLATTEN Function?

The FLATTEN function is a simple function in Google Sheets that takes data from several columns and combines it into a single column.

Take a look at the example below.

The FLATTEN function takes the two columns of letters and turns them into a single-column list.

Since the UNIQUE function can only work with a single row or column of data, the FLATTEN function is the perfect solution.

Combine the UNIQUE and FLATTEN Functions

Here is how you combine the UNIQUE and FLATTEN functions to get your list of unique values.

=UNIQUE( FLATTEN(range_of_values) )

The FLATTEN function goes first in the calculation. It takes multiple columns of data and transforms them into a single-column list.

This list is passed to the UNIQUE function, which creates your list of unique values.

Take a look at how these two functions are used in the example below to get a list of unique colors.

How to Exclude Columns

What happens if you want to get a list of unique values from only specific columns while ignoring others?

For example, what if you want the unique colors from the list in column A and column C but not column B?

Here is how you would set up your formula.

=UNIQUE( FLATTEN(column_A_Range, column_C_range) )

The FLATTEN function can take on more than one argument. Each argument can be a range you want to include. In the case of your color lists, you can include the range of colors in column A as the first argument and the range of colors in column C as your second argument.

The FLATTEN function takes these two ranges and combines them into a single array of colors. This list is passed to the UNIQUE function, which generates your list of unique colors.

How awesome is that?

Now you know how to create a list of unique values from many different scenarios! Thank you for reading, and be sure to comment below if you have any questions.

This Post Has 3 Comments

  1. I use UNIQUE in Excel all the time, but I had no idea about FLATTEN. This is so much more helpful than just UNIQUE by itself! I also didn’t know about the horizontal list that could be created with UNIQUE. Are these functions also available in Excel?

    1. The UNIQUE function is available in the Microsoft 365 version of Excel, but the FLATTEN function is currently not available in any version of Excel. Thanks for asking! I hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top