Google Sheets lets you remove duplicates from your document with three methods ranging from a simple integrated function to a custom script. While it’s not as apparent as in Excel, Sheets offers a more diverse way to remove duplicates in your spreadsheet.

Update: You can now do this with the built-in “Remove Duplicates” tool in Google Sheets.

RELATED: How to Remove Duplicate Rows in Excel

Remove Duplicates Using the Unique Function

The first method we’re going to look at uses the built-in Google Sheets function that finds all unique entries, letting you get rid of everything else in the data set.

Fire up your browser and open up a spreadsheet to get started.

A sample table with duplicates

Next, click the empty cell where you want the data to output, type =UNIQUE, and then click on the suggested function that appears in the dialog window.

Select an empty cell and start typing =Unique, then click on the suggestion that appears

From here, you can either manually enter the range of cells or highlight them for the function to parse. When you’re done, hit Enter.

Highlight the rows/cells you want the function to search through, then hit Enter

Just like magic, Sheets picks out all the unique results and displays them in the selected cell.

The unique entries that were found will appear outside your table below the function's cell

If you copy and paste the information back into a Google Sheet, be sure to right-click where you want to paste and then choose Paste Special > Paste Values Only—otherwise, only the formula gets copied into the new cell.

If you copy and paste the information back into a Google Sheet, be sure to right-click, then choose Paste Special > Paste Values Only

Remove Duplicates Using an Add-On

For the next method, you’ll need to install an add-on to Google Sheets. If you’ve never used an add-on before, they’re similar to browser extensions that unlock extra additional features for you inside of Google apps, like Docs, Sheets, and Slides.

For this example, we’ll be using Remove Duplicates by AbleBits. It comes with a free trial for 30 days; premium memberships are $59.60 for a lifetime subscription or $33.60 annually.

RELATED: The Best Google Sheets Add-Ons

Installing the Add-On

To get an add-on, open a file in Google Sheets, click “Add-ons,” and then click “Get add-ons.”

Open Add-Ons menu, then click Get Add-Ons

Type “Duplicates” into the search bar and then click the “Free” button.

Type Duplicates into the search bar, then click Free on the add-on you want to install

Click on the Google account you want to use to install the add-on.

Choose an account to install the add-on

Upon installing add-ons, you need to grant them specific permissions. These are fundamental to the operation of the add-on. Make sure you fully understand the permissions and trust the developer before installing any add-on.

Click “Allow.”

Read and review the permissions for the add-on, then click Allow

Using the Add-on

You can use the Remove Duplicates add-on for a single column or across multiple rows. In this example, we’ll be looking for duplicates in rows, but it works mostly the same for a single column.

In your spreadsheet, highlight all the rows you want to search for duplicates. After that, click Add-ons > Remove Duplicates > Find duplicate or unique rows.

Highlight the rows you want to search, click Add-ons, point to Remove Duplicates, then click on Find duplicate or unique rows

After the add-on opens, check to make sure the range listed is correct and then click “Next.”

Check to make sure the range listed is correct, then click Next

Next, choose the type of data you want to find. Because we’re removing duplicates, select “Duplicates” and then click “Next.”

Select Duplicates, then click Next

Select the columns for the add-on to search. If you didn’t include the headers—or maybe your table doesn’t have any headers at all—making sure to untick the “My table has headers” option. Otherwise, the first row will be omitted. Click “Next.”

If your tables doesn't have headers, deselct My Table Has Headers, then click Next

Finally, choose what the add-on will do with its findings and then click “Finish.”

Choose what you want to happen with the results, then click Next

On the results page, the add-on tells us that four duplicate rows were found and removed.

The results page showing how many entries were found in your document

Voila! All duplicate rows disappear off of your sheet.

The updated table without any of the duplicated entries

Remove Duplicate Rows with Google Script Editor

The final method for removing duplicates in your sheet involves using Google App Script, a free-to-use cloud-based development platform for creating custom, light-weight web applications. Although it involves writing code, don’t let that scare you off. Google provides extensive documentation and even supplies you with the script for removing duplicates. Just copy the code, verify the add-on, and then run it inside your sheet.

RELATED: How to Supercharge Your Google Apps with the Script Editor

From the current Google Sheet, click “Tools” and then click “Script Editor.”

Click Tools, then click on Script Editor

Google Apps Script opens in a new tab with an empty script.

An empty function for your bound script

Delete the empty function inside the file and paste in the following code:

//Removes duplicate rows from the current sheet.

 function removeDuplicates() {
//Get current active Spreadsheet
 var sheet = SpreadsheetApp.getActiveSheet();
//Get all values from the spreadsheet's rows
 var data = sheet.getDataRange().getValues();
//Create an array for non-duplicates
 var newData = [];
//Iterate through a row's cells
 for (var i in data) {
   var row = data[i];
   var duplicate = false;
   for (var j in newData) {
    if (row.join() == newData[j].join()) {
     duplicate = true;
    }
  }
//If not a duplicate, put in newData array
 if (!duplicate) {
  newData.push(row);
 }
}
//Delete the old Sheet and insert the newData array
 sheet.clearContents();
 sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Save and rename your script. Hit the “Run” icon when you’re done.

Click the Run icon

You’ll have to review the permissions your script requires and grant it access your spreadsheet. Click “Review Permissions” to see what access this script wants.

Click Review Permissions to view the requested permissions

Accept the prompts and then click “Allow” to authorize the script.

Review the permissions, then click Allow

After it finishes running, go back to your Sheet and, just like the previous methods, all duplicate entries vanish from your file!

The duplicate data points have been removed!

Unfortunately, if your data is inside of a table—like the example shown above—this script will not resize the table to fit the number of entries in it, and you’ll have to fix that manually.


That’s all there is to it. Whether you want to use the integrated Unique function, a third-party add-on, or create a custom add-on with Apps Script, Google gives you multiple ways to manage duplicates in your spreadsheet.

Brady Gavin Brady Gavin
Brady Gavin has been immersed in technology for 15 years and has written over 150 detailed tutorials and explainers. He's covered everything from Windows 10 registry hacks to Chrome browser tips. Brady has a diploma in Computer Science from Camosun College in Victoria, BC.  
Read Full Bio »