September 15, 2020

How to Remove Duplicate Entries in Google Sheets

Google Sheet spreadsheet
SECTIONS
  1. Built-In Remove Duplicates Tool
  2. UNIQUE Function
  3. Use Add-Ons

When you begin consolidating data for analysis, you may find yourself spending more time removing duplicates, especially if you have a large amount of data. Fortunately, Google Sheets has quick ways to remove duplicate entries in your spreadsheets.

Remove Duplicates Using Built-In Remove Duplicates Tool

Google Sheets has a built-in Remove Duplicates tool. You can use it to remove duplicates from either a portion of the spreadsheet or the whole spreadsheet. Select the portion of the spreadsheet that you want to be scanned by the built-in function, and then do the following:

Step 1: Click Data on the menu, and then scroll down the drop-down box to select Remove duplicates.

Remove duplicates option under Data highlighted

Step 2: A small window will appear. If the selected data has a header row, tick the checkbox before Data has header row. Make sure the checkbox before Select all is also ticked. This ensures that the removed rows are the exact copy of other rows. Click Remove duplicates.

Remove duplicates window. Data has header row selected. All columns to analyze selected.


Step 3: Another window will appear, showing the number of rows that are duplicates of other rows. 

50 duplicate rows found and removed
Simple, right?


Remove Duplicates Using UNIQUE Function

But sometimes you want to keep the original entries and filter out the duplicates, copying the unique entries to another sheet.  You can use the UNIQUE function. The format of the function is simple:

=UNIQUE(<bounds of="" the="" portion="" sheet="">)</bounds>

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.

Two sheets included


To use the UNIQUE function, we go to the other sheet, select a cell where the copy begins, and then type the following:

=UNIQUE(TX!A1:D305)

The TX! before the bounds of the sheet refer to the sheet where the data is located. The result is as follows:

The new sheet includes the filtered rows.
We got the same results, but we have lost the formatting. 

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, it will also be deleted on the new sheet. To solve this, you can convert the results into static values.

Remove Duplicates Using Add-Ons

One add-on you can use is the Remove Duplicates by Ablebits. It allows you more versatility in removing duplicate entries in your sheets. Once you have installed it, do the following:

Step 1: Click Add-ons on the menu, and then click Remove Duplicates, and then click Find duplicate or unique rows.

Remove duplicates add-on options. Find duplicate or unique rows highlighted


Step 2: A window will pop-up. Select the portion of the sheet you want to be filtered here. Then click Next.

Select the range with your table


Step 3: The add-on allows you to either look for duplicates or for unique occurrences. Select Duplicates then click Next.

FInd duplicates selected

Step 4: Make sure Skip empty cells and My table has headers are selected. You can also change which columns are scanned. When you are done, click Next.

All columns selected


Step 5: You can now select the action to do once duplicates were found. Since we want the duplicates removed, select Delete rows within selection. Click Finish. 

Delete rows within selection selected
The advantage of this add-on is in this step. If you don’t want the duplicates removed, you could have other options, such as highlighting the rows, marking as DUPLICATE in a new column, clearing the values, etc. 


Step 6: The list of removed rows are displayed after the process. Click Close.

50 duplicates have been found and deleted.


We now have the same result:

 Filtered list after the add-on was used.


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.