How to find and remove duplicates in Google Sheets

Looking for a way to find duplicates in Google Sheets? How about 7 ways? :) Delete, highlight, count duplicates. Use formulas, conditional formatting or formula-free tools. Come pick your poison and deal with duplicates for good :)

No matter how often you use Google Sheets, chances are you have to deal with duplicated data. Such records may appear in one column or take up entire rows.

In this article, I share ways to find and remove duplicates in Google Sheets using formulas and different tools. Among other things, I'll show you how to highlight duplicates in Google Sheets with the help of conditional formatting.

Find duplicates in Google Sheets using formulas

Let's start with formulas. Based on the outcome you'd like to see, different functions will do.

Remove duplicates using the UNIQUE function

Suppose you need to find and remove duplicates in your Google Sheets so only unique records remain:
Duplicate rows in Google Sheets.
The UNIQUE function will do the trick:

=UNIQUE(A1:C10)
Delete duplicate rows in Google Sheets with the UNIQUE function.
This formula returns all 1st occurrences after removing 2nd, 3rd, etc ones.

However, if you want to get the 'real' unique rows — those that don't reoccur, not even once — skip the second argument in the formula but add the third:

UNIQUE(range,[by_column],[exactly_once])
  • range — the data you want to process.
  • [by_column] — skip if you're looking for duplicate rows. Or enter TRUE if it's columns that reoccur.
  • [exactly_once] — enter TRUE to ignore 1st occurrences. Skip this as well to consider all existing instances.

And here's what your formula for the 'real' unique rows should look like:

=UNIQUE(A1:C10,,TRUE)
Get only unique rows.

See that the table on the right is much shorter? It's because UNIQUE removed all duplicate rows and their 1st occurrences from the original Google Sheets table. Only unique rows remain.

COUNTIF function for Google Sheets to count duplicates

If taking up space with another dataset on your Google sheet is not part of your plan, you can count duplicates instead. It'll take just one extra column and COUNTIF will help.

Tip. If you're not familiar with the COUNTIF function, we have an entire blog post about it, take a look.

Let's see how many times each berry appears in the list. I use the following formula in D2 and then copy it down the column:

=COUNTIF($B$2:$B$10,$B2)
Count the total number of occurrences for each berry in the list.

Tip. If you wrap everything in ArrayFormula and change $B2 to $B2:$B10 (the whole column), the formula will count each row for the column automatically. In other words, you won't need to copy the formula down:
Incorporate ArrayFormula to count the occurrences of each berry at once.

In case the total number of occurrences is not your goal and you'd rather know whether this particular record in this particular row is the 1st, 2nd, etc occurrence, you'll need to make a slight adjustment.

Just change the range from the entire column ($B$2:$B$10) to one cell ($B$2:$B2).

Note. Pay attention to the use of absolute references.

=COUNTIF($B$2:$B2,$B2)
Mark each 1st, 2nd & 3rd occurrence.
While the aforementioned Google Sheets formulas count duplicates in just one column, you may need a formula that takes into account all columns and thus counts duplicate rows.

In this case, just swap COUNTIF for COUNTIFS and list every column of your table along with its corresponding criteria:

=COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)
Use COUNTIFS to count duplicate rows.

Tip. There's another way available to count duplicates — without formulas. It involves a Pivot table and I describe it further.

Find duplicates in Google Sheets and mark them in the status column — IF function

Sometimes numbers are just not enough. Sometimes it's better to find duplicates and mark them in a status column. You can filter this column later in order to see and process only duplicate or unique records.

For this task, you will need the same COUNTIF function but this time wrapped in the IF function. Just like this:

=IF(COUNTIF($B$2:$B$10,$B2)>1,"Duplicate","Unique")
Mark Google Sheets duplicates & uniques in a status column.
Let's see what happens in this formula:

  1. First, COUNTIF searches the entire column B for the berry from B2. Once found, it sums them up.
  2. Then, IF checks the total acquired by COUNTIF. If it's greater than 1, it returns Duplicate, otherwise, Unique.
Tip. In case you still don't fully understand the way this combo works, make sure to get to know each function individually: here's more about the COUNTIF and here's for the IF function.

Of course, you can always get the formula to return your own statuses, or, for example, find & identify only duplicates in your Google Sheets data:

=IF(COUNTIF($B$2:$B$10,$B2)>1,"Duplicate","")
Leave cells for unique records empty.

Tip. As soon as you find these duplicates, you can filter the table by the status column. This way lets you hide repeated records, hide unique records, and even select entire rows & remove these duplicates from your Google Sheets completely:
Filter your data by the status column.

In a similar manner, you can mark absolute duplicate rows — rows where all records in all columns appear several times in the table:

  1. Start with the same COUNTIFS from before — the one that scans each column for its first value and counts only those rows where all 3 records in all 3 columns repeat themselves:

    =COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)

  2. Then enclose that formula in IF. If the count exceeds 1, name the row as a duplicate:

    =IF(COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)>1,"Duplicate","")

