Today, we are going to have a close look at how to show duplicates in Excel. You will learn how to shade duplicate cells, entire rows, or consecutive dupes using conditional formatting. Also, you will see how to highlight duplicates with different colors using a specialized tool. Continue reading
Comments on: How to highlight duplicate cells and rows in Excel
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 53
How do you filter out the last record in a duplicate occurrence?
Hi Mark,
You can use Duplicate Remover to find dupes with the first occurrences (described in the article) and then sort the found entries using the standard Excel Sort.
Highlighting entire rows based on duplicate values in one column - this was exactly what I was looking for, so useful!
Is it possible to make each set of duplicates a different colour? I have the duplicates below, can I make the ones ending in 67H a different colour to the ones ending in 90H?
Computer
1C108749H
1F168937H
1F168967H
1F168967H
1F168978H
1F168990H
1F168990H
If you think by automated function then NO. but it is possible if you apply multiple conditions by following these steps
FIRST CONDITION:
Home>Conditional Formatting>New Rule>Select a Rule Type>Format only cells that contain>Edit the rule description>SPECIFIC TEXT>CONTAINING>67H>FORMAT>CHOOSE YELLOW COLOR
THEN FOR THE SECOND CONDITION
Home>Conditional Formatting>New Rule>Select a Rule Type>Format only cells that contain>Edit the rule description>SPECIFIC TEXT>CONTAINING>90H>FORMAT>CHOOSE RED COLOR
If you have more conditions then repeat the above process with a different colour.
The formula's here helped but since I wanted to check for duplicates over multiple columns (without the 1st occurrence), I couldn't do it with your formula.
Although when I tested with it I changed it from: =COUNTIF($A$2:$A2,$A2)>1 into:
=COUNTIF($A$2:A2,A2)>1
And then it was able to check over a wider range than just 1 column.
This does however give it's own problems since it's not working as well as I want it too.
It works fine for most cells except when they're diagonal from each other with the first occurrence in the left column being lower than the one in the right column.
I guess it's easier to show for those that are interested:
https://docs.google.com/spreadsheets/d/1vskEHr5IJzG56Aqqa8E8NNafclE2h7dyYxNaJ1gG5Tc/
My final question would be how to have a perfect solution for this, maybe add another conditional formatting rule that would check from the bottom right to the top left. But this rule would have to be embedded in the first rule or else it will overwrite the "skip 1st occurrence". (it might still do that if it's embedded though, I'm not sure)
Hi Bram,
To highlight duplicates without the 1st occurrence in a range (multiple columns), you can use a formula similar to this:
=(IF(COLUMNS($F20:F20)>1,COUNTIF(E$20:$F$33,F20),0)+COUNTIF(F$20:F20,F20))>1
It's written for the 4th data set in your test sheet, you can check it out there.
Thank you for this solution!
Sadly I don't quite get it yet (for instance, it uses column E in which there is no data) but it works beautifully.
I'll research it in some more depth later on!
I just thought that other users might be looking for a similar solution, so I added an example with more details: How to highlight duplicates in multiple columns
:< This just gives a popup that Excel does not recognize this as a proper forumla for me...
Hi Max,
Most likely, on your computer the List Separator is set to a different character. To check this, please go to Control Panel > Region > Additional Settings, and see what character is set for List Separator. Generally, it's either a comma (my case) or semicolon. If the latter, then simply replace the commas separating the arguments in the formula with semicolons. For more information, please see Excel formulas not working.