6

I have a simple column of numbers here (A2 to A10) in which I want to highlight the maximum value in that column. Instinctively I went to conditional formatting and learnt that the formula for it was =A2=MAX($A$2:$A$10) applied to A2:A10. I'm glad it works hassle-free, but I would not have thought to put the A2 in the front and have $'s, why are those necessary?

I would have been banging my head against a wall with just =MAX(A2:A10)

5

You are right: it can be simpler than that. Pick "Format cells if... Is equal to" and put =MAX(A$2:A$10), and you'll have the same result.

formatting

Why dollar signs? Because without them, the references are relative. The formula that you enter is assumed to be designed for the upper left corner of the range being formatted; for all other cells in the range, it will be interpreted using relative references. That is, if you compare A2 to =MAX(A2:A10), then A3 will be compared to =MAX(A3:A11), A4 to =MAX(A4:A12), and so on. This is not what you wanted.

(You will appreciate the relative nature of references when you decide to format cells in A if their values are equal / less than /greater than the corresponding values in B.)

There is no reason to put $ before A since the formatting is only applied within column A. Both $A and A mean the same column in this case.


Why would someone suggest =A2=MAX($A$2:$A$10)? Because they are used to conditional formatting using custom formulas. A custom formula needs to return True or False. So, the equality A2=MAX($A$2:$A$10) is checked, and the result is returned as True and False.

| improve this answer | |
  • precise answer as usual, thanks:) – timoseewho Nov 12 '15 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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