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:
- Remove Duplicates Tool
Table of Contents
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.
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.
- Select the range of data that you would like to remove duplicates. In our example, it would be A2:D12.
- After selecting the range of data, select ‘Data‘, then select ‘Remove Duplicates‘.
- 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.
- 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‘.
- Select ‘Remove duplicates‘.
- 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.
- 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.
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
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
The Anatomy of the UNIQUE Function
The way we write the
UNIQUE function is:
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.
arrayis 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
- Simply click on the cell that you want to write down your function. In this example, it will be F1.
- Begin your function with an equal sign
=, then followed by the name of the function,
UNIQUE, then an open parenthesis
- 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
- After the following steps, your input should look like this:
Similar to Example 1, the return values from using the
UNIQUE function have removed rows 10, 11, and 12 as these values are repeated.
You may make a copy of the spreadsheet using the link attached below and try it for yourself:
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! 🎇