Find duplicate rows with the same records in all columns.
There are now only 2 dupes because even though cherry occurs 3 times in a table, only two of them have all 3 columns identical.

To ignore the 1st occurrence and mark only the 2nd and the other ones, refer to the first cells of the table instead of the entire columns:

=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1,"Duplicate","")
Find duplicates without the 1st occurrences.

Highlight duplicates in Google Sheets

There's a possibility to process repeated data in such a way, that a single glance at your table will give you a clear understanding of whether this is a dupe record.

I'm talking about highlighting duplicates in Google Sheets using conditional formatting.

Tip. Never tried conditional formatting in spreadsheets? No worries, we explained how it works in this blog post.

Here's what you need to do:

  1. Open conditional formatting settings: Format > Conditional formatting.
  2. Make sure that the Apply to range field contains the range where you want to highlight duplicates. For this example, let me start with column B.
  3. In Format rules pick Custom formula is and enter the same COUNTIF that I introduced above:

    =COUNTIF($B$2:$B$10,$B2)>1

Once it finds records that appear at least twice in column B, they will be colored with a hue of your choice:
Highlight duplicate cells In Google Sheets.
Another option would be to highlight duplicate rows. Simply adjust the range to apply the rule to:
Highlight duplicate rows in Google Sheets based on the column values.

Tip. Once you highlight duplicates, you can filter your Google Sheets data by color:

  • On one hand, you can filter the column so that only cells with the white fill color remain visible. This way, you will hide duplicates from the view:

    Filter data by the fill color: hide duplicates.

  • On the other hand, you can keep only colored cells visible:

    Keep the colored cells visible.

and then select these rows and remove these duplicates from your Google Sheets completely:
Filter and remove duplicate rows from Google Sheets.

Formula-free ways to find and remove duplicates in Google Sheets

Formulas and conditional formatting are good, but there are other tools that will help you find duplicates. Two of them were designed for this particular problem.

Pivot table for Google Sheets to count duplicates

Did you know that you can use the pivot table from Google Sheets to count duplicates?

Tip. Since we have an entire tutorial devoted to pivot tables, below I will briefly cover the options needed to count duplicates.
  1. Go to Insert > Pivot table, specify your data range and a place for the pivot table:
    Insert pivot table.
  2. In the pivot table editor, add a column with your duplicates (Name in my example) for Rows and for Values.

    To count duplicates in Google Sheets, pick COUNT as a summary function for Values (for numeric records) or COUNTA for text:
    Tweak pivot table settings.

If you do everything correctly, the pivot table will feature each item from your list and count the number of times it appears there:
How to use Pivot table for Google Sheets to count duplicates.
As you can see, this pivot table shows that only blackberry and cherry reoccur in my data set.

Remove duplicates — standard data cleanup tool

Spreadsheets feature their small, simple and unfussy tool to remove duplicates. It's called after its operation and resides under the Data > Data cleanup tab:
Run the standard Remove Duplicates.
You won't find anything fancy here, everything is super straightforward. You just specify if your table has a header row and select all those columns that should be checked for duplicates:
Pick the columns in the standard tool.
Once you're ready, click that big green button, and the tool will find and remove duplicate rows from your Google Sheets table and say how many unique rows remain:
The resulting message of the standard Google Sheets tool.

Remove Duplicates add-on for Google Sheets

Another add-on you should try is called Remove Duplicates.

It contains 5 different tools that process duplicates, but for today you will need Remove Duplicate Rows:
Start the add-on from the Google Sheets menu.
Just like the standard tool, it also lets you select the range and columns to process, but in a more elegant way. All settings are divided into 4 user-friendly steps where you are to select:

  1. the range
  2. what to find: dupes or uniques
  3. the columns
  4. what to do with the found records

You can even peek at special pictures so it's always clear what to do:
Schemes will always hint at how it works.
What's the point, you may think? Well, unlike the standard tool, this add-on offers so much more:

  • find not only duplicates but uniques including or ignoring 1st occurrences
  • highlight found records in your Google Sheets
  • add a status column
  • copy/move the results to a new sheet/spreadsheet or any specific place within your spreadsheet
  • clear found values from cells
  • delete duplicate rows from your Google Sheet completely

Choose what to do with the found values.
It's utterly up to you which way suits you best. Just select the options and let the add-on do the job.

Tip. By the way, this video may be a bit old, but it perfectly demonstrates how easy it is to process duplicates with the add-on:

As icing on the cake, you will be able to save all the settings from all 4 steps into scenarios and run them later on any table with just a click. To learn more about it, please visit this tutorial.

I encourage you to install and poke around the add-on from the Google Sheets store. You'll see how easy it will be to find, remove and highlight duplicates without formulas in just a few clicks.

Spreadsheet with formula examples

Find & remove duplicates in Google Sheets - formula examples (make a copy of the spreadsheet)

You may also be interested in

Ukraine flag War in Ukraine! To support Ukraine and save lives please visit this page.