Excel - Highlight Unique Values Across a Row, Apply to Multiple Rows

Question

What I am trying to achieve is create a bare bones diff tool using Excel. I have multiple files, each containing one column of data, and I would like to paste each file into a single column in Excel. Then, across each row, if there is a unique value (i.e., a diff), I want Excel to highlight that cell.

Essentially, a sample Excel file should look like this:

I've tried Conditional Formatting to highlight unique values across rows, but have been unsuccessful so far. I almost get it right, but somehow Excel seem to highlight then in a way a little different to what I need.

This is what I did, step by step:

1. Highlight first row.

2. Add a new formatting rule to highlight unique values.

3. It correctly highlights the first row the way I want.

4. Highlight the first row again, and click 'Format Painter'.

5. Then drag the cursor across rows 2 through 5 to highlight all the rows with data. Note that I drag over the row headers, not actual rows.

Now, as you can see, it highlighted Row 4 correctly, highlighting the value 'D', as it is unique across that row. However, it did not highlight value 'A' in Row 2 and value 'B' in Row 5 although those too are unique to their rows.

Furthermore, now if I change the last value in Row 5 to 'S', it highlights that cell as well. What I can gather from this is that the conditional formatting is applied to all the cells in the range I selected as a whole instead of row-by-row basis.

What am I doing wrong? How do I get the formatting to work the way I want?

Thanks in advance!

Answer 1

Select all your Data,

• Click on Conditional Formatting

• Use a formula to determine which cells to format and in the box write:

=COUNTIF($A1:$C1,A1)=1

or

=COUNTIF(RC1:RC3,RC)=1 (with R1C1 references)

and format as you like
The formula will count the occurrence of each value and Highlight the unique ones (countif()=1) only.