7 Easy Ways to Find and Remove Duplicates in Google Sheets (2023)

Looking for an easy way to find duplicates in Google Sheets? How about 7 ways? :) That's all you need for numerous use cases :) I'll show you how to use tools without formulas (no coding, I promise!), conditional formatting, and some simple functions for avid formula fans.

No matter how often you use Google Sheets, you probably have to deal with duplicate data. Such records can appear in one column or occupy entire rows.

By the end of this article you will knoweverything you need to remove, count, highlight and status duplicates.I will show some formula examples and introduce different tools. One of them even finds and removes duplicates in your Google spreadsheets on a schedule! Conditional formatting will also come in handy.

Just pick your poison and let's roll :)

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

While I traditionally start tutorials with formulas, for this one I want to start by showing three of the simplest and most effortless solutions. Two of these were specifically designed to address the duplication problem.

Remove Duplicates - Standard Data Cleanup Tool

You probably already know that Google Sheets has a small, simple, and easy deduplication tool. It is named for its operation and is under theData > Data cleansingEyelash:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (1)

You won't find anything fancy here, everything is very simple. Just tell if your table has a header row and select all columns to check for duplicates:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (2)

When you're done, click this big green button and the tool will find and remove duplicate rows from your Google Sheets spreadsheet and say how many unique rows are left:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (3)

While it is enough for many of you, this tool is no longer enough. Anytime you need to deal with duplicates, you have to run this utility manually. Also, that's all it does: remove duplicates. There is no way to count them or color them or anything else.

Fortunately, all of these issues have been fixed in the Ablebits Remove Duplicates plugin for Google Sheets.

Eliminate Duplicates-Add-on für Google Sheets

eliminate duplicatesan improved version of the standard tool. But don't worry: advanced doesn't mean complicated. On the contrary, it is perfect for spreadsheet beginners and professionals alike.

In this toolkit you will find theFind duplicate or unique rowsAdd. It offers 7 different ways to deal with duplicates. And it not only speeds up the whole process, but knows how to fully automate it.

After you install it from the Google Workspace Marketplace, it will appear under theextensions:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (4)

As a standard Google Sheets tool, you can also select the range to process and the columns, but in a more elegant way :)

All settings are divided into 4 easy to use steps where you have to choose:

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

You can even look at special images so you're always clear on what to do:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (5)

What is it about, you might be thinking? Well, unlike the standard tool, Remove Duplicates offers much more:

  • find duplicatesas well asUniques that include or do not include the first appearance
  • To markDuplicate in Google Sheets
  • add a status column
  • copy/movethe resultsto a new sheet/tableor in a specific location in your spreadsheet
  • Of coursefoundValuesof cells
  • remove duplicate rowscompletely from your google sheet

7 Easy Ways to Find and Remove Duplicates in Google Sheets (6)

(Video) How to remove duplicates in Google Sheets

Just choose the way that suits you best, select the options and let the plugin do the work.

Above.This video may be a bit old, but it perfectly shows how easy it is to work with the plugin:

Let the plugin remove duplicates automatically

As the icing on the cake, you can save all 4-step settings to stages and then run them on any table with just one click.

Or, better yet, schedule these scenarios to start automatically at a specific time every day:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (7)

Your presence is not required and the plugin automatically removes duplicates even if the file is closed or you are offline. For more information on this, visitthis detailed tutorialand check out this demo video:


I recommend you install the plugin from theGoogle Sheets Storageand rummage in it. You'll see how easy it is to find, remove and highlight duplicates without formulas in just a few clicks.

Identify duplicates with the Google Sheets PivotTable

The special tools in the previous points are not the only ones suitable for finding duplicates in Google Sheets.

You can useDynamic tableInstead, it flips your data and displays it in a different way. And it doesn't affect your original spreadsheet. The latter serves as a reference while the result is displayed in a separate tab.

By the way, this result changes dynamically, depending on the settings you can adjust on the fly.

For repeat records, the pivot table will help you count and remove duplicates in Google Sheets.

Example 1. How the pivot table counts duplicates in Google Sheets

  1. GonnaInsert > Pivot Table, specify your data range and a location for the pivot table:
    7 Easy Ways to Find and Remove Duplicates in Google Sheets (8)
  2. In the pivot table editor, add a column with your duplicates (Namein my example) forrowsand toValues.

    If your column contains numeric records, select COUNT as the summary function toValuesto count duplicates in Google Sheets. If you have text, select COUNTA instead:
    7 Easy Ways to Find and Remove Duplicates in Google Sheets (9)

If you do everything right, the pivot table will display each item in your list and how many times it appears there:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (10)

As you can see, this pivot table shows that only blackberry and cherry appear again in my dataset.

Example 2. Remove duplicates in Google Sheets using a pivot table

To remove duplicates using pivot table you need to add the rest of your columns (2 in my example) likerowsfor your pivot table:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (11)

You will see the table with duplicate rows, but the numbers indicate which ones appear in the original record:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (12)

