Have you ever faced the frustration of dealing with unnecessary duplicates in your data? Data analysis often involves cleaning up these duplicates. Clean data is complete, relevant, and free of duplicates.
In this article, I will show you two simple and quick methods that Google Sheets offers to eliminate duplicates and obtain a set of unique values.
Method 1: Using the ‘Remove Duplicates’ tool
Google Sheets has a dedicated menu option for this specific task: removing duplicates to find unique values. With this method, you can remove duplicates in just three clicks.
Here are the steps:
- Select the range of data you want to work with. In our example, we selected columns A and B by dragging the mouse over their headers.
- In the Data menu, select the Remove duplicates option.
- This will open the Remove duplicates dialog box.
- If your selection also includes column headers, check the box next to the option ‘Data has headers.’
- Under ‘Columns to analyze,’ you have the option to choose whether you want the tool to analyze all selected columns or only a few. For example, if you want to get rid of records where first names and last names are identical, select ‘Select All.’
- Click the Remove duplicates button.
- A message box will appear, showing you how many duplicate rows have been deleted and how many unique rows have been retained.
You should now see rows 5 and 7 removed. When the ‘Remove Duplicates’ tool finds repeated values, it only keeps the first occurrence of the value, removing all others.
Method 2: Using the UNIQUE function
If you want to keep the original data set intact, it is best to use the UNIQUE function.
The syntax for the UNIQUE function is as follows:
UNIQUE(range)Here, the range refers to the range of data from which you want to extract unique entries.
The range can include a range of column names or a range of cell references. The function will display the result starting from the cell where you entered the formula.
Follow these steps to use the UNIQUE function to remove duplicates from the previous data set:
- In cell D1, enter the following formula:
=UNIQUE(A:B)or=UNIQUE(A1:B10). - Press the Enter key.
Now, you should see the unique data set occupying the range of cells from D1 to E8.
Note that the UNIQUE function allows you to dynamically remove duplicates, so any changes made to the original data set will automatically update in the function’s result.
Please note that you cannot make changes to the function’s result. Any attempt 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 the original data range and modifies it.
The UNIQUE function, on the other hand, displays the unique data in a new data range, thereby preserving the integrity of the original data.
If you want to keep the results of the UNIQUE function (to perform further operations on them), you need to convert the formula’s result into static values.
To do this, select the cells containing the output, copy them, and then use the keyboard shortcut CTRL+SHIFT+V (on PC) or CMD+SHIFT+V (on Mac) to paste the values of the copied cells.
In this tutorial, we have shown you two ways to find unique values in Google Sheets by removing duplicates. The first method uses the built-in ‘Remove Duplicates’ feature in Google Sheets. You can use this technique to remove duplicates and replace the original data with unique records.
The second method uses the UNIQUE function. This method is ideal if you don’t want to make any changes to the original data. Both methods work perfectly and allow you to quickly get the desired result.
I hope this tutorial has been helpful to you.
Discover more Google Sheets tutorials on Crawlan.com.
