Explanation

This approach uses SUMPRODUCT to count the occurrence of every value in the table, one row at a time. Only when the same value appears in the same location in all three rows is a count generated. For cell B4, the formula is solved like this:

=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
=SUMPRODUCT(({1,1,1,1})*({1,0,1,0})*({1,0,1,0}))>1
=SUMPRODUCT({1,0,1,0})>1
=2>1
=TRUE

Note that row references are fully absolute , while cell references are mixed , with only the row locked.

With a helper row

If you don’t mind adding a helper row to your data, you can simplify the conditional formatting formula quite a bit. In a helper row, concatenate all values in the column. Then you can use COUNTIF on that one row to count values that appear more than once, and use the result to trigger conditional formatting in the entire column.

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