Categories
Device

How to remove duplicates in google sheets

Editor’s Note: Romain Vialard is a Google Apps Script Top Contributor. He has contributed interesting articles and blog posts about Apps Script. – Jan Kleinert

December 2011, updated March 2018

This tutorial shows how to avoid duplicates when you want to automate the process of copying data in Google Workspace and specifically how to remove duplicate rows in spreadsheet data.

Google Apps Script lets you copy email attachments from Gmail to a collection in Google Docs, sync spreadsheet data with a list page in Google Sites or with Google Calendar or with your contact list. The example shown below can be used to avoid duplicates in each of those cases.

Time to Complete

Approximately 5 minutes.

Prerequisites

Before beginning this tutorial, you should feel comfortable using the Script Editor and have experience using the most basic Spreadsheet functions.

Running a simple example

    Open a new spreadsheet in Google Docs or use an existing spreadsheet containing duplicates.

If the spreadsheet is empty, add a few rows of data (for example, a list of contacts, parts inventory, etc.) and duplicate some of them.

How to remove duplicates in google sheets

Choose the menu Tools > Script Editor.

Copy and paste the following script:

Save the script.

Select the function removeDuplicates in the function dropdown list and click Run.

Take a look at your spreadsheet. There should be no more duplicates.

How to remove duplicates in google sheets

How the script works

First, we do a single call to the spreadsheet to retrieve all the data. We could have read our sheet row by row, but JavaScript operations are considerably faster than talking to other services like Spreadsheet. The fewer calls you make, the faster it goes. This is important because each script execution has a maximum run time of 6 minutes.

Our variable data is a JavaScript 2-dimensional array that contains all the values in our sheet. newData is an empty array where we put all rows which are not duplicates.

The first for loop iterates over each row in the data 2-dimensional array. For each row, the second loop tests if another row with matching data already exists in the newData array. If it is not a duplicate, the row is pushed into the newData array.

To finish, the script deletes the existing content of the sheet and inserts the content of the newData array.

Variation

In the example above, the script finds a duplicate when there are two identical rows, but you may also want to remove rows with matching data in just one or two of the columns. To do that, you can change the conditional statement.

This conditional statement finds duplicates each time two rows have the same data in the first and the second column of the sheet.

Reuse the method

Each time you need to copy data from one point to another you may want to check if this data has already been copied. For example, if you want to sync a label in Gmail to a collection in Google Docs to automatically retrieve important attachments, the solution shown above can be used.

  1. First, retrieve the data you want to copy (from Gmail, Calendar, a spreadsheet, etc.)
  2. Then, retrieve the data already stored in the targeted folder, spreadsheet, site, etc.
  3. For each item you want to copy:
    • Make the assumption that you want to copy this item by the use of a boolean, for example: var toCopy = true; .
    • For each item stored in the targeted folder, check if it looks similar to the item you want to copy.
    • If it looks like the item has already been copied, then you don’t want to copy it again, so set toCopy = false; .
    • Once you have read all items stored in the targeted folder, check if toCopy is equal to true or not. If it’s true, then copy the item.

There are other solutions to avoid duplicates. For example, you can tag each item as ‘already processed’ once they have been copied. An example can be found in this tutorial: Sending emails from a Spreadsheet. Or, you can remove the item from the list of items to copy.

Summary

Congratulations, you’ve completed this tutorial. You should now be able to avoid duplicates when manipulating data with Apps Script.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Updated on January 3, 2021 by Swayam Prakash

Data redundancy is a common problem in database applications like Excel and Google Sheets. During manual data entry in the worksheet, some info may get recorded more than once. This leads to data duplication or redundancy. In this guide, I will show you how to remove duplicate data from Google Sheets.

I have mentioned a few tricks to highlight and get rid of similar information from the Google Sheet. There is a function that is native to Sheets. People commonly use this to find and remove repetitive information. Also, there is a dedicated in-built add-on that helps to remove duplicate data in Sheets. Every option I have mentioned is free to use.

How to remove duplicates in google sheets

Table of Contents

Highlight and Remove Duplicates from Google Sheets

You can use any of the methods I have mentioned below.

Remove Duplicates Tool

