Find and remove duplicates in Google Sheets

July 27, 2021
Find and remove duplicates in Google Sheets
Table of Contents
Table of Contents

In this post, I will show you how to quickly find and remove 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.

Methods we will cover

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

Remove Duplicates

If you don’t need to review duplicate email addresses and simply want to remove them, 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

Highlight Duplicates with Conditional Formatting

Using this method, you can highlight duplicate values in the column 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.

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

If you have several conditional formatting rules, you can remove specific rules in the Conditional formatting sidebar panel:

You can also remove highlighting rules from the Conditional Formatting sidepane

Count how many times a value is repeated

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 if you want to be able to easily filter the sheet by unique and repeated values.  

  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.

Find the first and repeated instance of a value

If you want to find the first and repeated instances of an email address in the sheet, 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.

Get unique values into 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

Summary


As we saw, there are several ways to remove duplicates in Google Sheets:

  1. Use the in-built Remove Duplicates feature
  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