A big part of data analytics involves data cleaning. Well cleaned data is one that is free from incomplete, irrelevant, and redundant records.
To remove redundancy, you need to get rid of duplicates, so that you are left with a set of unique data records. This gives you less clutter to deal with and minimizes errors.
The good news is that Google sheets provide you with some easy ways that let you quickly find unique values in your data.
Table of Contents
How to Find Unique Values in Google Sheets
In this tutorial, I will show you two simple methods you can use to quickly find unique values from a single column data or multiple columns data
- Using the Google Sheets built-in ‘Remove Duplicates’ tool.
- Using the UNIQUE function.
Let us assume we have the following dataset from which we want to eliminate duplicate records:
Notice that rows 2 and 5, as well as rows 6 and 7 have repeating records.
We will look at how to use the two methods mentioned to remove these duplicate records from the dataset.
Finding Unique Values Using the ‘Remove Duplicates’ Tool
Google Sheets offers a menu option that is dedicated to just this task – removing duplicates to find unique values. Using this method, you can remove duplicates with just three clicks.
Here are the steps you need to follow:
- Select the range of data that you want to work on. In our example, we selected columns A and B by dragging the mouse pointed over their headers.
- From the Data menu, select the ‘Remove Duplicates’ option.
- This will open the ‘Remove duplicates’ dialog box.
- If your selection also contains the column headers, then check the box next to the ‘Data has header row’ option.
- Under ‘Columns to analyze’, you get the option to select if you want the tool to consider all columns selected, or a selected few columns. Right now, since we want to get rid of records where both first and last names are the same, we will leave it as ‘Select All’.
- Click on the Remove duplicates button.
- You will see a message box giving you details about how many duplicate rows got deleted and how many unique rows have been retained.
You should now find rows 5 and 7 removed. This is because, when the ‘Remove duplicates’ tool finds repeating values, it only retains the first occurrence of the value, deleting all others.
In case you wanted to remove all rows which contain duplicates of the First name, then you could simply uncheck the box next to Column B in the ‘Remove duplicates’ dialog box (step 5).
In that case, you would have three duplicate rows removed, since the row containing the name Paul Rodriguez also gets considered as a duplicate.
Your resultant dataset would then be as follows.
Finding Unique Values Using the UNIQUE Function
The ‘Remove duplicates’ tool works on the original dataset. So when it removes duplicates, it changes the original data. If, however, you want to retain the original data set, then using the UNIQUE function would be a better option.
Syntax for the UNIQUE Function
The syntax for the UNIQUE function is:
UNIQUE(range)
Here, range is the range of data that you want to extract unique entries from.
The range can include either a range of column names or a range of cell references. The function will show the result starting from the cell in which you entered the formula.
Using the Unique Function to Find Unique Values in Google Sheets
Let us work on the same dataset to understand how the UNIQUE function is applied.
To enable you to easily see the differences between the original and resultant data, we are going to display the result in the same sheet (in a neighboring cell range). However, you can even enter the UNIQUE formula in a new sheet and display the unique records there.
Here are the steps you need to follow if you want to use the UNIQUE function to remove duplicates from the above dataset:
- In cell D1, type the following formula:
=UNIQUE(A:B) or =UNIQUE(A1:B10).
- Press the return key.
You should now see the set of unique records occupy the range from cell D1 to E8.
Note that the UNIQUE function allows you to remove duplicates dynamically, so any changes you make to the original dataset get automatically updated to the output of the function.
Also, note that you cannot make any changes to the output. Attempting to do so will result in a #REF error.
In fact, the main difference between the two methods explained in this tutorial is that the ‘Remove duplicates’ tool works on and changes the original data range.
The UNIQUE function, on the other hand, displays the unique data into a new data range, thereby keeping the original data unchanged.
If you want to retain the results of the UNIQUE function (so that you can perform subsequent operations on it), then you need to convert the formula result to static values.
To do this, you need to select the cells containing the output and copy them. Then, use the keyboard shortcut CTRL+SHIFT+V (on a PC) or CMD+SHIFT+V (on a Mac), to paste the values of copied cells.
In this tutorial, we showed you two ways to find unique values in Google Sheets by removing duplicates. The first method uses the in-built ‘Remove duplicates’ functionality of Google Sheets. You can use this technique to remove duplicates and replace the original data with only unique records.
The second method uses a UNIQUE method. This method is ideal if you don’t want to make any changes to the original data. Both methods work great and get the job done quickly.
We hope this tutorial has been helpful to you.
Other Google Sheets tutorials you may also like:
