Conditional formatting over wide area of cells, referencing other cells

Question

As data validation does not seem to be able to reference cells for numerical conditions (instead only being able to hardcode numbers); I have fallen back on conditional formatting. However, it does not seem to be possible to have more than any individual column affected by conditional formatting rule formulae

So while it is perfectly possible to do this:

Actually replicating this so that K3:K125 is compared with K1 does not seem to be possible. I would even settle for hardcoding all of the references like so:

However, this does not work.

Answer 1

Both data validation and conditional formatting can reference the values in other cells.

For example, if I require all values in column A to be greater than the value in cell C1, I would use Data Validation with custom formula =A1>C$1. The logic is as follows:

• Validation rules (and conditional formatting rules) are entered as they should apply to the upper left corner of the range. From there the reference will be automatically mapped if needed: e.g., the rule =A1>C1 would compare A2 to C2, and so on.

• The formula =X>Y returns True if X is greater than Y and False otherwise. Similarly, one can use expressions like =X>=Y or =X=Y. The initial = is just the symbol indicating we have a formula to evaluate.

---

With conditional formatting, you can use the same custom formula as above. Or, pick "greater than" from the list of criteria and enter =C$1 as the value to compare to. Not C$1, because C$1 is simply text "letter C, dollar sign, digit 1".