How to Remove Duplicates in Google Sheets Automatically
Duplicates in Google Sheets makes it difficult to read or edit. Unclutter it by removing it. Here’s how to remove duplicates in Google Sheets.
Managing your data on a spreadsheet sometimes leaves duplicate values, rows, or records untouched and that makes your spreadsheet messy. Here’s how to remove duplicates in Google Sheets automatically.
The duplicates you leave on your Google Sheets spreadsheet making it hard for you and others to read the data. Thanks to Google Sheets, while not as obvious as Excel’s, you can remove duplicates automatically so there’s no need to manually look for them and delete duplicates one by one.
There are some methods you can do to delete duplicates in Google Sheets, from using its built-in feature, add-on, function, and script.
How to remove duplicates in Google Sheets with the built-in feature
The built-in feature to remove duplicates is easy to use, but you may find it hard to look for. This feature allows you to remove duplicate rows instantly without the need to use an add-on.
Simply select which columns and rows which might contain the duplicate values in Google Sheets.
The built-in feature is located in the Data menu. Click it, then choose Remove duplicates at the bottom.
A box would pop out. Simply check Data has header row if it contains a header row, and select which columns to analyze and search for the duplicate rows. Choose Select All to check all of them.
Once done, click Remove duplicates.
Then, the duplicate row has been found and removed. Click OK to close.
The result will be like this:
Using the built-in feature is as easy as that. You can do more than just clearing the duplicate rows with the next method.
Delete duplicates with UNIQUE function in Google Sheets
Google Sheets has UNIQUE function to filter out duplicate rows from a range of rows and columns you can specify. This won’t remove the duplicates in Google Sheets but keeping the unique ones that you can put in other areas of the spreadsheet or a new sheet.
The function works just like any other function, which depends on the existence of the ranges of rows and columns you’ve specified. If the ranges’ value is changed, the filtered results which only contain unique values will also change.
To do this, simply put the function in an empty area of the spreadsheet where you want to output the data. Here’s the function:
=UNIQUE(range)
You type until the open bracket, then select the range which contains the duplicates you want to remove, and put the closed bracket once done selecting the range.
Once done, press Enter to see the result. The output data will contain only the unique rows in Google Sheets, and the duplicates have been removed.
If you would like to move the output data elsewhere, you can copy it as usual. But, you have to paste it using this method: Paste special > Paste values only. Otherwise, you would only paste the formula.
Remove duplicates using Google Sheets’s add-on
There are add-ons you can use in Google Sheets to help you get done faster, including to remove duplicates. Not just removing, you can also mark the duplicate in your own ways, and do other things like copying or moving the duplicates elsewhere — depending on the add-on features you’re using.
In this post, I used the one made by Ablebits. To install the add-on, simply click the Add-ons menu on your Google Sheets, then choose Get add-ons.
Search “remove duplicates” or something like that. You can choose any add-on, but we’ll talk about Ablebits’s.
Click Install.
Choose Continue to install the add-on.
A new browser window would open. Simply choose to login with the same Google account you’re using on the Google Sheets.
Choose Allow.
Now that the add-on has been installed, you can close the pop-up dialog box, and continue to your Google Sheets.
To use the add-on to remove duplicates in Google Sheets, simply select the range first. Then, choose Add-ons > Remove duplicates. There, you can do anything else other than removing duplicates.
To remove, choose Find duplicate or unique rows.
Select the range. You can checkmark Create a backup copy of the sheet to back up the sheet in case something goes wrong. Click Next.
Here, you can specify what you would like to find. You can also find the unique only, or duplicate with the first occurrences. To simply remove, choose Duplicates. Click Next.
Select columns to analyze. You can skip empty cells, checkmark whether you have headers, or whether you want to match case. Click Next.
This is a great thing when using Google Sheets add-on. You can do more than just removing duplicates. You can fill with color, add a status column, copy or move, simply clear the value, or delete the rows.
To delete duplicate rows, choose Delete rows within selection and choose Finish.
Wait for a second. Then the duplicate rows have been deleted. Select Close.
Advanced ways to remove duplicates in Google Sheets: Google Script Editor
You can also use the script editor inside the Google Sheets to make things a little more advanced. This would use a script code that you can customize as well.
First, open the script editor by clicking Tools > Script editor in the menu.
The script editor is opened in the new tab, which contains an empty function. Remove the empty function, and put this function within the link below:
Once you’ve put the function from the link above, save the script first, otherwise, the script won’t run.
Once ready, click the play icon button to run the script.
It will ask your permission. Choose Review Permissions.
Choose to the same Google account you’re currently using on the Google Sheets right now.
If this pop-out, simply click Hide Advanced. Then choose Go to (your spreadsheet name) (unsafe).
Then, finally, choose Allow.
The script will do the work to remove duplicates in Google Sheets you’re working on in another tab. Check the Google Sheets tab to see whether the duplicates have been removed.
Duplicate values, rows, records, numbers, names, etc will make your spreadsheet look messy and difficult to read or even edit in Google Sheets. Remove those duplicates with one of the above methods, which some you can customize.
