Click "Use a formula to determine what cells to format"
Copy and paste the above conditional formatting formula to "Format values where this formula is true:"
Click Format button
Select a formatting you like. For example, cells filled with yellow.
Explaining CF formula in cell B2
There are two parts in this formula, one part determines if a value is a duplicate in the first column. The second part of the formula determines if a value is a duplicate in the remaining columns.
The reason the formula looks like this is because of the order of how Excel calculates cells.
IF(logical_expression, first_part, second_part)
Step 1 - Check if first column is being evaluated
The COLUMNS function counts columns in a cell reference. $A$1:A1 is an expanding cell reference, it grows because A1 is a relative cell reference that changes between cells.
1=1 and returns TRUE.
Step 2 - Count cells based on a condition
The IF function changes the calculation based on the logical expression in the first argument. The second argument is calculated if the logical expression returns TRUE, the third argument is calculated if the logical expression returns FALSE.
The COUNTIF function makes sure that duplicates are not highlighted, only the first instance of each value. However this works only in the first column, the remaining columns need a different formula in order to do correct calculations.