Comments on: How to highlight duplicate cells and rows in Excel

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 page 2. Total comments: 86

  1. how to highlight in between values froe example..
    on e cell contains 123456789 and other cell contains 34567
    how to highlight mid value 34567. hope my question is clear

    1. Hi,
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?

  2. Hi
    I need some help with highlighting cells according to single, duplicate and triplicate occurrences. For example i have a sheet with client IDs entered in a sheet from B3:N368. All IDs occur three times in the sheet against certain dates and times. I was all first entries of the IDs to be highlighted in PINK, all second occurrences in YELLOW and all third occurrences in GREEN. Only the above three colors are required. So, when i scroll down the sheet and see an ID with GREEN cell filling, i know this is the third entry for this ID, and when i scroll up is should find the second occurrence of this ID highlighted in YELLOW. The reason for this requirement is when i look at an ID anywhere on the sheet, i must know whether this is the first, second or third occurrence in the sheet.
    Thanks.

    2. Hi, did you receive a solution for your question? I am trying to figure out the same thing. Thank you

  3. I NEED A SPREADSHEET TO HIGHLIGHT CELL TEXT RED ONCE I HAVE "UL" IN 5 OR MORE CELLS IN A SINGLE ROW. HOW DO I DO THIS?

  4. Hi!
    Thanks for the info but I'm having trouble finding unique duplicates.
    With this formula if I have for example:
    Cell 1. labels
    Cell 2. blue labels
    It will mark the two as duplicates whereas the content is partially duplicated.

    How can I make sure only 100% content is marked as duplicated?

    Thank you!

  5. How can I highlight only the second column, but have the formula apply to both columns (so the duplicate from the first column gets highlighted in the second column only).

  6. Hi There
    I have a little different story. I hope you will be able to help with this.
    In my spreadsheet, there is a button to transfer data from one sheet (sheet1) to another (sheet2). I want auto highlight duplicates when transfer data from sheet1 to sheet2.
    Happy to provide more information if needed and I would greatly appreciate if you can help me with this.
    Many thanks
    Vasanthi

  7. I Need a conditional formation formula for highlight duplicate words in sentence contain in single cell.

    E.g : aaa bbb ccc ddd eee ccc aaa
    Highlight duplicate i.e aaa ccc any color font.

  8. hi, how can i highlight this duplicate entries

    F1P01
    A1P02
    B1P03
    F1P01,F1P02,F1P03

    THNX

  9. hello,
    can i know how to highlight the first item for each number?
    Example:
    1 - highlight
    1
    2 - highlight
    2
    3 - highlight
    3

  10. Hi,

    This Ablebits option of duplicate remover is very useful for everyone. Thanks for the updating and please give the more useful option of Microsoft excel.

    1. Hi Anita,
      Thank you very much for your feedback.

      Please note that we have not only Duplicate Remover, but also many other add-ins to ease your work in Excel. If you are interested, please have a look at our Ultimate Suite that contains all our tools for Excel (70+ add-ins). Feel free to install the fully functional 14-day trial version of this product to check how it works. Here is the direct download link for you.

      Hope you'll enjoy using our software :)

  11. I am looking to highlight rows based on duplicate cells in a single row in an excel spreadsheet. In other words, if O7=R7, highlight the entire row. Ultimately, I am looking to sort these rows and delete them from my report. I may have missed the answer above...any ideas?

  12. Hi,

    I have list of duplicate IMEI Numbers, I want to find the difference of date: which means 2/8/2018 one IMEI has logged, Same IMEI number got logged on 27/9/2018. Now I want to find the difference between No of days. Please help me. What is the formula?

  13. How should we find duplicate data in multiple sheets of excel in different columns and different worksheets in the same time althought i tried from condational formatting unfortunately I unable need your cooperation

  14. Hi Svetlana,

    This has been really helpful and is in great detail,

    I wonder if you could help me, I have a sheet with Column B containing 500 names (some are duplicates which i have managed to highlight) and i need to keep them highlighted but also highlight the corresponding cell in Column F on the same sheet,

    Also i would like the highlighted cell in Column F to say "NO"

    Is this possible or is this too much info for excel?

    Thank you in advance if you can help

  15. Hi Svetlana,

    Happy to find your blog! Thank you for sharing with the folks who are passionate about Excel - me included! - your expertise!

  16. Date Time Pick/Drop Trip Sheet
    01.12.17 04:00 DROP 3622SH0400012
    01.12.17 04:00 DROP 3622SH0400011

    06.12.17 23:00 DROP 3627SH0400075
    06.12.17 23:00 DROP 3627SH0400112

    HOW TO FIND THE OUT BY DUPLICATE TIME IN A ROW IN A DAY WITH DIFFERENT TRIPS and vechile

  17. Hi,
    When using highlighting duplicate feature or duplicate removing feature on cells with text format, I encounter problem of distinguishing between values in cells with text format. For example, both of these features consider text in cell A2 (0123456)the same as text in cell A7 (123456). Therefore, it will highlight both of them. Is there a way to solve this problem? Thank you.

    1. Hello,

      Please create a custom Conditional Formatting rule for range A1:A7 using this formula:

      =SUMPRODUCT(--($A$2:$A$7=$A2))>1

      Hope it will help you.

  18. I used your formula: =COUNTIF($A$2:$A2,$A2)>1

    in order to highlight duplicates without 1st occurrences.

    However, for some reason the formula is highlighting the 1st occurrence of the value instead of the second.

    Do you have a solution?

  19. USED DUPLICATED VALUE EXCEL SHEET 1 AND SHEET 2 SAME NO.

  20. Hi, I'm trying to use your "highlight all duplicates except the first occurrence" formula.

    However, the formatting is working in reverse. It's formatting all instances except the LAST, not the first.

    I've tried messing around with it, with no luck.

  21. Hi,
    could someone help me?
    I have a column with dates just dates that are entered in to it. I would like to know what formula to use to color the cells background green if four or more of the same dates appear. Is there a way to do this?

  22. Hi,
    Can someone help me....
    I have two columns Empid and Amount.

    if both the columns Empid and Amount are duplicate I need remove the 1 st occurrence duplicate amount.
    Else
    if Empid is duplicate and Amount is not duplicate then I want to add the amount

    eg
    empid Amount Result
    12 100 100
    12 100
    13 120 250
    13 130

    Thanks in advance
    Mat

    1. Hi Mathews,
      Not sure if you will ever check back here but for your question, I'd use a formula and not a highlight (or any other conditional formatting).
      Imagine your example in Excel, 3 columns, 5 rows.
      The formula in C2 would be something like:
      =IF($A2=$A1,IF($B2=$B1,"delete this row",$A2+$B2),$A2+$B2)

      Now you can just select cell C2 and drag it down.
      Later use a search command and search for all instances of "delete this row" (don't forget to set it to values or else it will just select wherever you used that formula).

      1. Oh, it seems I misread a bit of your question.
        Your example seems wrong which also confused me (f.e. you mentioned deleting the first double duplicate occurrence but in your example you added the amount behind the first row)

        I suggest doing this in 2 parts, first, delete all the rows that you don't want, you could do that with this formula in C2 (and dragging it down):
        =IF(A2=A3,IF(B2=B3,"delete this row",""),"")

        I removed the rest of my example to just link the sheet I made for you: https://docs.google.com/spreadsheets/d/1AfNzwLHdJa4cX6Vu1Tdm6JSC-MiT51o60qGel9OwdVs/

  23. How do you filter out the last record in a duplicate occurrence?

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

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

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

  25. Thanks very useful tips

  26. THANK YOU

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

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

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

          1. :< This just gives a popup that Excel does not recognize this as a proper forumla for me...

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

  28. very helpful content, thank you mam, you are doing very excellent job.
    i've a problem, plz help to solve this
    i've a data in 2 different sheets,i want to highlight the repeated text of a column of sheet2 in a column of sheet1.

  29. How to use conditional formatting to highlight duplicates (either text or number) with different colors

  30. Dear Madam,

    I would like to ask that I have data in Column and i do not want user to enter duplicate value in particular column not just typing method but also cant use cut copy paste command.

  31. helpfull thank,s you such a great

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)