Excel: How does relative and absolute reference determine which rows are formatted?

Question

In the image below, why are all K column values formatted and only the correct J column values?

I've read about absolute vs relative reference, don't see why the behavoir is different on my two tables. The image is an edit of an example from Microsoft Excel 365 Bible

Answer 1

Actually, the explanation is slightly incorrect.

In the second rule, in which all works as expected, the $E part means that when column D's cells are being considered by Excel, it compares the column E value for the row to the fully absolute $B$3 as expected, and then when considering the column E cells (since the Applies to range is both columns), it still compares the column E value for the row to the fully absolute $B$3.

The difference between the two, that of the first rule's missing $ before the "K", means that with the Applies to range being two columns again, the first comparison is done as expected: it uses the column K value comparing it to the fully absolute $H$3, just as the left table does.

But then it goes awry, as when it moves right one column to considering column K's cells for coloring, it ALSO moves one column right for the cell to compare to the fully absolute $H$3: when coloring column K, it no longer compares the column K value for the row to $H$3, but rather compares one column to the right, column L, to $H$3. So the comparison for column K is =L3<$H$3 and since all the cells in column L are unused, therefore having a value of 0, they all succeed in the test and all of column K gets colored.

It is that change of what to compare as one moves one column right, due to its address being relative, and then the resulting test column, column L, being empty, that makes the test produce unfortunate results.

Note that if column L were populated, you might have gotten rather different results and in some cases, perhaps many, the problem might not have caught your eye. Probably would have here, of course, since the coloring likely would not have matched column J, but in other circumstances...

The lack of the absolute reference for the comparison cells in the first rule is what caused the problem, no question or quibble with that. It's just the explanation given did not really match what's happening. That's important because, especially with Conditional Formatting (especially!) but with most other things as well, small subtle things are often the cause of difficulties in Excel and knowing exactly what each little thing does, not generally but exactly, is often key to figuring them out.

In this case, pattern matching would have also given one a leg up on figuring it out. One should always remember that though the detail is super important, noticing errors in the detail can benefit from any and all of your general problem-solving skills. Excel is the same, and while you do need the knowledge to know what the reason behind problems is, you don't always need knowledge to fix a thing. To be sure you fixed it, yes, but not just to fix it. For instance, notice the difference in the comparisons, that lack of a leading $ and you could edit it to have one, then see it all work. You'd still want to know why to be SURE it is a real fix, but it actually would fix it and you could move on. Learn why later, or in this case, just be happy you found a typo and fixed it.

Answer 2

First formula K3<$H$3: K3 is relative to column and relative to row. What it means for your range ($J$3:$K$14) is that the result applies to the column and the row, in your case all cells. When the condition is true, apply the conditional formatting to the whole range. That's why column K's cells are also highlighted.

Second formula $E3<$B$3: $E3 is absolute to column and relative to row. It means is that the result applies only to the rows not the column. When the condition is true, apply the conditional formatting to only the rows that meet the condition. That's why only some rows are highlighted.