Explanation
In the formula, COUNTIFS counts the number of times each value in a cell appears in its “parent” column. By definition, each value must appear at least once, so when the count > 1, the value must be a duplicate. The references are carefully locked so the formula will return true only when all 3 cells in a row appear more than once in their respective columns.
The helper column option “cheats” by combining all values in a row together in single cell using concatenation. Then COUNTIF simply counts the number of times this concatenated value appears in column D.
Helper column + concatenation
If you don’t mind adding a helper column to your data, you can simplify the conditional formatting formula quite a bit. In a helper column, concatenate values from all columns. For example, add a formula in column E that looks like this:
=B4&C4&D4
Then use the following formula in the conditional formatting rule:
=COUNTIF($E$4:$E$11,$E4)>1
This is a much simpler rule, and you can hide the helper column if you like.
If you have a really large number of columns, you can use the TEXTJOIN function (Excel 2016 365) to perform concatenation using a range:
=TEXTJOIN(",",TRUE,A1:Z1)
You can then use COUNTIF as above.
SUMPRODUCT
If you’re using a version of Excel before 2007, you can use SUMPRODUCT like this:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
Explanation
COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule.
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but B4 is fully relative. So, the rule is evaluated for each cell in the range, with B4 changing and $B$4:$G$11 remaining unchanged.
A variable number of duplicates + named ranges
Instead of hard-coding the number 1 into the formula you can reference a cell to make the number of duplicates variable. You can extend this idea and make the formula easier to read by using named ranges . For example, if you name F2 “dups”, and the range B4:G11 “data”, you can rewrite the formula like so:
=COUNTIF(data,B4)>=dups
You can then change the value in F2 to anything you like and the conditional formatting rule will respond instantly, highlighting cell that contain values greater than or equal to the number you put in the named range “dups”.
