Why does Google Docs Conditional Formatting with custom formula not work?

Question

I am attempting to conditionally format a column of numbers to highlight those that are equal to the maximum. For example, if the column has these values 3,5,8,4,8,2,8,7

I want the 8s (in the 3rd, 5th and 7th rows) to be formatted.

I am selecting the row, right-clicking to choose "Conditional Formatting", selecting "is equal to" from the "Format cell is..." dropdown, and typing in "=MAX(A1:A8)" for the custom formula.

The cells that equal the max (8) have the conditional formatting correctly applied, but the final item in the column (the 7) has the conditional formatting incorrectly applied.

Here is a screenshot:

Here is a link to the spreadsheet (non-editable).

I've noticed the same problem with other columns of numbers — the maximum values have the formatting applied (as expected), but some other numbers in the column always have the formatting incorrectly applied (unexpected).

Answer 1

You are using relative addressing so that the 7 in A8 is actually the maximum of A8:A15.

Use the $ absolute anchor to lock the rows with =MAX(a$1:a$8) or if the conditional format range extends laterally, =MAX($a$1:$a$8).