3

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

Example of column formatting

2 Answers 2

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.

2
  • You've just opened a whole can of worms that afflicts the entire industry (or quite possibly our entire species) as a whole when it comes to the culture of technical support troubleshooting paradigms and subjectivity of what constitutes a "resolution", on both sides of the table.
    – Arctiic
    Nov 22, 2022 at 1:12
  • Thank you. If I copy Column K values, to column L, K is colored correctly and L is all red. So in K3<$H$3, uses the column to the right to color the column on the left. Your paragraph that starts with "But then goes awry" answers my question.
    – OrigamiEye
    Nov 22, 2022 at 9:32
4

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.

1
  • 3
    It may be worth adding that OP probably wants the first formula to be =$K3<$H$3. You explained why it didn't work but not what it should be. Nov 21, 2022 at 13:20

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.