In this tutorial, you will learn how to remove duplicate rows in Google Sheets.
How To Remove Duplicate Rows in Google Sheets
A common task that users often need to do is removing duplicate rows from a spreadsheet. This can be a pain to do manually, but luckily there is a built-in function in Google Sheets that can do it for you.
You may want to remove duplicate rows because you want to avoid counting the same entry twice.
For example, a dataset of contact numbers pulled from different sources may include duplicates of the same number. We can use the Remove Duplicates option to ensure that every row in our dataset is unique.
Besides the Remove Duplicates option, we will also show you how to programmatically remove duplicates rows in a range using Google Sheets functions. This option is best for users who want to keep the original data intact.
How To Delete Duplicate Rows in Google Sheets
Here’s how to delete duplicate rows in Google Sheets.
First, select the range you want to remove duplicates from. Next, click on Data > Data cleanup > Remove duplicates to delete all rows in that range with duplicate values.
In the pop-up window, select all columns you want to check for duplicates. Click on Remove duplicates to continue.
Google Sheets will indicate how many duplicate rows were found and removed. Click on OK to proceed.
Your range should no longer have any duplicate rows. Do note that differences in capitalization do not count as a duplicate value and will not be removed.
Now that we know how to handle a single column, we will now try to remove duplicate rows with two or more columns.
Select the range of cells you would like to filter.
In the Columns to analyze section, check all columns that you would like to check for duplicates. Click on Remove duplicates to proceed.
Your dataset should now have all duplicate rows removed.
You may want to retain the original dataset and instead create a copy of the dataset with the duplicate rows removed. We can do this through the use of two Google Sheets functions: UNIQUE and QUERY.
We’ll start by adding the UNIQUE function on a separate cell outside of our dataset.
Next, we’ll add a QUERY function as an argument of our UNIQUE function. We’ll use the query “SELECT A,B” to retrieve both columns from the specified range.
The formula should remove all duplicate rows from the original range.
This guide should be everything you need to delete duplicate rows in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.