Highlight Unique Duplicate Rows Across Columns

In the below table few rows/students are repeating, the challenge is... need to highlight unique duplicate student across columns. Below we go step by step

We need to use two functions 1. CONCATENATE to Combine data of all columns and 2. COUNTIF to identify duplicate values. Below is the table with added two columns with formulas & Results

Here one can use simple conditional formatting in the COMBINE column to see duplicate values similar to below, which gives the answer of duplicate values. But could not highlight all columns and need to have an additional COMBINE column to see duplicates

Challenge is without showing additional column, need to highlight duplicates & across columns. Here we need to combine both CONCATENATE & COUNTIF functions in Conditional formatting. Let's go step by step

1. Select data range - don't select titles
2. Go to Conditional Formatting - New rule

3. Select 'Use a Formula to determine which cells to format'

4. Write COUNTIF formula as discussed above / as shown in the below picture.

5. After the Countif function - click on format and select the desired color, then click OK

6. Here is the result! Madavan & Vishnu are unique duplicate values, the same has been highlighted across columns

I hope this helps...thanks for reading.

Please do share & join Excel Pros!