Conditional Formatting to Detect Re-Occurrences

While working on Excel data, most of the times, we want to know if the duplicates are automatically highlighted so that the viewer can easily view the duplicate item & start working on it. In this article, we will use COUNTIFS formula to highlight the exact matching row.

Question): I have 3 columns data; I want to highlight the row which is exactly matching & is occurring second time. If an entry is made and all three items are the same, then I want it to be highlighted.

Following is the picture of data we have. The row# 10 is exactly matching to row# 2

To highlight the duplicate row, we need to follow the below steps:

Select range A2:C10
Press ALT + O + D to launch Conditional Formatting Rule Manager

Click on New Rule
Select “Use a formula to determine which cells to format”

In Format values; the formula is
=COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2,$C$1:$C1,$C2)

Click on Format & select any color from Fill tab

Click on OK button twice
We will get to see the duplicate row.

To verify the above formula is perfectly working or not, let us change the Age in cell B8 from 27 to 25
Now row# 3 & 8 are both identical & conditional formatting should highlight row# 8

In this way, we can highlight the exactly matching duplicate rows in the provided data.

Download - Conditional Formatting to Detect Re-occurences - xlsx

SVR Krishna
August 8, 2019 at 12:07 pm

HI
i want to override the different color filled in a row if i have other color. Like A2, A3, A4 have three different colors if i conditionally formated A1 with other color then A2, A3, A4 should not be filled with colour can this be possible .
Thanks

Reply ↓