I would like to highlight the maximum numerical value in a particular column.

Take a look at this sheet: https://docs.google.com/spreadsheets/d/107c-9pET6SA1APwtGIJh1Qxi__SOXAHGtaGVGd5Nw6g/edit?usp=sharing

Or this screenshot:

conditional formatting is broken

This formula seems to actually be taking A2 and replacing it with the cell being calculated. In other words, it's only checking this cell and all cells after it to find the max, and no cells before it.

How do I make sheets check all cells?

1 Answer 1


Insert $ between A and 2 in the formula. As CF 'steps through' to test each cell in the range in turn the range is being automatically adjusted. So when for example testing A9 it is checking for the maximum in A9:A1007, which is indeed 14 - so it formats it.

  • Awesome! Do you know the name of this concept, what the dollar sign does etc? Sounds like a fairly fundamental primative for sheets I should be aware of
    – SCdF
    Commented Nov 17, 2016 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.