Using UNIQUE Function in Google Sheets

on that can be used to filter out duplicates in a column of entries and leave only unique values. In this tutorial, we learn how to use this function, which also serves as Google Sheets’ alternative to the SQL SELECT DISTINCT query.

Table of Contents
  1. Apply UNIQUE function to a single column
  2. Apply UNIQUE function to more than one column
  3. UNIQUE function as alternative to SELECT DISTINCT in SQL
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. Apply UNIQUE function to a single column
  2. Apply UNIQUE function to more than one column
  3. UNIQUE function as alternative to SELECT DISTINCT in SQL
4 Minutes

The UNIQUE function is a Google Sheets function that can be used to filter out duplicates in a column of entries and leave only unique values. In this tutorial, we learn how to use this function, which also serves as Google Sheets’ alternative to the SQL SELECT DISTINCT query. 


Before we start, note that the UNIQUE function works for columns, not for rows. 

Apply UNIQUE function to a single column


The syntax of UNIQUE function is straightforward:


=UNIQUE(range_to_filter)


Where range_to_filter is the range containing values that we want to filter using the UNIQUE function. For our first example, we want to filter a column of values to find the unique values. We will apply the UNIQUE function to the row labeled States

Original array of values.


As the range of values under the States column is A2:A11, we will set it as the input to the function:


=UNIQUE(A2:A11)


The result is:

UNIQUE function applied to the first column of the array.


Apply UNIQUE function to more than one column


We can apply the UNIQUE function to a range containing more than one column. The unique function will filter out duplicate rows, where all the columns in a row are similar to another row. For our first example:

Original array of values.


We just copied the example in the previous section. We will apply the UNIQUE function to the whole range:


=UNIQUE(F2:G11)


And the result is:

UNIQUE function applied to the entire array. The UNIQUE function filters out all duplicate rows. Since there is no duplicate row, the original rows were retained.


This is expected, since while the first column contains duplicates, the second does not - and Google Sheets consider them unique.


Now let us modify the input a bit:

Original array of values, but has duplicate rows this time. 


As you can see, there are some duplicates: the entry for New York, New York and Los Angeles, California. We will apply the UNIQUE function to the range:


=UNIQUE(L2:M11)


And the result is:

UNIQUE function applied to the entire array. The UNIQUE function filters out all duplicate rows, giving us a shorter list of results this time.


UNIQUE function as alternative to SELECT DISTINCT in SQL

It turns out you can apply the UNIQUE function to the results of the QUERY function. Specifically, you can insert the QUERY function inside the UNIQUE function. For this section, we will replicate the result of the two previous sections.


For a single column:


=unique(query(A16:B25,"select A"))


The result:

The combination of UNIQUE and QUERY function applied to the single column of the original array. 


For more than one column:


=unique(query(F16:G25,"select F, G"))


The result:

The combination of UNIQUE and QUERY function applied to the single column of the original array that contains duplicate values. 


Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

November 14, 2021

Using UNIQUE Function in Google Sheets

Google Sheet spreadsheet

The UNIQUE function is a Google Sheets function that can be used to filter out duplicates in a column of entries and leave only unique values. In this tutorial, we learn how to use this function, which also serves as Google Sheets’ alternative to the SQL SELECT DISTINCT query. 


Before we start, note that the UNIQUE function works for columns, not for rows. 

Apply UNIQUE function to a single column


The syntax of UNIQUE function is straightforward:


=UNIQUE(range_to_filter)


Where range_to_filter is the range containing values that we want to filter using the UNIQUE function. For our first example, we want to filter a column of values to find the unique values. We will apply the UNIQUE function to the row labeled States

Original array of values.


As the range of values under the States column is A2:A11, we will set it as the input to the function:


=UNIQUE(A2:A11)


The result is:

UNIQUE function applied to the first column of the array.


Apply UNIQUE function to more than one column


We can apply the UNIQUE function to a range containing more than one column. The unique function will filter out duplicate rows, where all the columns in a row are similar to another row. For our first example:

Original array of values.


We just copied the example in the previous section. We will apply the UNIQUE function to the whole range:


=UNIQUE(F2:G11)


And the result is:

UNIQUE function applied to the entire array. The UNIQUE function filters out all duplicate rows. Since there is no duplicate row, the original rows were retained.


This is expected, since while the first column contains duplicates, the second does not - and Google Sheets consider them unique.


Now let us modify the input a bit:

Original array of values, but has duplicate rows this time. 


As you can see, there are some duplicates: the entry for New York, New York and Los Angeles, California. We will apply the UNIQUE function to the range:


=UNIQUE(L2:M11)


And the result is:

UNIQUE function applied to the entire array. The UNIQUE function filters out all duplicate rows, giving us a shorter list of results this time.


UNIQUE function as alternative to SELECT DISTINCT in SQL

It turns out you can apply the UNIQUE function to the results of the QUERY function. Specifically, you can insert the QUERY function inside the UNIQUE function. For this section, we will replicate the result of the two previous sections.


For a single column:


=unique(query(A16:B25,"select A"))


The result:

The combination of UNIQUE and QUERY function applied to the single column of the original array. 


For more than one column:


=unique(query(F16:G25,"select F, G"))


The result:

The combination of UNIQUE and QUERY function applied to the single column of the original array that contains duplicate values. 


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.