Share via

How to highlight Duplicate and Trpl Duplicate value in Excel

Aziz Husain 196 Reputation points
2022-01-10T06:50:03.86+00:00

Hi All,

I have an Excel sheet with some Duplicate values and some triple duplicate values. I want to highlight duplicate values in different color and triple duplicate values in different color. How i can do that?

I am able to highlight all duplicate value but not able to highlight triple duplicate with different color.

images is attached for reference blue color is repeat three times, yellow color is repeat two times. what i want is whichever value repeat more than 2 times should be highlight with different color.

thanks
Aziz163479-capture.png

Microsoft 365 and Office Install, redeem, activate For business Windows
Microsoft 365 and Office Excel For business Windows

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff
    2022-01-10T07:39:15.71+00:00

    Hi @Aziz Husain ,
    I created a sample about your problem, and you can try these formulas with conditional formatting:

    Duplicate : 

    =COUNTIF($A$3:$H$12,A3)=2

    Trpl Duplicate: 

    =COUNTIF($A$3:$H$12,A3)=3

    163562-23.png

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.