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:

EasyExcel_23_1_Find Duplicates in Excel

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”.

EasyExcel_23_2_Find Duplicates in Excel

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.

EasyExcel_23_3_Find Duplicates in Excel

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.

EasyExcel_23_4_Find Duplicates in Excel

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

=COUNTIF($A$2:$A$28,A2)>1

More easy tricks with Conditional Formatting:

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish

20 thoughts on “How to Find Duplicates and Triplicates in Excel

  1. 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.)

  2. 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?

    Thanks

  3. HI, good day !

    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 ?

    many thanks
    Senna
    htadis@gmail.com

  4. 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.

  5. When i copy this formula: =countif($a$2:$a$25,$a$2)>2

    it highlights the entire column rather than just the cells once a triplicate arrives – what am i doing wrong?

  6. How annoying that Audun does not answer the two people who have the same issue I have, but answers everyone else. It is pointless to type in a formula when one can just use the Find function for their dupes. Sure – it would be annoying to do this for large amounts of data, but my frustration is that I DON’T KNOW what the values are that are dupes, triplicates etc, and that is what I need excel to find for me. What does the formula look like without a criterion????

  7. Hi, any idea on an Excel of championship results that contains four columns of names and four of points and I have to find and highlight all the names that are triplicates (those qualify for an elite championship)? What criterion should I set? The “A2” does not work.

  8. this works… mostly. But when highlighting triplets, it only highlights 2 of the 3 and not the one that’s furthest down on the list. Likewise, if I highlight duplicates, it only highlights the highest one on the list.

    thanks for the help!

    • Be sure to include all the rows in your formula: If you have a range from A1 to A200, for example, the formula in Conditional formatting should be
      =COUNTIF($A$1:$A$200,A1)=$E$2
      (Cell E2 is where you have the criterion; 2 for duplicates, 3 for triplicates etc)

  9. Pingback: Compare List Excel – Almazrestaurant

Leave a Reply

Your email address will not be published. Required fields are marked *


8 × seven =