Advertisement

How to Remove Duplicates in Google Sheets Automatically

get unique rows
Advertisement

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.

delete duplicates automatically

The built-in feature is located in the Data menu. Click it, then choose Remove duplicates at the bottom.

Advertisement

remove duplicates button

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.

remove duplicated rows

Then, the duplicate row has been found and removed. Click OK to close.

Advertisement

duplicate rows and columns have been removed

The result will be like this:

google sheets spreadsheet

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.

Advertisement

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.

unique function in google sheets

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.

get unique rows

Advertisement

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.

paste special values only in google sheets

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.

Advertisement

google sheets add-ons

Search “remove duplicates” or something like that. You can choose any add-on, but we’ll talk about Ablebits’s.

searching google sheets add-ons

Click Install.

remove duplicates in google sheets using add-on

Advertisement

Choose Continue to install the add-on.

get ready to install

A new browser window would open. Simply choose to login with the same Google account you’re using on the Google Sheets.

login to google sheets

Choose Allow.

Advertisement

remove duplicates

Now that the add-on has been installed, you can close the pop-up dialog box, and continue to your Google Sheets.

remove duplicates add-on

To use the add-on to remove duplicates in Google Sheets, simply select the range first. Then, choose Add-onsRemove duplicates. There, you can do anything else other than removing duplicates.

To remove, choose Find duplicate or unique rows.

Advertisement

find duplicates 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.

select sheet and range to find duplicate rows

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.

find duplicates

Advertisement

Select columns to analyze. You can skip empty cells, checkmark whether you have headers, or whether you want to match case. Click Next.

select columns to search in

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.

clear automatically duplicate rows and columns

Wait for a second. Then the duplicate rows have been deleted. Select Close.

duplicate rows have been found and deleted

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 ToolsScript editor in the menu.

google script editor

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:

See the Google Script Editor scripts here.

google script editor

Once you’ve put the function from the link above, save the script first, otherwise, the script won’t run.

saving google script editor

Once ready, click the play icon button to run the script.

running google script editor

It will ask your permission. Choose Review Permissions.

authorization required

Choose to the same Google account you’re currently using on the Google Sheets right now.

login to google account

If this pop-out, simply click Hide Advanced. Then choose Go to (your spreadsheet name) (unsafe).

this app isn't verified

Then, finally, choose Allow.

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.

RELATED: How to Convert Excel to Google Sheets

Advertisement

Leave a Comment

Your email address will not be published. Required fields are marked *