Google Sheets – Why Can’t Refer to Another Cell with Value Comparator

conditional formattinggoogle sheets

This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already.

From what I could gather, if I want to apply a conditional formatting based on the value of another cell, I have to use a custom formula. However, in the list, there are a couple options that would seem to be much more relevant: "Greater than (or equal to)", "Lesser than (or equal to)", "Is (not) equal to", "Is (not) between". But I'm starting to think that they are not so useful.

Here I have a column of numbers ranging from 10 to 30; I want to highlight them in green if they're greater than or equal to a number (20) set in another cell. From a few other questions here and on SO, it would seem that I should use a custom formula as something like =K7>=K4, but instead I still wanted to try the options in the list that seemed more intuitive.

enter image description here

As you can see though, it does some weird things, where it consider 11 to be under 20 (which is true AFAIK), but everything else above 11 is either equal or greater than 20.

My guess is that putting =K4 in the "Value or formula" field didn't quite link to the actual K4 cell, but I can't figure out why it does what it does here.

If I put "20" in the field, it highlights the right cells (but it's not dynamic anymore).

Edit: putting =K$4 works like a charm. Quite literally, in this case.

Best Answer

You identified and solved the issue correctly: =K4 is a relative reference, so when it is applied to the range K7:K26, the following happens:

  • K7 (being the upper left corner of the range) gets compared to K4, as stated in the formula
  • K8 (one cell down) gets compared to K5 (one cell down)
  • K9 (two cells down) gets compared to K6 (two cell down), and so on.

If this sort of comparison is not what you want, then the absolute reference =K$4 should be used.

Note that the above logic applies equally well to custom formula formatting, and to "greater than", "less than", etc.

Related Topic