Find Duplicates and Triplicates in Excel

A NEW VERSION OF THIS POST HAS BEEN PUBLISHED HERE: http://easy-excel.com/?p=503

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

If you just want to remove duplicates, you can use the Advanced Filter or the built-in Remove Duplicates feature, but what if you want to keep the duplicates in the list and highlight them with a different colour? I’ll show you one easy way and one super-easy way!

First, the super-easy way:

Select the cells you want to check and choose Highlight Cell Rules => Duplicate Values from the Home Ribbon.

Then, the easy way

If you only want to locate the duplicates, the super-easy way is the right way to do it. But let’s say you want to find triplicates or quadruplicates, i.e. three or four occurrences of the same piece of data. There is no built-in feature for that, so we have to find our own way.

In my example 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, this is the formula: =COUNTIF($A$2:$A$28,A2). We’ll use Conditional Formatting with a formula like this.

As we’ve seen in a previous post, Conditional Formatting requires a TRUE or FALSE. 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:

So, let’s put this 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) and click on Conditional Formatting from the Home ribbon and choose New Rule (or shortcut: Alt => H => L => N). Choose “Use a formula to determine which cells to format” and type the 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 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, only the unique values will be highlighted.

Extra: Do you want to learn how to create a search field in your Excel report? Have a look at this post (opens in new window/tab): Create a search field in Excel in 5 minutes

Note: If you use comma as the decimal separator as default (applies to most non-English users) you have to replace the commas in the formulas with semicolons.

More easy tricks with Conditional Formatting:

Author: Audun Danielsen

7 thoughts on “Find Duplicates and Triplicates in Excel

  1. I have a question troubled me long time:

    how to find and delete some special blank row? For example:
    A
    B
    C
    D
    (Blank)
    E
    F
    G
    (Blank)
    H
    (Blank)
    I
    J
    K
    ….

    I want to delete H and either of the blank row up or down.
    Thank you!!

    • If you only want to delete the rows which both have a blank above and below them, you could do like this:
      1. Make sure you have a heading row on top
      2. Type this formula into B2: =AND(A1=””,A3=””)
      It will return TRUE for every row that has a blank above and below it.
      3. Type this formula into C2: =OR(B1=TRUE,B2=TRUE,B3=TRUE)
      It will return a TRUE for all the rows that you want to remove.
      4. Use the Filter to remove the TRUEs from column C. (Data Ribbon => Filter)

  2. I’ve seen similar responses in my search for an efficient way to point out duplicates.

    I deal with somewhat large files (often have to deal with 30k rows x 15-30 columns), and conditional formatting is out of the question as it tends to noticeably bog down Excel’s response time.

    If you’re like me and try to avoid conditional formatting while editing large files, there are a couple things that can be done to the formula mentioned above.

    First and foremost, if your goal is to find all duplicate/triplicate, etc. instances of a selection, changing the Equal (=) condition to a Greater Than (>) 1 condition will include duplicates that show 2 or more times.

    So the formula becomes:
    =COUNTIF($A$2:$A$28,A2)>1

    This little change will be less restrictive when your goal is to find all recurrences of a cell.

    Now, since searching through a column full of TRUE/FALSE statements certainly is an eyesore (short of engaging a “Custom Sort…” which I do, but lets assume that may not be an option), let me introduce another idea: enclosing the COUNTIF() function with an IF() function.

    The IF() function allows you to decide what should show when it is TRUE or FALSE. In short: IF(Condition,”TrueValue”,”FalseValue”). the text values should be enclosed in the quotation marks. The Condition section can be any formula or expression that returns True/False.

    So now we can change our formula to this:
    =IF(COUNTIF($A$2:$A$28,A2)>1,”Duplicate”,””)

    Now the cell will clearly state “Duplicate” (without quotes) when TRUE, or will show a blank cell when FALSE, note the double quotes are still required in the formula above. Now the formula can be copied down the entire column.

    If that’s not noticeable enough, a technique I use often would be to select the column and use “Find…” under “Find & Select”, search for “Duplicate” (without quotes) and click “Find All”. This will bring up a list of cells that were found. From here, you can select all the rows in the list. Select the first one in the list, and either press “Ctrl+A”, or scroll to the bottom of the list, hold “Shift” and click the last one. Now that all the cells are selected, feel free to add your own highlighting from the “Home” Tab, or right-click a selected cell and use either the quick menu or “Format Cells…” -> Fill Tab.

  3. Pingback: Are you Plain Clueless about Issues in Reporting - Help on Excel

  4. Hi All,
    I have a question which trouble me lot. I have column A1 to A50 contains with nationality. I want to list to 5 the most nationality repeated on that column. help please!.

  5. Pingback: Design Reports Effortlessly on Excel Platform

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s