Excel Formula – Match Duplicate and Highlight with different color

Posted on


enter image description here

A to D column with title List-1,List-2,List-3,List-4 and “Main List” A12 to A106 (original “Main List” DATA I did cut/paste here for color showing purpose)

There are random name showing in A2 to D10 which have 3 or 4 times duplicate match within A2:D10

I want to Highlight Duplicate match with different color like
if same name match 4 times then it should be HIGHLIGHT with GREEN background,

if 3 times match then should be highlight with BLUE background and

if 2 times match highlight with Yellow background.

SAMPLE image attached.

Please help me with excel formula or guide me if any possible way.


Its done with below steps.

Select A2 to D10 -> Conditional Formula -> use below formula (one by one) -> Color

=COUNTIF($A$2:$D$10,A2)=4 (Blue)

=COUNTIF($A$2:$D$10,A2)=3 (Yellow)

=COUNTIF($A$2:$D$10,A2)=2 (Grey)

Leave a Reply

Your email address will not be published.