This is very easy. Suppose I have a list of bikes and their engine displacement. If there is one bike and its displacement details repeated twice, then this tool will remove one of it

  • Select the dataset in the Google Sheet. You may notice the data collection has two values from each data set repeated.
  • Click on the option Data in the menu bar > scroll down to Remove Duplicates
    How to remove duplicates in google sheets
  • Then click the checkbox Data Has Header Row
  • Also, click on the checkbox Select All
    How to remove duplicates in google sheets
  • Finally, click on Remove Duplicates. You will see the below message of the removal of duplicate data.
    How to remove duplicates in google sheets

If you ask me, this above method is the easiest method to get the job done in removing duplicate entries in the Google Sheet. However, there is a catch. If the duplicate entry has a spelling error or you have up put any space, then the two duplicate values will not match. Hence, the Remove Duplicates tool will not work.

Remove Duplicates Using the UNIQUE Function

Google Sheets has another easy function that can help weed out the duplicate entries in the worksheet.

  • Simply put the cursor on any blank cell
  • Type this formula =UNIQUE(A2:B10)
    How to remove duplicates in google sheets
  • Press Enter

A2 and B10 are just examples. If you notice in the screenshot above, these cell numbers are the initial cell of one dataset and the final cell of the second dataset. You can accordingly mention the cell number in the formula as per your Google Sheet database.

Then after pressing enter the redundant value will be omitted and a new list will show up. This list won’t have any duplicate entries. See, how simple was that.?

Using Add-Ons to Remove Duplicates in Google Sheets

In Google sheets, you can also use add-ons to remove duplicate entries from the worksheet. First, you have to install the Add-on. It is free to download.

  • Open Google Sheets
  • In the menu bar, click on Add-ons
  • Then select Get Add-ons
    How to remove duplicates in google sheets
  • Then in the search-box type Remove Duplicates
  • In the results, click on the Remove Duplicates from AbleBits
    How to remove duplicates in google sheets
  • Then click Install and use the same Gmail account that you use to work on Google Sheets to give the add-on permissions.
    How to remove duplicates in google sheets

Now, let’s see how to use the add-ons. It’s pretty straight-forward.

  • Select the dataset in the sheet
  • Then in the menu bar click on Add-On
  • Under that expand to Remove duplicates > click Find Duplicate or Unique Rows
    How to remove duplicates in google sheets
  • Now you have to go through 4-steps.
  • In the 1st, click on the checkbox that says Create A Backup of the sheet and check that the range of the dataset has been mentioned correctly.
    How to remove duplicates in google sheets
  • Then click Next
  • In the 2nd step, click on the radio button Duplicates and click Next
    How to remove duplicates in google sheets
  • Next, in the 3rd step select the checkboxes Skip Empty Cells, My table has headers and Match case
    How to remove duplicates in google sheets
  • In the last and 4th step select the radio button Delete Rows Within Selection.
  • Then click on Finish
    How to remove duplicates in google sheets
  • You will see a message that the duplicate row in the dataset has been removed. That’s it.
    How to remove duplicates in google sheets

So, that’s how you remove duplicates from Google Sheets using various functions and add-ons. All the methods are quite easy to carry out. I hope that this guide was informative.

How to remove duplicates in google sheets

If you want to remove duplicates in Google Sheets, you need to use the UNIQUE function. This function extracts all the unique values and puts it in another place on the sheet.

This is very different than the way you can remove duplicates in MS Excel. In Excel, there is an option in the data tab to remove duplicates.

This Article Covers:

Remove Duplicates in Google Sheets

Suppose you have a dataset as shown below:

How to remove duplicates in google sheets

In this data, there are repetitions in names (Mark and Brenda).

How to remove duplicates in google sheets

Now let’s see how you can use the UNIQUE function to remove duplicates in Google Sheets.

Example 1: Remove Duplicates from a Single Column

Let’s say you only want to remove the duplicates from the Name column.

Here are the steps to do this:

  • Select a cell where you want to get the list of unique items/names.
  • Enter the following formula: = UNIQUE ( A2:A10 )
  • Hit Enter.

This would instantly remove the duplicates and give you a list of unique names.

How to remove duplicates in google sheets

Note: If you want to remove the list, delete the first cell, or select the entire range and hit delete. Google Sheets will not let you delete individual cells (other than the first one – which would delete the entire list).

