by Learn how to highlight duplicates in Google Sheets using conditional formatting with different custom formulas or a specialized Remove Duplicates extension, which spots repeated values instantly without formulas. In this guide, I’ll show you how to find matches in one, two, or multiple columns, and how to highlight an entire row based on a value in one column or identify absolute duplicate rows.
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. Below, I will cover the most popular cases — from highlighting single columns to identifying entire duplicate rows.
Quick Summary: Ways to highlight duplicates
Option 1: Conditional formatting
Apply a conditional formatting rule with Custom Formulas to highlight duplicates:
| If you want to highlight... | Use this Custom Formula |
|---|---|
| Duplicates in one column | =COUNTIF($A$2:$A$10, $A2) > 1 |
| Matches between two columns | =COUNTIF($A$2:$A$10, B2) > 0 |
| Duplicates across multiple columns | =COUNTIF($A$2:$C$10, A2) > 1 |
| Entire rows based on one column | =COUNTIF($B$2:$B$10, $B2) > 1 |
| Absolute duplicate rows | =COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2, $C$2:$C$10,$C2) > 1 |
| Duplicates excluding 1st instances | =COUNTIFS($A$2:$A2,$A2, $B$2:$B2,$B2, $C$2:$C2,$C2) > 1 |
Option 2: Remove Duplicates extension
If you prefer a formula-free approach, use the Remove Duplicates extension to highlight and clean your data in 4 easy steps:
- Pick the data you want to check.
- Select Duplicates.
- Pick the columns to search in.
- Choose a highlight color and click Finish.
With the Remove Duplicates add-on for Google Sheets, no formulas are needed. Moreover, you can not only highlight, but automatically move, copy, or delete duplicates and find uniques. As a bonus option, you’ll even be able to schedule it to clean your data automatically.
Highlight duplicate cells in one column in Google Sheets
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.
Here's how to highlight duplicate cells in one Google Sheets column:
- Go to Format > Conditional formatting in the Google Sheets menu:
- You will see the Conditional format rules window with the Single color tab opened by default:
- Select the range of cells where you want to highlight dupes (in the apply to range field) — A2:A10 in my example:
- Under Format rules, pick Custom formula from the drop-down:
- And enter the following formula:
=COUNTIF($A$2:$A$10,$A2)>1Note. Formulas in conditional formatting are dynamic. Hence, in this COUNTIF, I lock only A in A2 with a dollar sign. This way, the formula will handle each cell from column A. You will learn more about this trick with cell references in this article. - Select any color from the Formatting style to highlight those duplicates.
- Click Done.
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.
Google Sheets: highlight duplicates in two columns
Sometimes you need to compare two columns and highlight duplicates in the second one to see which items are already present.
To highlight duplicates between two columns:
- Apply the rule to the range B2:B10 (only the column you’re checking).
- Type this custom formula in conditional formatting:
=COUNTIF($A$2:$A$10, B2) > 0
This COUNTIF takes each record in column B and looks for it in column A. If there's a match, that cell gets highlighted.
Tip. This is a quick way to spot matches in one sheet. For more tricks to find duplicates in two columns or missing data across files, check out our dedicated guide.
How to highlight duplicates in Google Sheets: multiple columns
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:
- Select A2:C10 as a range to color repeated cells within
- Change the range for Custom formula as well:
=COUNTIF($A$2:$C$10,A2)>1This 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.
- Pick a color in the Formatting style section and hit Done
This COUNTIF scans all 3 columns and counts how many times each value appears in the whole range. If more than once, conditional formatting will highlight these duplicate cells in your Google Sheets table.
Highlight the entire row if duplicate values are in same column
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:
- Apply the rule to the range A2:C10
- And here's the formula:
=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.
Highlight complete duplicate rows in spreadsheets
Now, what if the entire row (with the same 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?
Method 1: COUNTIF and ArrayFormula
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:
- ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10) concatenates every 3 cells from each row into one text string that looks like this: SpaghettiPasta9-RQQ-24
Thus, in my example, there are 9 such strings — one per row.
- Then COUNTIFS takes each string (starting from the first one: $A2&$B2&$C2) and looks for it among those 9 strings.
- If there's more than one string (>1), these duplicates get highlighted.
Tip. You may learn more about COUNTIF and the concatenation in Google Sheets in the related articles.
Note.This method can sometimes return "false positives" because it glues values together. For example, a row with "1" and "23" becomes "123", and a row with "12" and "3" also becomes "123". The formula will treat them as duplicates even though the columns are different.
Method 2: COUNTIFS formula
Alternatively, you can use COUNTIFS to identify entire row duplicates. It handles multiple criteria without concatenating text together:
=COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2, $C$2:$C$10, $C2) > 1
The COUNTIFS checks if the values across all specified columns match simultaneously and highlights them only when that specific combination appears more than once in your table. Because it checks each column separately, it is much more precise and won't be fooled by overlapping numbers or text.
Tip. Learn how to set multiple criteria within the COUNTIFS formula in one of our guides.
Highlight actual duplicates — 2nd, 3rd, etc instances
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 a little 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.
The formula for highlighting real duplicate rows:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2) > 1
Can you see the difference in the formula?
Instead of mentioning all rows ($A$2:$A$10), I use an expanding range that starts with only the first cell of each column ($A$2:$A2).
As the conditional formatting moves down the table, the range "grows" (e.g., in row 6, the formula checks $A$2:$A6). This lets each row look only above to see if there are matching rows. If so, every current row will be treated as another instance or, in other words, as an actual duplicate that will be colored.
You can also use a combination of COUNTIF and ArrayFormula for the same result, though it is slightly more complex:
=COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2&$C$2:$C2),$A2&$B2&$C2)>1
Filter by color in Google Sheets
Once you highlight duplicates in your Google Sheets, you can filter the data by color.
Learn more about how to set up filters and exclude duplicates in this guide.
Hide duplicates from your view
To filter the column so that only uniques (cells with the white fill color in our example) remain visible:
- Go to Data > Create a filter.
- Click the Filter icon in your header row.
- Select Filter by color > Fill Color > White.
This way, you will delete duplicates from the view without actually deleting them.
Delete highlighted duplicates completely
To delete these duplicates from your Google Sheets completely, do the opposite — keep only the colored cells visible:
- Filter the column by your highlight color.
- Select all visible rows.
- Right-click and select Delete selected rows.
When you turn off the filter, you'll see your remaining unique data.
Formula-free way to highlight duplicates — Remove Duplicates add-on for Google Sheets
It's not a secret that any formula and conditional formatting require a learning curve. If you're not ready to devote your time to those, Remove Duplicates is the easiest solution.
Remove Duplicates extension for Google Sheets will highlight duplicates for you.
It takes just a few clicks on 4 steps. There's a separate step for each action so you won't get confused:
- First, the add-on offers an intuitive way to select your data:
- Our add-on knows how to highlight not only duplicates but also uniques. And there's an option to ignore 1st instances as well — all on step 2:
- Next, you pick columns you'd like to check for duplicates:
- And finally, the option to highlight found duplicates is just a radio button with a color palette on the last step:
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:
Highlight duplicates on schedule using the 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 by clicking the button below.
As soon as you try it on your data, you'll see how much time and nerves it saves on getting those records colored correctly. Yes, Remove Duplicates highlights matches without any formulas and in just a few clicks 😉
Video: How to highlight duplicates in Google Sheets
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.
Practice spreadsheet
Highlight duplicates in Google Sheets - conditional formatting examples (make yourself a copy to practice)
Latest comments
=COUNTIF(ARRAYFORMULA($B$2:$B$10&$D$2:$D$10&$G$2:$G$10),$B2&$D2&G2)<1.
Why this farmula is not wokring?everything seems fine.
Waqar, your formula will always return FALSE because the criterion ($B2&$D2&G2) is part of the first row of the ARRAYFORMULA, since it’s included in the range, and the result of the COUNTIF will be at least 1.
Great guide on highlighting duplicates in Google Sheets! Your step-by-step approach makes it easy to follow, especially with the clear explanations of conditional formatting and formulas. I appreciate the tips on handling multiple columns too. This will definitely save time and enhance spreadsheet organization. Thanks for sharing these valuable techniques!
Thank you for your feedback!
...My local is Brasil so ";" is correct...
Thank you my Brazilian friend) The same here in Ukraine.
Hello
The tutorial is great! Thank you for it!
I need duplicates from column E to be highlighted when the date matches today's date from column A.
I played with various formulas but every time I ran into the same problem: the formula would highlight values from column E if the row had today's date, but it was the only entry on today's exact date, the reason it was highlighted is that the same value in E column was repeated on previous dates.
The formula I'm currently using is:
=AND(COUNTIF(E:E,E1)>1,A1=TODAY())
Is there any way for the formula to ignore previous dates?
How can we use different color for different duplicate values
Hello Paras,
I'm afraid you won't be able to do that with one simple formula or so.
If you want to try without the script, here's an example for a short list of names in A2:A8. First, assign each unique name a number in column B with the following formula:
=IF(COUNTIF($A$2:A2, A2)=1, MAX($B$1:B1)+1, VLOOKUP(A2, $A$1:$B1, 2, FALSE))
Copy the formula down the column.
Then you need to create as many conditional formatting rules as there are unique names. For example, for a first 'name + number 1' the rule will be:
=AND(COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2&$C$2:$C2),$A2&$B2&$C2)>1,$B2=1)
For the next 'name + number 2' the rule will be:
=AND(COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2&$C$2:$C2),$A2&$B2&$C2)>1,$B2=2)
You change the number at the very end of the formula in each rule based on the number assigned to the name in column B.
If you'd rather avoid so many formulas, you may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links:
https://developers.google.com/apps-script/overview
Thank you, i found solution to find duplicates
How to delete entire raw if it has duplicate cell
Hello Sanjay,
You will find different ways of removing rows with duplicates in this article. This one is the quickest and doesn't require formulas.
Great post. Thanks for sharing this article.
Thank you for your feedback! Happy to know the article is useful :)
Yes this worked but highlighted one of the duplicate cells and another some other cell. Can you solve ?
Hello Sanket Saliyan,
I'm sorry, I don't understand what you mean. What method have you tried and what has gone wrong?
Hey there!
Thx for the awesome info!
Is it possible to have the "find duplicates" keep going as I fill in the cells?
Example:
I've already ran the check and no duplicates were found, however, I now add another cell within the range that IS a duplicate, can it immediately fill in the cell with a certain color?
Or the only way is to run the formula AFTER all the cells are filled?
Thx a million! :)
Hey Nadav,
Thank you for your kind feedback :)
As for your question, if you mean the Remove Duplicates add-on described at the end, you need to run it each time after entering more records because there is no technical possibility to keep it constantly running in the background. But there are scenarios available that speed up the process: you run the add-on with just one click or even schedule its auto-start every hour or so.
If your question is about conditional formatting rules that you are to set up manually, you just need to indicate a bigger range in advance in the Apply to range field.
This worked for me (with a tiny bit of adjusting)...
But my Issue/question is:
Issue: Every duplicate is the same color... so if there is a string of duplicates in a row, it will highlight all of them (visually) as one group.
Question: is there a way to have alternating colors so that it's easier to tell when one group of duplicates stops, and another begins?
Hello Myers,
Sorry, it's impossible with one formula or setting. You'd need to create multiple conditional formatting rules and specify different colors for the exact values. Or use Google Apps Script to code something like this for your task.
Is it possible to exclude blanks for duplicates highlights in the same row? In other words, I no need the blank cells to be highlighted.
I have same issue with Raghavi .
i use this formula: =COUNTIF(ArrayFormula($I$3:$I$165&$J$3:$J$165),$I3&$J3)>1
It's highlight all the duplicate including the blank cells.
Thanks
Hello Durrah,
The same solution as Raghavi found will help you:
=AND(COUNTIF(ArrayFormula($I$3:$I$165&$J$3:$J$165),$I3&$J3)>1,$I3<>"")
Hi,
I'm getting almost, but the formula I'm using is highlighting the FIRST value, the original one, but I need to highlight the second or more duplicated value.
This is the formula I'm using:
=CONTAR.SI($B4:$B$5500,B)>1
=COUNTIF($B4:$B$5500,B)>1
What I need is to highlight the second repeated value.
Thanks in avance.
Hi ILIANA,
Please read this part to see how to highlight duplicate values keeping the original value intact.
Hello
Formula =COUNTIF($A$2:$A$10,$A2)>1 doesn’t seem to be working.. i put the correct range but it is not highlighting any cells what might be an issue?
Hello.
is it possible to highlight duplicate between multiple sheet ?
for example i want to highlight duplicate sheet1 column A & sheet2 column A
Hello Albert,
You will find the info on how to compare data in 2 different sheets in this article.
Hello,
Is it possible to exclude blanks for duplicates highlights in the same row? In other words, I no need the blank cells to be highlighted.
Hello Raghavi,
Please provide the exact formula you're using that counts blanks as well.
Hello Natalia,
I used =And(countifs(A:A,A1) > 1,A1"") to exclude blanks from highlighting. Now it is working well.
Hello Raghavi,
Happy to hear you've found the formula that works for you!
=countif($D2:$D, $D2)>1, this formula is not working for exact value, its highlighting all the similar in a cell. please help
Hello Namrata,
This formula is used to highlight duplicate cells in one Google Sheets column. If this is not what you need, please describe your task in detail, I'll try to help.
Hi! I've come across this great tutorial while searching for a way to higlight a row when there are at least two "x" in the row. Sadly, I'm already stuck. I've gone all the way to recreate the first example in order to test this out, but google says that the formula that's written here is wrong. In the formula "=COUNTIF($A$2:$A$10,$A2)>1" it doesn't let me put the ",$A2" after the "$A$10". In fact, it doesn't let me put a comma in there at all without showing an error. Does anybody know why that is the case?
Hi Cane,
You can make a copy of the spreadsheet that contains all formula examples I used by clicking the link in the related section at the end of the blog post.
As for Google Sheets not understanding a comma in your formulas, this is dictated by your spreadsheet locale (File > Spreadsheet settings > General > Locale). When you start typing any formula, look at the Google Sheets hint about it. Perhaps, you need to use a semicolon instead.