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:
- Highlight first row.
- Add a new formatting rule to highlight unique values.
- It correctly highlights the first row the way I want.
- Highlight the first row again, and click 'Format Painter'.
- 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!