Since this is a formula, it would automatically update if you make any changes in the original list.

UNIQUE function returns a #REF! error if there is already some data in the cells that are supposed to be filled by the unique function.

If you hover the mouse over the cell with error, it will show a message stating the issue.

How to remove duplicates in google sheets

Handling Leading and Trailing Spaces

Another issue you may face is when there are leading or trailing spaces. For example, if the example below, there is a trailing space in Brenda (in A10). When the UNIQUE function is used on this data set, it considers both the names as unique and returns both the names in the result.

How to remove duplicates in google sheets

Here is how to handle this:

  • Go to the cell where you want to get the list of unique items/names.
  • Enter the following function: = UNIQUE ( TRIM ( A2:A10 ) )
  • While in the eidt mode, press Ctrl + Shift + Enter. It would change the formula to = ArrayFormula ( UNIQUE ( TRIM ( A2:A10 ) ) ). How to remove duplicates in google sheets
  • Press Enter.

This would automatically account for any leading, trailing, or double spaces and give you the final result after removing these.

Example 2: Remove Duplicates from Multiple Columns

If you have more than one column, you can use the above method to remove duplicate rows and get unique data.

Suppose you have the data as shown below:

How to remove duplicates in google sheets

In this data, there are two duplicate rows (highlighted in orange and green).

Here are the steps to remove these duplicate rows:

  • Select a cell where you want to get the list of unique items/names.
  • Enter the following formula: = UNIQUE ( A2:B10 )
  • Hit Enter.

How to remove duplicates in google sheets

If there are leading, trailing or double spaces, use the following formula:

I hope this helps you cleaning your data in Google Sheets. Do let me know your thoughts by leaving a comment.

You May Also Like the Following Google Sheets Tutorials:

1 thought on “How to Remove Duplicates in Google Sheets”

Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. This tutorial is handy to remove all the duplicates at once and be more productive. I love excel tricks. Thanks!

Khamosh Pathak

17 Sep 2014

How to remove duplicates in google sheets

Google Spreadsheet (now called Sheets as part of the Google Drive productivity suite) is turning out to be a real MS Excel competitor. The amount of stuff Excel can do is truly mind-boggling. But Sheets is catching up. More importantly it is beating Excel by integrating add-ons and scripts in an easy to use manner. Plus there’s a long list of functions for quick calculations.

Using Google Sheets to do serious work means dealing with a serious amount of data. Data that’s often not sorted or organized. In such times you want an easy way to get rid of duplicate data entries. And let’s face it, searching for and deleting recurring data sets manually is inexact and time consuming.

Thanks to Sheet’s support for add-ons and functions, this process can be taken care of in mere seconds.

Remove Duplicates Add-on

We talked some cool Google Docs add-ons for writers last time. The Remove Duplicates add-on can be installed directly from the spreadsheet view. From the Add-ons menu select Get Add-ons. Search for Remove Duplicates and install it. In a couple of seconds, the add-on will be ready to work.

Once installed, select the cells in question and go to Add-ons -> Remove Duplicates. This will bring on a multi-step popup menu where your selection will already be accounted for.

How to remove duplicates in google sheets

In the next step you can choose if you want to find duplicates or uniques.

How to remove duplicates in google sheets

Now you can choose to highlight the duplicates in a color you like. From here you can also ask the add-on to directly delete such occurrences.

How to remove duplicates in google sheets

The add-on will take a couple of seconds and then present you with its findings.

While the add-on works great when you’re just using just one row, it gets wonky when you try using multiple rows.

Using The Unique Function

With this method, leave a couple of blank rows from where your data is and enter the function “=UNIQUE()”. After you’ve opened the parenthesis, select the cells you want to filter. You can also select rows. Close the parenthesis and press Enter.

How to remove duplicates in google sheets

You’ve just created a copy of the data you had, only no repeating values were included. Now delete the preceding rows/cells and you’re done.

How to remove duplicates in google sheets

Any other methods you know of removing duplicates quickly from a Google Sheet?

Last updated on 8 Feb, 2018
The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.

Read Next

How to remove duplicates in google sheets

How to Restrict Editing Cells in a Google Docs Spreadsheet

Learn How to Restrict Editing Cells in a # Google Docs Spreadsheet.

How to remove duplicates in google sheets