Above.If you no longer need the numbers, just close themValuesField in the pivot table by pressing the corresponding icon in the upper right corner:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (13)

This is what your pivot table will finally look like:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (14)
No duplicates, no additional calculations. There are only unique records ordered in a table.

(Video) Google Sheets - Highlight Duplicate Data in a Column or Row

How to find duplicates in Google Sheets using formulas

Of course, you can also use formulas to find and remove duplicates in Google Sheets. Its main advantage is that your original spreadsheet remains intact. Formulas identify duplicates and return the result elsewhere in your Google Sheets. And depending on the desired result, different functions are used.

How to remove duplicates in Google Sheets using the UNIQUE function

The SINGLE function scans your data, removes duplicates, and returns exactly what the name says: unique values/rows.

Here's a small sample table that repeats multiple rows:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (15)

Example 1. Remove duplicate rows, keep first occurrence

For one, you may need to remove all duplicate rows from this Google Sheets spreadsheet and keep only the first few entries.

To do this, simply enter the range of your data in UNIQUE:

=UNIQUE(A1:C10)
7 Easy Ways to Find and Remove Duplicates in Google Sheets (16)

This little formula will return all unique rows and all first occurrences, ignoring second, third, etc.

Example 2. Remove all duplicate rows, including first occurrences

On the other hand, you may want to get only the "real" unique rows. By "real" I mean the ones that don't happen again, not even. So, what are you doing?

Let's take a moment and look at all the UNIQUE arguments:

UNIQUE (range, [per_column], [exactly_once])

  • Area— is the data you want to process.
  • [per_column]: Indicates whether you are looking for matching rows or entire cells in individual columns. If they are columns, enter TRUE. If they are rows, enter FALSE or just omit the argument.
  • [exactly once]– This tells the function to not only remove duplicates in Google Sheets, but also your first entries. Or in other words, only return records with no duplicates. For this, set TRUE, otherwise FALSE or omit the argument.

The final argument is his influence here.

So, to completely remove all duplicate rows from your Google Sheets (along with the first), omit the second argument in the formula but add the third:

=UNIQUE(A1:C10,,TRUE)
7 Easy Ways to Find and Remove Duplicates in Google Sheets (17)

Notice that the table on the right is much shorter? This is because UNIQUE found and removed duplicate rows and their first occurrence from the original Google Sheets spreadsheet. Now only unique rows remain.

Identify duplicates with the Google Sheets COUNTIF function

If using another record isn't part of your plan, you can count duplicates in Google Sheets (and then manually delete them). Only one additional column is needed and the COUNTIF function will help.

Above.If you are not familiar with this feature, we have awhole blog post about it, Feel free to look around.

Example 1. Retrieve the total number of occurrences

Let's identify all duplicates with their first appearance in Google Sheets and check the total number of each berry listed. I'll use the following formula in D2 and then copy it into the column:

=CONTAR.SI($B$2:$B$10,$B2)
7 Easy Ways to Find and Remove Duplicates in Google Sheets (18)

Above.To have this formula handle each column row automatically, wrap everything in ArrayFormula and change it to$B2A$B2:$B10(entire column). So you don't need to copy the formula:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (19)

(Video) 3 EASY Ways to Find and Remove Duplicates in Excel

If you then filter this data set by numbers, you can manually view and even remove any excess duplicate rows from your Google Sheets spreadsheet:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (20)

Example 2. Find and list all duplicates in Google Sheets

If the total number of occurrences isn't your goal and you'd rather know if that particular record is the first, second, etc. entry in that particular row, you will need to adjust the formula slightly.

Change the range of the entire column($B$2:$B$10)to a single cell($ B $ 2: $ B2).

UseBe sure to use absolute references.

=CONTAR.SI($B$2:$B2,$B2)
7 Easy Ways to Find and Remove Duplicates in Google Sheets (21)

This time, removing some or all of the duplicates from this Google Sheets spreadsheet is even easier, since you can hide all but the first entries:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (22)

Example 3. Count duplicate rows in Google Sheets

While the formulas above count duplicates in a single column in Google Sheets, you may need a formula that counts all columns, thereby identifying duplicate rows.

In this case, COUNTIFS is more appropriate. Just list each column in your spreadsheet along with the appropriate criteria:

=ZÄHLENWENNS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)
7 Easy Ways to Find and Remove Duplicates in Google Sheets (23)

Above.There is another way to calculate duplicates, without formulas. It is a pivot table andI describe it more.

Mark duplicates in a status column: IF function

Sometimes the numbers just aren't enough. Sometimes it's better to find duplicates and mark them in a status column. Again, if you then filter your Google Sheets data by this column, you can remove duplicates you no longer need.

Example 1. Find duplicates in 1 column in Google Sheets

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

=IF(COUNTIF($B$2:$B$10,$B2)>1,"Duplicate","Unique")
7 Easy Ways to Find and Remove Duplicates in Google Sheets (24)

