Google-sheets – Highlighting the maximal value of a column with conditional formatting

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".

Generally: the way to refer to "this" cell is to enter notation for the upper-left corner of the range being formatted. For example, if the range is C1:C, then C1 means "this cell".

For example, formatting C1:C with custom formula

=C1 < today() - 14

will format all cells in C with dates two weeks in the past.

If the range you want to format begins with row 8, and you think you may be inserting rows above that (thus shifting the range), then this formula can be used:

=and(C1 < today() - 14, row(C1) >= 8)

The formatting will apply only to rows starting with 8, but the range being the entire column, the formula will handle insertion of rows above row 8.