by Natalia Sharashova, updated on
Looking for a simple way to find duplicates in Google Sheets? How about 7 ways? :) That's all you need for numerous use cases :) I will show you how to use formula-free tools (no coding — promise!), conditional formatting and a few easy functions for avid formula fans.
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.
By the end of this article, you will know everything you need to remove duplicates, count them, highlight and identify with a status. I will show some formula examples and share different tools. One of them even finds and removes duplicates in your Google Sheets on schedule! Conditional formatting will also come in handy.
Just pick your poison and let's roll :)
Though traditionally I start tutorials with formulas, for this one I'd like to point out three the most easiest and effortless solutions first. Two of them were designed for the duplicate problem specifically.
You probably already know that Google Sheets owns their small, simple and unfussy tool to remove duplicates. It's called after its operation and resides under the Data > Data cleanup tab:
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:
Once you're ready, click that big green button, and the tool will find and delete duplicate rows from your Google Sheets table and say how many unique rows remain:
Though for many of you it's enough, this is as far as this tool goes. Each time you need to deal with duplicates, you will have to run this utility manually. Also, this is all it does: deletes duplicates. There's no way to count them, or color, or else.
Luckily, all these drawbacks have been solved in the Remove Duplicates add-on for Google Sheets from Ablebits.
Remove Duplicates an advanced version of the standard tool. But don't be afraid: advanced doesn't mean complicated. On the contrary, it's perfect for both spreadsheets beginners and pros.
Inside this toolkit, you'll find the Find duplicate or unique rows add-on. It offers 7 different ways to handle duplicates. And it doesn't just speed up the whole process – it knows how to automate it entirely.
Once you install it from the Google Workspace Marketplace, it will appear under the Extensions:
As the standard Google Sheets tool, it also lets you select the range and columns to process but more elegantly :)
All settings are divided into 4 user-friendly steps where you are to select:
You can even peek at special pictures so it's always clear what to do:
What's the point, you may think? Well, unlike the standard tool, this Remove Duplicates offers so much more:
Just pick whatever way suits you best, select the options and let the add-on do the job.
Tip. This video may be a bit old but it perfectly demonstrates how easy it is to work 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.
Or — even better — schedule those scenarios to kickstart automatically at a certain time daily:
Your presence is not necessary, and the add-on will delete duplicates automatically even when the file is closed or you're offline. To learn more about it, please visit this detailed tutorial and watch this demo video:
I encourage you to install the add-on from the Google Sheets store and poke around it. You'll see how easy it is to find, remove and highlight duplicates without formulas in just a few clicks.
The special tools from points above are not the only ones suitable to find duplicates in Google Sheets.
You can use Pivot table instead to sort of turn your data around and display it another way. And it doesn't affect your original table. The latter works as a reference while the result is on a separate tab.
That result, by the way, will change dynamically depending on the settings you can tweak on the go.
In the case of repeated records, the pivot table will help you count and remove duplicates in Google Sheets.
If your column contains numeric records, pick COUNT as a summary function for Values to count duplicates in Google Sheets. If you have text, select COUNTA instead:
If you do everything correctly, the pivot table will feature each item from your list and get you the number of times it appears there:
As you can see, this pivot table shows that only blackberry and cherry reoccur in my data set.
To delete duplicates using the pivot table, you need to add the rest of your columns (2 in my example) as Rows for your pivot table:
You'll see the table with duplicate rows yet numbers will tell which of them reoccur in the original dataset:
Tip. If you don't need the numbers anymore, just close the Values box in the Pivot table by pressing the corresponding icon at its upper-right corner:
This is what your pivot table will look like eventually:
No duplicates, no extra calculations. There are just unique records sorted out in one table.
Of course, you can also use formulas to find & remove duplicates in Google Sheets. Their main advantage is that your original table remains intact. The formulas identify duplicates and return the result to some other place in your Google Sheets. And based on the desired outcome, different functions do the trick.
The UNIQUE function scans your data, deletes duplicates and returns exactly what its name says — unique values/rows.
Here's a small sample table where different rows reoccur:
On one hand, you may need to remove all duplicate rows from this Google Sheets table and keep only the first entries.
To do that, just enter the range for your data inside UNIQUE:
This small formula returns all unique rows and all 1st occurrences ignoring 2nd, 3rd, etc.
On the other hand, you may want to get only the "real" unique rows. By "real" I mean those that don't reoccur — not even once. So what do you do?
Let's take a moment and look through all UNIQUE arguments:
That last argument is your leverage here.
Hence, to remove all duplicate rows from your Google Sheets completely (along with their 1st ), skip the second argument in the formula but add the third:
See how the table on the right is much shorter? It's because UNIQUE found and removed duplicate rows as well as their 1st occurrences from the original Google Sheets table. Only unique rows remain now.
If taking up space with another dataset is not part of your plan, you can count duplicates in Google Sheets instead (and then delete them manually). It'll take just one extra column and the COUNTIF function will help.
Tip. If you're not familiar with this function, we have an entire blog post about it, feel free to take a look.
Let's identify all duplicates with their 1st occurrences in Google Sheets and check the total number of each berry appearing on the list. I will use the following formula in D2 and then copy it down the column:
Tip. To make this formula handle each row in the column automatically, wrap everything in ArrayFormula and change $B2 to $B2:$B10 (the whole column). Thus, you won't need to copy the formula down:
If afterwards you filter this dataset by the numbers, you will be able to see and even remove all excess duplicate rows from your Google Sheets table manually:
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 entry, you'll need to make a slight adjustment to the formula.
Change the range from the entire column ($B$2:$B$10) to just one cell ($B$2:$B2).
Note. Pay attention to the use of absolute references.
This time, deleting any or all duplicates from this Google Sheets table will be even easier because you'll be able to hide all entries but the 1st ones:
While the above formulas count duplicates in just one Google Sheets column, you may need a formula that considers all columns and thus identifies duplicate rows.
In this case, COUNTIFS will suit better. Just list every column of your table along with its corresponding criteria:
Tip. There's another way available to calculate duplicates — without formulas. It involves a Pivot table and I describe it further.
Sometimes numbers are just not enough. Sometimes it's better to find duplicates and mark them in a status column. Again: filtering your Google Sheets data by this column later will let you remove those duplicates you no longer need.
For this task, you will need the same COUNTIF function but this time wrapped in the IF function. Just like this:
Let's see what happens in this formula:
Of course, you can get the formula to return your own statuses, or, for example, find & identify only duplicates in your Google Sheets data:
Tip. As soon as you find these duplicates, you can filter the table by the status column. This way lets you hide repeated or unique records, and even select entire rows & delete these duplicates from your Google Sheets completely:
Similarly, you can mark absolute duplicate rows — rows where all records in all columns appear several times in the table:
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:
Tip. If you are using Microsoft Excel, the following examples might be helpful: How to find duplicates in Excel.
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. Conditional formatting will help you with this.
Tip. Visit this tutorial for more formulas to highlight duplicates in Google Sheets.
Here's what you need to do:
Once it locates records that appear at least twice in column B, they will be colored with a hue of your choice:
Another option would be to highlight duplicate rows. Simply adjust the range to apply the rule to:
Tip. Once you highlight duplicates in your Google Sheets, you can filter the data by color:
and then select these rows and delete these duplicates from your Google Sheets completely:
Find & remove duplicates in Google Sheets - formula examples (make a copy of the spreadsheet)
Table of contents