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:

enter image description here

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.

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

  1. 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.

enter image description here

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.

enter image description here

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

Thanks in advance!

1 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(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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.