How to Easily Create Graphs and Charts on Google Sheets

Are you terrified by the mere idea of creating graphs & charts on # Google Sheets? Explore this comprehensive guide to create charts on Google Sheets like a pro.

How to remove duplicates in google sheets

Top 11 Google Slides Animation Tips and Tricks to Use It Like a Pro

Excited about adding animations and transitions to # Google Slides? Check out 11 tips and tricks to add and edit animations in # Google Slides.

How to remove duplicates in google sheets

7 Best Fixes for Spotify Keeps Pausing on Windows and Mobile

A list of all the ways to fix # Spotify pausing on # Windows and # mobile devices. This is a device-specific issue that can be easily fixed using one of the solutions discussed.

How to remove duplicates in google sheets

3 Best Ways to Share Google Forms With Others

Do you want to send # Google Forms to others to fill or edit it? Check out three smart ways to share # Google Forms with others.

How to remove duplicates in google sheets

How to Listen to Spotify Offline on Mobile and Windows 10

Can’t find the # offline option in # Spotify app? Click here to learn how to listen to Spotify offline on all your desktop and # mobile devices.

How to remove duplicates in google sheets

3 Ways How Google Photos Will Count Against Google Drive Storage

Wondering how to manage # Google # Photos # storage with your # Google Drive? Here are the 3 ways that will illustrate how your # Google Photos will count against the Google Drive # Storage

How to remove duplicates in google sheets

Top 9 Fixes for YouTube Restricted Mode Won’t Turn off on Mobile and PC

Unable to turn off Restricted mode on # YouTube? Check out 9 fixes to solve the issue of cannot turn off YouTube Restricted mode.

Did You Know

Since its launch, Shazam has been used more than 30 billion times to identify songs.

While working in Google Sheets, you may run over huge bookkeeping pages where you need to manage many copy passages. We comprehend the torment of managing copies and how troublesome it would get if you somehow happened to feature and eliminate sections individually. Be that as it may, with the assistance of contingent designing, featuring and eliminating copies turns out to be pretty simple. Follow this guide as we disclose to you how to discover and eliminate copy passages in Google Sheets.

Google Sheets: How to feature copies in a solitary section

Prior to realizing how to eliminate copy sections from Google Sheets, how about we figure out how to feature copies in a solitary segment. Follow these means.

Open your accounting page in Google Sheets and select a segment.

For example, select section A > Format > Conditional designing.

Under Format rules, open the drop-down rundown and select Custom recipe is.

Enter the Value for the custom recipe, =countif(A1:A,A1)>1.

Underneath Format rules, you can discover Formatting styles, which lets you set an alternate tone for featured copies. To do that, hit the Fill shading symbol and select your favored shade.

Whenever you’re done, hit Done to feature the copies in a solitary section.

Essentially, in the event that you need to do it for segment C, the equation becomes, =countif(C1:C,C1)>1 and you do it so on for different sections also.

Moreover, there’s a method to discover copies in the sections as well. To learn, follow these means.

We should accept you wish to feature copies between cells C5 to C14.

All things considered, go to Format and choose Conditional Formatting.

Under Apply to go, enter the information range, C5:C14.

Next, under Format rules, open the drop-down rundown and select Custom recipe is.

Enter the Value for the custom recipe, =countif(C5:C,C5)>1.

Set an alternate tone for the featured copies by following the prior advances on the off chance that you wish to. Whenever you’re done, hit Done.

How to remove duplicates in google sheets

While Google Sheets doesn’t have quite the same depth of features as Microsoft Excel, it still has a number of useful tricks under the hood. One of its most useful features is conditional formatting.

This allows you to change the formatting of any cells that obey a specific set of conditions that you create. You could, for instance, highlight all of the cells that are above a certain value, or contain a specific word. Conditional formatting has a number of uses, such as highlighting errors or comparing data.

Another useful application of conditional formatting is highlighting duplicates. This allows you to quickly spot any duplicate text or values within a large spreadsheet. Here’s how it’s done.

Highlighting Duplicates From a Single Column

If you want to highlight duplicates from a single column in Google Sheets, you’ll need to first select the column where you want to find duplicates.

How to remove duplicates in google sheets

With your column selected, click Format > Conditional Formatting from the menu bar.

