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.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .