This post will explain five easy methods to remove Google Sheets duplicates from your spreadsheets.
Duplicates in Google Sheets can be a massive problem for any business, company, or organization. It can result in missed opportunities, lost revenue, or worse!
Imagine you are calling potential customers twice because their phone number shows up twice in your spreadsheet. That would be a disaster!
When you begin consolidating data for analysis in Google Sheets, you may find yourself spending lots of time removing duplicate values, especially if you have a large amount of data. Fortunately, Google Sheets has quick ways to remove duplicates in Google Sheets.
Before we start, let's quickly run through each method that we will cover in this tutorial. You can read through all of them or use the Table of Contents to quickly skip to the method that will help you the most.
Method 1: The Remove Duplicates tool is the easiest way to remove duplicates in Google Sheets.
Method 2: The UNIQUE function is useful for when you want to create a second table of data without the duplicate values while retaining the original data.
Method 3: The add-on tool is useful for its customization options when removing duplicates.
Method 4: A Pivot Table can be useful for identifying duplicates and giving you extra information in a separate tab without editing the original data.
Method 5: Using conditional formatting is helpful because you can highlight duplicates in the original data without creating new columns or using an add-on.
Google Sheets has a built-in Remove Duplicates tool in its data menu. You can use it to remove duplicate rows from either a portion of the spreadsheet or the whole spreadsheet, even when your data is spread over multiple columns. To use the this tool, follow these steps:
If the selected data has a header row, tick the checkbox before Data has header row. We also recommend checking the Select All box. By checking this box, Google Sheets searches for duplicate rows, not just values, which is very helpful if your data is in multiple columns.
This ensures that the tool removes duplicates that are the exact copy of other entire rows.
However, if you want to search for duplicate values in a single column, even if the other information in other columns does not match, check the specific column to analyze.
This window will tell you how many duplicates existed, as well as how many rows now remain after Google Sheets removed duplicate data.
Simple, right?
Sometimes, you want to find duplicate entries in your Google Sheets data set without fully removing duplicates. The UNIQUE function in Google Sheets allows you to filter out the duplicates and create a new spreadsheet with only unique values.
This Google Sheets function is particularly helpful if you want to remove duplicates from your data range but not delete duplicates permanently from the data set.
The format of the function is simple:
=UNIQUE(bounds_of_the_portion_of_the_sheet)
To use the function, follow these steps:
The function only requires you to find the bounds of the portion of the sheet. For our example, we created another sheet TX_2 that will only contain unique values from sheet TX.
To use the UNIQUE function in our example, we went to our other sheet (TX_2), selected a cell where we wanted the copy to begin (A1), and then typed the following:
=UNIQUE(TX!A1:D305)
The TX! before the bounds of the sheet referred to the sheet where the data is located, and A1:D305 represented the boundaries of our data set. The result is as follows:
We received the same results as using Remove Duplicates in Google Sheets, but we have lost the formatting.
Also, our original data remained in the first sheet (with the duplicate data), while the second sheet contained only unique values.
We like this option to remove duplicates in Google Sheets because this method deletes duplicates from your data set without deleting them from the whole spreadsheet. If you need the duplicates in Google Sheets, you can always find them in the original sheet.
One downside of this method is that you cannot delete entries on the new sheet, as they depend on the entries from the original sheet.
Additionally, when you delete entries on the old sheet, they will also be deleted on the new sheet. To solve this, you can convert the results into static values.
One Google Sheets add-on that you can use is the Remove Duplicates by Ablebits. It allows you more versatility in removing duplicate entries from your Google Sheets. Once you have installed it, do the following:
Ablebits is available in the Google Sheets Add-ons store, where you can search for and download this helpful tool to remove duplicates.
While we want to identify duplicates and remove them, the tool can also search for unique values or all duplicates with their first occurrence.
This screen also allows you to modify which cells are scanned.
Since we want to delete duplicates in Google Sheets, select Delete rows within selection.
This final step is the biggest advantage of using an add-on. If you don't want to remove duplicates in Google Sheets, you have other options, such as the option to highlight duplicates, marking as DUPLICATE in a new column, clearing the duplicate cells, etc.
Normally, these actions can only be accomplished with Google Sheets conditional formatting, but this add-on includes them.
However, we have also written another article about using conditional formatting to identify duplicates in Google Sheets and highlight duplicates in Google Sheets that may interest you. You can read it here.
We now have the same result as the Remove Duplicates option in Google Sheets:
This method is useful because you can find every duplicate in a pivot table, then return to your original data and decide how to proceed.
Check out our tutorial on the basics of the Google Sheets Pivot Table.
Step 1:
Highlight your data and create a pivot table. (Insert > Pivot Table.)
You can add it to a new sheet or specify a range to add it to your existing sheet. We chose to add it to the existing sheet so that we could use the results to edit our existing data.
In our example, we want to find all of the duplicate entries in our inventory list. Maybe multiple people made entries, so we want to consolidate all of the duplicate entries into one inventory row per item.
In this case, we need to find the duplicates, but we can't remove duplicates in Google Sheets because we need the data. Using pivot tables lets us find duplicates and then deal with them as we see fit.
Step 2:
A pivot table editor will open in the sidebar
In the ROWS section, click Add, then select the column you want to examine for duplicates.
For our example, we selected the "Items" column.
Step 3:
In the Values section, click Add, then select the same column that you selected in the ROWS section.
This will add another column to your pivot table where we will identify duplicates in Google Sheets.
In our example, we again selected the "Item" columns.
Step 4:
In the "Summarize by" dropdown. select the option for the COUNTA function.
Now you have created a pivot table that summarizes the number of each inventory item.
Step 5:
To sort large->small, return to the Rows section and sort by the COUNTA formula, and sort in descending order.
This will allow you to easily see the number of duplicate rows for each entry at the top of the pivot table. You can then choose to edit your spreadsheet and eventually remove duplicate rows in Google Sheets when you are finished.
Sometimes you need to find duplicates in Google Sheets, but you don't necessarily need or want to remove duplicates in Google Sheets from your spreadsheet.
In these cases, you can also use conditional formatting to highlight duplicates in your original data without having to remove duplicates in Google Sheets.
Using conditional formatting is helpful because you no longer have to download an add-on.
To do this, follow our tutorial to highlight duplicates in Google Sheets.
You might also like our article on how to find duplicates in Google Sheets and our guide on alternatives to Google Sheets.
To optimize your workflow, we recommend reading our tutorial on how to send an email based on a date in Google Sheets.