How to remove duplicates in google sheets

If you already have conditional formatting active in that column, click Add another rule. Otherwise, you can edit the rule that is currently showing.

How to remove duplicates in google sheets

Press the Format Cells If box and choose Custom formula is from the dropdown options.

How to remove duplicates in google sheets

Type =countif(A:A,A1)>1 into the Value or formula box or replace all instances of the letter A in the formula with your chosen column.

For example, to apply the formatting to column M, your formula would be =countif(M:M,M1)>1

How to remove duplicates in google sheets

You can set the formatting style yourself, by selecting the color and font options. You can also use one of the various preset formatting styles by clicking on the Default text under the Formatting style options, then selecting one of the presets.

How to remove duplicates in google sheets

Click Done to add your conditional formatting rule. Any duplicate cells should now appear with your chosen highlighting.

Highlighting Duplicates From Multiple Columns

It’s possible to apply this formatting to more than one column, highlighting any duplicates that appear across your selected cell range.

To begin, select the columns that you want to include, hold down the Ctrl key, then click on the letter at the top of each column to add individual columns.

You can also hold down the Shift key and click on the first and last cells in your range to select multiple columns at once.

How to remove duplicates in google sheets

With your cells selected, press Format > Conditional Formatting.

How to remove duplicates in google sheets

Edit the current rule or press Add another rule to add a new rule.

How to remove duplicates in google sheets

Press the Format Cells If… option and choose Custom formula is.

How to remove duplicates in google sheets

Type =COUNTIFS($A$1:Z,A1)>1 into the Value or formula box. Replace each A with the letter of the first column in your selection and Z with the last column in your selection.

For example, if your data was in columns M to Q, then =COUNTIFS($M$1:Q,M1)>1 would be the correct formula.

How to remove duplicates in google sheets

You can select your own formatting style in the Formatting style section or click on the Default text to apply one of several preset formatting options.

How to remove duplicates in google sheets

Click Done to confirm and save the rule—your duplicates will now be highlighted across all the columns that you selected.

Any columns that you missed out will be not be highlighted, but will still count towards the number of duplicates.

How to remove duplicates in google sheets

Using Powerful Google Sheets Features

Conditional formatting is a useful feature that allows you to easily spot duplicated information across a large spreadsheet, but there are plenty of other features that can be incredibly useful for Google Sheets beginners and regular users.

You may wish, for instance, to add in-cell validation to your spreadsheets to improve the quality of your data. If you’re looking to create automated tasks, you may want to look at using Google Sheets to send emails based on cell values.

Google Sheets is used by numerous people all around the globe as a reliable tool for data entry. No matter personal or official, one can create, edit and collaborate with others on spreadsheets from supported gadgets with the Google Sheets. You might have used the Google Sheets to get your work done. However, there might be a case when duplicates in Google Sheets entries ruin your whole venture. In case if you’re looking for nifty workarounds to fix the nuisance with the duplicates, let’s see how can it be avoided.

How to remove duplicates in google sheets

Thankfully, Google Sheets features some indirect mechanisms to eliminate the duplicates. Here, we’d be going through some of them.

Guide to Remove duplicates in Google Sheets

Using the Addon – Duplicate Remover

Google Sheets support a plethora of Add-Ons which can be installed based on your need and purpose. For removing Duplicates, Google Sheets has an add-on called Duplicate Remover. Using the add-on we can remove duplicates with few 3-4 steps.

  1. On Google Sheets, go to Tools>>Get the add-on
  2. Enter duplicate on the search bar to see all the available tools with duplicate as a keyword
  3. From the available options, choose Duplicate Remover from Adlebit

Once installed, Duplicate Remover will be featured on the Google Sheets. So, in order to remove duplicates, Go again to Tools and choose the add-on, Remove Duplicates.

How to remove duplicates in google sheets

How to remove duplicates in google sheets

How to remove duplicates in google sheets

How to remove duplicates in google sheets

How to remove duplicates in google sheets

The add-on will take you to 3-4 series of steps to get your preference. Moreover, options such as whether you want to highlight duplicates, delete them, create a new copy of your data and such are available.

2. Conditional Formatting

In order to remove duplicates using conditional formatting, we need to go through two steps. The numero uno is to highlight the duplicates, and then to remove the highlighted contents.

  1. Highlight the duplicates
  2. Remove the highlights

