Post navigation

How to Find Duplicates and Triplicates in Excel

Is there an easy way to locate and highlight duplicates in a list in Excel?

If you just want to remove the duplicates, the easiest way is to use the Advanced Filter or the built-in Remove Duplicates feature on the Data ribbon, but what if you want to find the duplicates in the list, keep them and highlight them with a different colour? That almost sounds like a job for a professional Excel consultant, but there’s no need for that – you can easily do it yourself! I’ll show you one easy way and one super-easy way:

First, the super-easy way:

Select the cells you want to check, go to the Home Ribbon, choose Conditional Formatting and select Highlight Cell Rules > Duplicate Values.

Then, the easy way

If you only want to locate duplicates, the super-easy way above is the right way to do it. But sometimes we want to make it more dynamic: If we want to be able to choose between highlightning duplicates, triplicates or quadruplicates, i.e. two, three or four occurrences of the same piece of data, we need another approach: Conditional Formatting with a formula.

First, let’s find out how to count the number of occurences in a list. In my example below I have 27 rows of data, with names in the range A2 to A28. In A2 we find the name Robert, so if we want to find out how many times Robert appears in the list, we can use this formula: =COUNTIF($A$2:$A$28,A2). In this example the formula will return 3.

We will use almost the same formula in Conditional Formatting: As you might already know, Conditional Formatting uses Boolean logic, which means that it checks whether or not a statement is TRUE, and formats the cells that return TRUE. First, let’s see how our formula works when we put it in the worksheet. We use the same formula as above, only with “=1”, “=2” or “=3” in the end, and we will get TRUE or FALSE for each statement. In this example, for Robert, the statement is true for “=3”.

So, let’s put the formula into Conditional Formatting, with one small adjustment: Instead of hard-coding the value after the equal sign (1,2,3 etc.) we’ll use a cell reference. I will have my reference in cell E2.

Select the cells you want to include in the search (A2:A28 in this example), go to the Home Ribbon and choose Conditional Formatting > New Rule. Select “Use a formula to determine which cells to format” and type this formula into the formula field:

=COUNTIF($A$2:$A$28,A2)=$E$2

Note that the range A2:A28 and the reference to E2 (number of occurrences) have to be locked with dollar signs (shortcut: F4). The criterion, A2, must remain unlocked.

The result: All the triplicates are highlighted. If you change the value in E2 to 2, you will get the duplicates instead, and if you change it to 1, all the unique values will be highlighted.

If you want to highlight all the recurrences of a value (both duplicates, triplicates, quadruplicates etc.), you can use this formula instead:

12 thoughts on “How to Find Duplicates and Triplicates in Excel”

Strangely none of this work for me except the predefined rules in excel. When I type a formula excel doesn’t mind it at all. I really don’t know why.

So my case is as follows:
I’ve got a column with values that ideally should come in couples. But there a some of them that are unique and some that are appearing more than twice. The ones that a unique are unnecessary and will be deleted but the others will receive special attention.

Is there any remedy to it, I will be happy to receive any suggestions!

You can use Conditional formatting with this formula: =COUNTIF($A$2:$A$1000,A2)=1
This will highlight the unique values. Or you can do as described above, and change the value in E2 to 1.
(You might have to replace the comma with a semi-colon, depending on your local settings.)

I have three columns, separated by other data, that I would like to compare and highlight triplicates. I have tried your method above and it works except for the very top row of the three columns. Is there any way to fix that?

Is it possible that the formula in Conditional Formatting doesn’t cover the whole range? If that’s not the case, I have no idea… You’re welcome to e-mail it to me (a.danielsen -(at)- gmail.com), and I can try to find the error.

I have a column with series of truck numbers. I need to high light their second arrival in one color and the third in another color. Your suggestion does not work to me as i can not name any truck number by A2. any other easy way to do this ?

Using Excel 2013 I get an error with your formula – if i type it without the ,A2 it says I have too few arguments, if I type it with the ,A2 part – it says that I have an error in the formula and should check it.
My case is the following – I have a column (F actually) with 4037 values and I need to find out which of the them is repeated 4 or more times.

What if I have duplicate, triplicate, quadruplicate, quintuplicate, sextuplicate, septuplicate, octuplicate, nonuplicate, decuplicate in a column and highlight all the greater than quintuplicate less than octuplicate? Thank you.