Let's see what happens in this formula:

  1. First, COUNTIF searches the entire column B for the berry of B2. Once found, it summarizes them.
  2. So IF checks that sum, and if it's greater than 1, saysDouble, otherwise,Single.

You can of course get the formula to return your own states, or just find and identify duplicates in your Google Sheets data, for example:

=IF(COUNTIF($B$2:$B$10,$B2)>1,"Duplicate","")
7 Easy Ways to Find and Remove Duplicates in Google Sheets (25)

Above.Once you find these duplicates, you can filter the table by the status column. This way you can hide repeating or unique records and even select entire rows and completely remove those duplicates from your Google spreadsheets:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (26)

Example 2. Identify duplicate rows

Similarly, you can mark absolute duplicate rows: rows in which all records in all columns appear multiple times in the table:

  1. start with the sameCOUNTIFS of antes— the one that searches each column for its first value and only counts the rows where the 3 records are repeated in the 3 columns:

    =ZÄHLENWENNS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)

    (Video) Remove Duplicates - Google Sheets

  2. Then enclose this formula in IF. Checks the number of repeated rows, and if it exceeds 1, the formula names the row as a duplicate:

    =SI(CONTAR.SI($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)>1,"Duplicar","")

7 Easy Ways to Find and Remove Duplicates in Google Sheets (27)

Now there are only 2 duplicates because even though cherry appears 3 times in a table, only 2 of them have all 3 identical columns.

Example 3. If you find duplicate rows, ignore the first entries

To ignore the first occurrence and mark only the second and the others, query the first few cells of the table instead of the entire columns:

=SI(CONTAR.SI($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1,"Duplicar","")
7 Easy Ways to Find and Remove Duplicates in Google Sheets (28)

Above.If you use Microsoft Excel, the following examples may be helpful:How to find duplicates in Excel.

Identify and mark duplicates in Google Sheets with conditional formatting rules

There's an opportunity to process repetitive data in such a way that you can tell if it's a bogus entry with just one look at your spreadsheet.

I'm talking about highlighting duplicates in Google Sheets.Conditional formatwill help you with that.

Above.Visitit's tutorialfor more formulas to highlight duplicates in Google Sheets.

This is what you should do:

  1. Open the conditional formatting settings:Format > Conditional Formatting.
  2. Make sure thatapplication areaThe field contains the area where you want to highlight duplicates. For this example, let me start with column B.
  3. Informat ruleschooseThe custom formula isand enter the same COUNTIF that I entered above:

    =CONTAR.SI($B$2:$B$10,$B2)>1

Once it finds records that appear at least twice in column B, it will color them with a hue of your choice:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (29)

Another option would be to highlight the duplicate rows. Just adjust the range to apply the rule to:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (30)

Above.Once you've flagged duplicates in your Google Sheets, you can filter the data by color:

  • For one, you can filter the column so that only cells with the white fill color are visible. This is how you remove duplicates from the view:
    7 Easy Ways to Find and Remove Duplicates in Google Sheets (31)
  • On the other hand, you can leave only the colored cells visible:
    7 Easy Ways to Find and Remove Duplicates in Google Sheets (32)

and then select these rows and completely remove these duplicates from your Google Sheets:
7 Easy Ways to Find and Remove Duplicates in Google Sheets (33)

Table with examples of formulas

Find and remove duplicates in Google Sheets - Formula examples(Make a copy of the table)

You may also be interested in

  • How to Compare Data in Two Google Spreadsheets or Columns
  • Merge data from duplicate rows in Google Sheets based on a single column
  • Special plugin to merge duplicate rows, merge values ​​and add subtotals in Google Sheets

Videos

1. Remove Duplicates for Google Sheets – Automation
(Ablebits)
2. Removing Duplicates from Multiple Columns
(Spreadsheets & Coffee)
3. Google Sheets - Identify Duplicates between Two Worksheets
(Prolific Oaktree)
4. Excel - Find Duplicate Rows Based on Multiple Columns
(Learn Google Sheets & Excel Spreadsheets)
5. Remove Duplicates in sheets a google sheets addon
(SMALLPDF KENYA)
6. Google Sheets - Prevent Duplicate Entries
(Prolific Oaktree)
Top Articles
Latest Posts
Article information

Author: Mrs. Angelic Larkin

Last Updated: 04/16/2023

Views: 6127

Rating: 4.7 / 5 (67 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Mrs. Angelic Larkin

Birthday: 1992-06-28

Address: Apt. 413 8275 Mueller Overpass, South Magnolia, IA 99527-6023

Phone: +6824704719725

Job: District Real-Estate Facilitator

Hobby: Letterboxing, Vacation, Poi, Homebrewing, Mountain biking, Slacklining, Cabaret

Introduction: My name is Mrs. Angelic Larkin, I am a cute, charming, funny, determined, inexpensive, joyous, cheerful person who loves writing and wants to share my knowledge and understanding with you.