First of all, we can highlight the duplicates, for that select your dataset and open the conditional formatting sidebar (under the Format menu).

How to remove duplicates in google sheets

    Under the “Format cells if…” option, choose custom formula is (the last option) and enter the following formula.

The duplicates in column A will be highlighted on your data set.

  • In case you want to highlight the whole row, then change the above code a little by adding $ sign before the last A1.
  • 3. Using Pivot Tables

    Pivot Tables are useful especially for exploratory data analysis. Pivot Tables can be used to search for duplicates in Google Sheets. They’re flexible and fast to use, also helps to phase out any duplicates in your data.

    1. Highlight your dataset and create a Pivot Table (under the Data menu).
    2. A new tab opens with the Pivot Table editor.
    3. Under ROWS, choose the column you want to check for duplicates (e.g. invoice number). Then in VALUES, choose another column (I often use the same one) and make sure it’s set to summarize by COUNT or COUNTA (if your column contains text), like this:How to remove duplicates in google sheets

    You can see that duplicates values (for example 196-X) will have a count greater than 1. From here you can look up these duplicate values in your original dataset and decide how to proceed.

    Apart from the above mentioned three methods to remove duplicates, there do exist some other method such as by App Script. However, these are the simplest method which you can use to phase out the duplicates.

    How to remove duplicates in google sheets

    Removing duplicates in Google Sheets is the act of removing data that appears more than once or removing duplicate entries in your sheet.

    This should be done before carrying out any data analysis to avoid errors and prevent unnecessary corrections.

    Today in this post, I will show you how to remove duplicates in Google Sheets using any of the available methods.

    Google sheet or spreadsheet is now serving the same purpose as MS Excel, which is being powered by the Google Drive Suite.

    However, the Google sheet is becoming more common and advanced with the help of easy to use scripts and add-ons.

    As well, providing the full list of various functions to help you in quick computing and calculations.

    Making use of the Google sheets to sort and organize a vast amount of data can be tiring and stressful, especially getting rid of duplicate data entries.

    Hence, if you know how to find duplicate data entries in the Google sheet, then removing them will be a great move.

    But, if you don’t know how to achieve this, then, this guide will be of help to you anytime any day.

    With the help of Google drive by integrating add-ons and functions, getting rid of duplicates in Google sheets can be done in a few minutes.

    Let’s quickly see the available methods on how to remove duplicates in Google Sheets.

    Table of Contents

    How to Remove Duplicates in Google Sheets

    There are so many methods on how to remove duplicates in Google sheets, which includes;

    How to remove duplicates with an Add-On.

    How to remove duplicates with Apps Script.

    How to remove duplicates with Pivot Tables.

    How to remove duplicates in Google sheets with the UNIQUE Function.

    How to remove duplicates in Google sheets with Conditional Formatting.

    How to remove duplicates in Google sheets with the Remove Duplicates tool.

    Nevertheless, this guide will teach you how to remove duplicate cells in Google sheets using the UNIQUE Function.

    This is the best and easiest method so far, and it works perfectly well for me.

    So, let’s quickly see the steps to follow below here.

    How to use the UNIQUE Function to Remove Duplicate Cells

    Step 1: First, visit the site here on https://sheets.google.com in your internet browser.

    If you are not signed in yet, then sign in to your Google account and a list of Google spreadsheets files will be displayed.

    Step 2: Select the particular file you want to edit that has rows/columns containing duplicates data entries in the cells.

    How to remove duplicates in google sheets

    Step 3: Click on an empty cell away from the row/columns where your data is located at.

    Then, you will now enter the Function in that cell.

    Step 4: On the empty cell, type in “=UNIQUE(range),” and replace “range” with the cell range where the duplicates occur.

    Example, as seen in the image below, to find the duplicates in column A (Boy Names) from row 2 to 8, we type =UNIQUE(A2:A8).

    How to remove duplicates in google sheets

    Step 5: After typing in the Function, quickly press the Enter key on your computer keyboard.

    It will then create a new cell range containing the data entries but without the duplicates.

    How to remove duplicates in google sheets

    You can now copy and replace the original cell range with the new data cells having no duplicates.

    If you no longer want the data in the original cells, highlight, and copy, then paste in the first column of the original cells.

    You can achieve that using CTRL+C for copy and CTRL+V to paste.

    That is all on how to remove duplicates in Google sheets and how to merge duplicates in Google sheets.

    With the help of this UNIQUE Function , you can quickly sort and remove duplicates in Google sheets easily.

    Therefore, take advantage of this today, and you will be glad you did.

    Remember to share this article with your friends and on social media platforms.

    How to quickly highlight cells with duplicate values in Google Sheets using conditional formatting. The duplicate cells can be easily removed from the spreadsheet with Google Script.

    The Email Extractor app pulls emails addresses of your contacts from Gmail into a Google Sheet. You can then use Mail Merge or Document Studio to send personalized emails to your contacts from within the sheet itself.

    That said, the imported mailing list may sometimes have duplicate email addresses and it is thus be a good idea to clean up the data in your Google Sheet by removing duplicates before sending out the emails.

    Highlight Duplicates in Google Sheets

    You can use Conditional Formatting in Google Sheets combined with the COUNTIF formula to quickly highlight cells that contain duplicate values.

    How to remove duplicates in google sheets

    Here’s how you can find duplicates in Google Sheets:

    Open the Google Sheet containing your data and go to the Format menu.

    Select Conditional Formatting from the expanded menu. This will help us change the colors of cells, rows or columns that meet a specific criteria.

    In the Apply to Range input box, add the range of cells that contain possible duplicates. In our case, the email addresses are in column A so we can put A:A to specify the entire A column.

    Under the ‘Format cells if’ section, choose “Custom formula is” from the dropdown list as set the formula as =COUNTIF(A:A, A1) > 1

    Click the Done button and you’ll instantly notice that all duplicate cells are highlighted as shows in the screenshot below.

    How to remove duplicates in google sheets

    The COUNTIF Function

    The COUNTIF function in Google sheets (and Microsoft Excel) essentially counts the number of cells in the range that meet a specific criteria. For instance =COUNTIF(A:A, “apple”) will count the number of cells that contain the word apple.

    It can accept wildcard characters too so =COUNTIF(A:A, “apple?”) will count cells that contain the word apple or apples. Or say =COUNTIF(A:A, “*@gmail.com”) and it will highlight all email address that end with a gmail address.

    Please note that the COUNTIF function is case-insensitive so values like gmail.com and Gmail.com are seen as duplicates.

    Highlight Entire Row Containing Duplicates

    If you’ve noticed in the previous screenshot, only specific cells that have duplicate values are highlighted through conditional formatting.

    However, if you would like the Google Sheet to highlight the entire spreadsheet row that contains duplicate values, we need to slightly tweak the formatting rule.

    Go to the Apply to Range input box and specify the entire spreadsheet range, not just the column that contains duplicates.

    In the custom formula,use absolute reference for the range and also change criterion to use $A1 instead of A1 . When we use $A1 , we are telling Google Sheet to only change the row but lock the column.

    The new duplicate detection formula reads =COUNTIF($A$1:$C$50, $A1)>1

    How to remove duplicates in google sheets

    Compare Multiple Columns for Duplicates

    If you would like to determine duplicates by comparing data in multiple columns of the Google Sheet, use COUNTIFS instead of COUNTIF .

    For instance, if column A is Email Address and column B is Company Name and you would like highlight duplicates cell only when the combination of values in Column A and B is identical, the new formula can be written as =COUNTIFS(A:A, A1, B:B, B1)>1

    Remove Duplicate Rows in Google Sheets

    Now that we have figured out a simple method to highlight duplicates in Google Sheets, the next task is to remove all duplicate rows.

    There are two ways to go about it – either use Google Apps script or use the built-in feature of Google Sheets to remove duplicates.

    First, highlight the entire column in Google Sheet that contains the duplicate data. Next, go to the Data menu and choose the Remove Duplicates option.

    Select which columns to include and whether or not the selected range has any header row. Click Remove duplicates and your list is clean up in one go. Like with COUNTIF function, Google Sheets will ignore case and formatting when determining duplicates.

    How to remove duplicates in google sheets

    Remove Duplicates with Google Scripts

    If you prefer automation, here’s a little snippet that will remove the duplicates in your active Google Sheet based on data in the first column.