I have a table containing text. I'd like to use conditional formatting to highlight, in each row, those cells which differ from the cell immediately to the left. I'd like to apply this to a particular block so the first column isn't all flagged (as being different from the row headers).

I can see how to highlight all the cells that differ from fixed reference text and to a fixed reference cell, but even in a test situation with just two cells changing the reference text from "=$B$9" to "=B9" stops it highlighting.

Can anyone give a pointer to how this can be achieved, ideally without having to use VBA?

  • 1
    Please see Use a formula to determine which cells to format: ($A9<>B9)`, make sure that the format works, and propagate the format to the rest of the cells, which should signal a text mismatch.
    – JohnSUN
    Feb 21, 2022 at 14:09
  • Thanks. The key here was the cursor selection. One of my first attempts was to select the two cells I wanted to compare (eg C4 and D4) and set the formula to "=D4<>C4" then copy D4's format to the rest of the table. Didn't work. The same process starting off with only D4 selected works. Same formula. I can't use anything with $ signs because that locks the comparison to a specific column (or row) Feb 22, 2022 at 8:55

2 Answers 2


What you are looking to achieve is relative conditional formatting (CF). By default Excel will insert references in named ranges and CF as absolute references e.g. $A$2.

The key here is to position your cursor correctly BEFORE defining the CF formula. E.g. put your cursor in cell C2, now define a CF by formula


Without $$ references. This CF rule is now defined relative to C2, so if you were to copy the cell or the format (with format painter) to say say D4, its CF rule will auto update to =D4<>C4. Assuming column A contains your headers, copying this CF rule into B would yield incorrect results. Copying it to A would yield an #REF error.


Try this method, not select the all Row, start from the second cell you need to highlight, for example I want to highlight the Row14 and select from B14 to N14: enter image description here

I entered the formula in Conditional Formatting rule:


enter image description here

You must log in to answer this question.

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