How to Remove Duplicates in Google Sheets

Google Sheets lets you remove duplicates from your document with methods ranging from a simple integrated function to a custom script. While you can do the same in Excel, Sheets offers more diverse ways to remove duplicates in your spreadsheet.

RELATED: How to Remove Duplicate Rows in Excel

Remove Duplicates Using Data Cleanup

One of the quickest ways to remove duplicates in Google Sheets is using the Data Cleanup tool.

Select the data you want to check for duplicates. Go to the Data tab, move to Data Cleanup, and pick “Remove Duplicates” in the pop-out menu.

In the window that appears, you’ll see the columns you’ve selected with an option to mark whether or not your data has headers. Check or uncheck the boxes as needed for the columns you want to review and click “Remove Duplicates.”

You’ll see a message letting you know how many duplicates were found and removed. Click “OK” to continue and view your data.

Remove Duplicates Using the Unique Function

The next 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.

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.

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.

Just like magic, Sheets picks out all the unique results and displays them in the selected 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.

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 $19.99 annually.

Update, 6/30/22: Since writing these instructions, Google Sheets has renamed Add-Ons to “Extensions.” So if you choose to use this method, note that you’ll find the word “Extension” rather than “Add-On” in all menu options.

Installing the Add-On

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

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

Click on the Google account you want to use 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.”

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.

After the add-on opens, check to make sure the range listed is correct and 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 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.”

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

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

Voila! All duplicate rows disappear off of your sheet.

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, select Extensions > Apps Script.

Google Apps Script opens in a new tab with an empty 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.

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.

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

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

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 cleanup tool, 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.