by Natalia Sharashova, updated on
In my previous blog post, I described different means to find and process duplicates in your spreadsheet. But in order to spot them instantly, it would be best to highlight them with color.
And today I will try to cover the most popular cases for you. You will highlight duplicates in Google Sheets using not only conditional formatting (there are different formulas based on the spread of duplicates in your table) but also a special add-on.
Let's start with the basic example. It is when you have just one column with repeated values:
Tip. I'm going to use conditional formatting in every but the last case today. If you're not familiar with it, get to know it in this blog post.
To highlight duplicate cells in one Google Sheets column, open conditional formatting and set the following options:
=COUNTIF($A$2:$A$10,$A2)>1
Note. There's a dollar sign next to the letter for A2. It is intentional so the formula could count each cell from column A. You will learn more about cell references in this article.
That COUNTIF formula will scan your column A and tell the rule which records appear more than once. All these duplicate cells will be colored according to your settings:
Tip. See how to count cells by color in Google Sheets in this article.
It may happen that repeated values will be in more than one column:
How do you scan and highlight duplicates in all 3 Google Sheets columns then? Using the conditional formatting as well. The drill is the same as above with a few slight adjustments:
=COUNTIF($A$2:$C$10,A2)>1
Note. This time, remove the dollar sign from A2. This will let the formula count all occurrences of each cell from the table, not just from column A.
Tip. Read this article to learn more about relative, absolute, & mixed cell references.
Unlike the aforementioned COUNTIF, this one scans all 3 columns and counts how many times each value from the table appears in all columns. If more than once, conditional formatting will highlight these duplicate cells in your Google Sheets table.
Next up is the case when your table contains different records in each column. But the entire row in this table is considered as a single entry, a single piece of information:
As you can see, there are duplicates in column B: pasta & condiment sections occur twice each.
In cases like this, you may want to treat these entire rows as duplicates. And you may need to highlight these duplicate rows in your Google spreadsheet altogether.
If that's exactly what you're here for, make sure to set these for your conditional formatting:
=COUNTIF($B$2:$B$10,$B2)>1
This COUNTIF counts records from column B, well, in column B :) And then the conditional formatting rule highlights not just duplicates in column B, but the related records in other columns as well.
Now, what if the entire row with records in all columns appears several times in your table?
How do you check all 3 columns through the table and highlight absolute duplicate rows in your Google sheet?
Using this formula in conditional formatting:
=COUNTIF(ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1
Let's break it down into pieces to understand how it works:
Thus, in my example, there are 9 such strings — one per row.
Tip. You may learn more about COUNTIF and the concatenation in Google Sheets in the related articles.
Let's suppose you'd like to keep the 1st entries of duplicate rows intact and see all other occurrences if there are any.
With just one change in the formula, you'll be able to highlight these 'real' duplicate rows — not the first entries, but their 2nd, 3rd, 4th, etc instances.
So here's the formula I suggested right above for all duplicate rows:
=COUNTIF(ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1
And this is the formula you need to highlight only duplicate instances in Google Sheets:
=COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2&$C$2:$C2),$A2&$B2&$C2)>1
Can you see the difference in the formula?
It's in the first COUNTIF argument:
$A$2:$A2&$B$2:$B2&$C$2:$C2
Instead of mentioning all rows like in the first formula, I use only the first cell of each column.
It lets each row to look only above to see if there are the same rows. If so, every current row will be treated as another instance or, in other words, as an actual duplicate that will be colored.
Of course, you may have some other use case that requires another formula. Nonetheless, any formula and conditional formatting require a learning curve. If you're not ready to devote your time to those, there's an easier solution.
Remove Duplicates add-on for Google Sheets will highlight duplicates for you.
It takes just a few clicks on 4 steps, and the option to highlight found duplicates is just a radio button with a color palette:
The add-on offers an intuitive way to select your data and pick columns you'd like to check for duplicates. There's a separate step for each action so you won't get confused:
Besides, it knows how to highlight not only duplicates but also uniques. And there's an option to ignore 1st instances as well:
Tip. Here's a video that shows the add-on in action. It may be a bit old since at the moment the add-on has more to offer, but it's still the same add-on:
All the steps with their settings that you select in the add-on can be saved and reused in a click later or even scheduled to a certain time to autostart.
Here's a 2-minute demo video to back up my words (or see below for a couple animated images):
And here's a short animated image instead showing how to save and run scenarios once your data changes:
What's even better, you can schedule those scenarios to autostart a few times a day:
No worries, there's a special log sheet available for you to track all automatic runs & make sure they work correctly:
Just install Remove Duplicates from the Google Sheets store, try it on your data, and you'll see how much time and nerves you will save on getting those records colored correctly. Yes, without any formulas and in just in a few clicks ;)
This 1,5-minute video shows 3 quickest ways (with and without formulas) to find & highlight duplicates in Google Sheets. You will see how to color 1 column or entire rows based on duplicates, even automatically.
Highlight duplicates in Google Sheets - conditional formatting examples (make a copy of the file)
Table of contents