How to Find Unique Values in Google Sheets

Finding unique values in Google Sheets is useful to clean up huge amounts of data. This is to make sure that the data set is free from incomplete, irrelevant, and redundant records. 

Unique values in Google Sheets mean values that are one of a kind. This implies that the range of data does not have any duplicated values, making it “Unique”.

For those who are dealing with huge sets of data, we got you! By learning how to identify unique values in Google Sheets, you will be able to remove unwanted data such as duplicated entries. 

In this tutorial, we would be demonstrating two ways to find unique values from Google Sheets, namely:

  1. Remove Duplicates Tool
  2. UNIQUE Function

 

 

Using Remove Duplicate Tool to Find Unique Values 

The Remove Duplicates tool is a built-in tool in Google Sheets that allows us to remove duplicates in just three clicks. 

 

Example 1:

Picture yourself running a skincare brand. To collect customer data, it is standard procedure to record the customer’s name, email address, and age after every purchase. 

By the end of each month, you would like to email your customers newsletters on the latest promotions for the coming month. However, the data collected has duplicated entries by customers who purchased more than once during the month. 

Let’s learn how to clean up the data collected to identify unique values only. 

 

  1. Select the range of data that you would like to remove duplicates. In our example, it would be A2:D12.

 

  1. After selecting the range of data, select “Data”, then select “Remove Duplicates”.

 

  1. Once you selected the “Remove Duplicates” option, a pop-up would appear. If your data range selected includes the headers, you can tick the “Data has header row”. If not, leave it unticked. 

 

  1. Under the “Column to analyze”, you get the option to select certain columns or all columns selected. In our case, since we want to get rid of all duplicated records, we will tick “Select all”. 

 

  1. Select “Remove duplicates”. 

 

  1. After selecting “Remove duplicates”, a message box would appear to notify you on how many duplicated entries are identified and removed, as well as how many unique values are remained. 

 

  1. Row 10, 11, and 12 would be removed, as these are duplicates. Your data would now look like this: 

 

The “Remove duplicates” tool would only retain the first occurrence of the value, deleting all other repeating values. 

As you can see from the image below, there are 3 sets of repeated values. Hence, rows 2,4, and 7 are retained. Whereas rows 10, 11, and 12 are removed. 

How to Find Unique Values in Google Sheets - Sheetaki
This tool makes changes directly on the original set of data, as shown in our example. 

However, if you would like to retain and see a comparison between the original set and the unique values identified, using the UNIQUE function would be a better alternative. 

 

 

Using UNIQUE Function to Find Unique Values 

The UNIQUE function returns a list of unique values in a list or range. Values can be in text, numbers, dates, times, etc. 

Similar to the “Remove duplicates” tool, this function also removes duplicated entries. The only difference is that the UNIQUE function retains the original dataset. 

As shown in the image below, the second set of data is generated solely from the UNIQUE function.

How to Find Unique Values in Google Sheets - Sheetaki

 

 

The Anatomy of the UNIQUE Function

The way we write the UNIQUE function is: 

= UNIQUE(array)

Let us help you understand the context of the function:

  • The equal sign = is how we start any function in Google Sheets.
  • UNIQUE() is our function. We need to add one compulsory attribute, namely the array, to make it work correctly.
  • The array is the range of data selected to return unique rows or columns.

We would be using the same dataset in Example 1, to demonstrate how to apply the UNIQUE function. 

 

Example 2:

  1. Simply click on the cell that you want to write down your function. In this example, it will be F1.

How to Find Unique Values in Google Sheets - Sheetaki

 

  1. Begin your function with an equal sign =, then followed by the name of the function, UNIQUE, then an open parenthesis (.

How to Find Unique Values in Google Sheets - Sheetaki

 

  1. Then we will insert the range we would like to remove the duplicated value from. In this example, it would be A1:D12. Don’t forget to close the formula by inserting a close parenthesis )!

How to Find Unique Values in Google Sheets - Sheetaki

 

  1. After the following steps, your input should look like this:

How to Find Unique Values in Google Sheets - Sheetaki

 

Similar to Example 1, the return values from using the UNIQUE function have removed rows 10, 11, and 12 as these values are repeated. 

So there you go! Two simple ways to find the unique values in your list of data. 

Don’t forget to check out our tutorial on the UNIQUE function to get a more in-depth understanding of how the function works! 🎇

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

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

You May Also Like