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