Conditional format for Max value

[handerson0603]

I know this should be easy. I want to highlight the highest value in a column of numbers, but when I use the obvious [=MAX(D:D)] in a conditional format formula, it highlights the entire column. What am I doing wrong?

[XOR LX]

Hi,

Amend it to e.g.:

=D1=MAX($D:$D)

assuming D1 is the first in your range.

Regards

[handerson0603]

Thanks, XOR! That fixed it.

Can you explain the logic of your formula to me? I'm trying to see how I could apply that to other problems, and it's escaping me.

[XOR LX]

Sure.

Basically, and particularly if using a formula-based approach, CF will apply to those cases where a Boolean TRUE is returned.

Let's say for example that the maximum value in column D is 1000.

In your original construction, =MAX(D:D) evaluates to =1000 (for all cells), but this is not sufficient for Excel since, even when it applies it to the actual cell in that range which contains this maximum value, it has not returned a TRUE/FALSE value, which it needs.

Whereas in my construction, =D1=MAX($D:$D), when applied to all the cells in the range, will produce a series of tests:

=D1=MAX($D:$D)
=D2=MAX($D:$D)
=D3=MAX($D:$D)

etc., all of which, when evaluated, will produce results such as (taking some random numbers in those cells):

=33=1000
=1000=1000
=249=1000

etc., which, in turn, evaluate to:

FALSE
TRUE
FALSE

The important thing to remember is to make sure that the formula you are using for which you wish to have the Conditional Formatting applied is one which evaluates to either TRUE or FALSE.

Also, I notice that you had not fixed your range reference (D:D, not $D:$D). Although you are using a whole column as the range here, in general you need to consider the potentially negative consequences of not fixing the range - although you don't 'see' the Conditional Formatting formulas, when entered with relative references they behave just as worksheet formulas would when copied down.

Hope that helps.

Regards

[handerson0603]

Very helpful explanation. Thanks for taking the time.

[XOR LX]

You're welcome.