4 ways of checking for duplicates in Google Sheets

July 27, 2021
4 ways of checking for duplicates in Google Sheets
Table of Contents
Table of Contents

In this post, I will show you how to quickly find and delete duplicates from a Google Sheets column. If you are preparing a YAMM mail merge, it’s best to check for duplicate email addresses beforehand to avoid sending multiple emails to the same recipient. Fortunately, Google Sheets provide several easy ways to clean your list from duplicates.

How to Find and Delete Duplicates in Google Sheets: 4 Methods

  1. Remove Duplicates feature
  2. Conditional formatting
  3. COUNTIF formula
  4. UNIQUE formula

How to delete repeats in Google Sheets

If you want to know how to dedupe in Google Sheets and don’t need to review duplicate values, Google Sheets have a very quick and easy way to delete any repeated values in a column.

  1. Click on the Data menu and select Remove Duplicates
Click on the Data menu and select Remove Duplicates

2.   In the pop-up, check the box “Data has header row” (this prevents your header row from being taken into account for duplicates removal)

3.   Tick the column from which you want the duplicates to be removed: in my case, it’s Column A - Email.

4.   Click the Remove Duplicates button. Google Sheets will keep only the first instance of the email address and completely delete the cells with duplicates.

Check the box "Data has header row" and the column to analyze

In the end, you will get a pop-up informing you of how many duplicates were removed and the number of rows that remain.

A pop-up informs how many duplicates were removed

As you can see from the results, the second instance of brian@gmail.com was removed:

The second instance of the value is removed

If you need to remove the table rows which contain the same values in all columns, make sure to check all applicable columns in the pop-up. For example, let’s say my list contains 3 rows with the same email address brian@gmail.com and two different names - Brian A and Brian B.

The table contains 3 rows with the same email address and different names

I can remove the second occurrence of the combination brian@gmail.com and Brian B by checking both Column A and Column B for duplicates removal.

Check both columns to be analyzed for duplicates

And this is the result - only one instance of Brian B is kept in the list:

Only the cells with the same combination of email address and name were removed

Using conditional formatting to highlight duplicates in Google Sheets

Using this method, you can highlight duplicates in Google Sheets to review them. This method is just slightly more complex than the previous one.

  1. Select the range of cells you want to analyze and click Format -> Conditional Formatting.
Select the range of cells you want to analyze and click Conditional Formatting in the Format menu

2.   In the sidebar pane under the Format Rules dropdown select “Custom formula is”.

3.   Introduce the following formula in the field below:

=COUNTIF($A$2:$A$11,$A2)>1

Note: $A$2:$A$11 in the formula means that Google Sheet will analyze rows 2 through 11 in Column A, and the second argument $A2 indicates that it will start reviewing from the second row. If your sheet has a different structure, you will have to change the formula respectively to highlight duplicates in sheets.

Select Custom Formula Is and add =COUNTIF($A$2:$A$11,$A2)>1

4.    Click Done and the duplicate values in your list will be highlighted automatically:

Duplicate values are highlighted in the analyzed column

To remove highlighting:

  1. Select the range of cells
  2. Click Format -> Clear Formatting
Select Format -> Clear Formatting to remove highlighting of duplicate values

You can have multiple Google Sheet conditional formatting rules for duplicates at the same time If you do have several conditional formatting rules active and want to remove one or more, you can do this in the Conditional formatting sidebar panel:

You can also remove highlighting rules from the Conditional Formatting sidepane

Deploying COUNTIF to count repeated values

This method is similar to the above, but instead of highlighting a duplicate value, you will have a separate column showing how many times a particular email address appears in a column. It is convenient in Google Sheets to filter duplicates by denoting unique and repeated values too.  

  1. Start by creating a new column - for example, “Number of occurrences”.
  2. Introduce the following formula in the second row and drag it down:
=COUNTIF($A$2:$A$11,$A2)

Google Sheet will calculate how many times each value appears in the column: here, we can see that brian@gmail.com is repeated twice.

Use COUNTIF formula to count how many times a value is repeated

Now you can filter the sheet by values greater than 1 and analyze duplicate addresses in detail. Note that both the first and the second instances of the email address will show “2”.

Same as before, $A$2:$A$11 in the formula means that Google Sheet will analyze rows 2 through 11 in Column A, and the second argument $A2 indicates that it will start reviewing from the second row. If your sheet has a different structure, you will have to change the formula respectively.

Tip: formulas can be written in upper or lower-case: this won’t change the results.

How to dedupe in Google Sheets by finding the first and repeated instances

If you want to dedupe Google Sheets by finding the first and repeated instances of an email address, the COUNTIF formula will save the day again.

  1. Create a new column.
  2. Introduce the following formula in the second row and drag it down:
=(COUNTIF($A$2:$A2,$A2)=1)+0

The first occurrence of the value will be indicated by “1”, while any repetition will appear as “0”.

Use COUNTIF to find the first and repeated occurrences of a value

This method allows you to easily filter the sheet by the first or repeated instance. For example, you can filter in the first occurrences of the email addresses and use YAMM segmentation to send your campaign to the filtered rows only without removing any rows from your sheet.

Search for duplicates in Google Sheets by listing unique values in a different column

This is a simple method if rather than deleting duplicates in place, you want to pull unique values into another column or even sheet.

  1. Start by creating a new column - I will call it Unique Email:
Create another column to which you'd like to pull unique values

2.   In the second row of your new column, introduce the formula:

=UNIQUE(A2:A11)

3.   Press Enter. Now you have a new column with unique values only:

Introduce UNIQUE formula and press Enter to get the list of unique values from another column

What if you want to also get other columns into your new range? That’s easy to do: simply specify the complete range in the formula above, in my case it will be:

=UNIQUE(A2:B11)
Update the range in the UNIQUE formula to get unique values from other columns

Need help with Google Sheet Formulas? Learn these 9 useful formulas for efficiency.

How to get rid of duplicates in Google Sheets: Summary


Now that we have explained how to filter duplicates in Google Sheets, it is time to summarize all the different methods for easy reference:

  1. Use the in-built Remove Duplicates feature of Google Sheets to eliminate duplicates
  2. Highlight duplicate values by using conditional formatting
  3. Apply the COUNTIF formula to calculate how many times a value is repeated or to indicate the first and repeated instances of the value
  4. Try the UNIQUE formula to get unique values into another column(s).
Great! Next, complete checkout for full access to The Yet Another Mail Merge Blog
Welcome back! You've successfully signed in
You've successfully subscribed to The Yet Another Mail Merge Blog
Success! Your account is fully activated, you now have access to all content
Success! Your billing info has been updated
Your billing was not updated