Highlighting the highest/lowest cell value in a row or colum

[all4sport]

Is there any way to have excel automatically highlight the highest or lowest number in a column. I don't wish for it to sort or move anything around. I just have weekly data and I want to automatically see the highs and lows.

[Mark W.]

Use Conditional Formatting with a formula such as...

=OR(A2=MAX(A:A),A2=MIN(A:A))



[ This Message was edited by: Mark W. on 2002-03-01 12:01 ]

[Aladin Akyurek]

On 2002-03-01 11:50, all4sport wrote:
Is there any way to have excel automatically highlight the highest or lowest number in a column. I don't wish for it to sort or move anything around. I just have weekly data and I want to automatically see the highs and lows.

Select the relevant range in a column (say, A1:A10), activate Format|Conditional Formatting, choose 'Formula Is' for Condition 1, enter what follows in the formula box:

=A1=MAX($A$1:$A$10)

activate Format,and choose a color on the Patterns tab.

[all4sport]

Thanks for the help...I still have a question though. I can get this to work now if all my rows are together, ex. A1:A10, but my problem is that I need this to work for A1:A10, A14:A24, A29:A37...etc....I want it to highlight the highest value amongst those groups....does this make sense.

[Mark W.]

No problem. Setup a separate conditional format with its own formula for each range....

=OR(A1=MAX($A$1:$A$10),A1=MIN($A$1:$A$10))
=OR(A14=MAX($A$14:$A$24),A14=MIN($A$14:$A$24))
=OR(A29=MAX($A$29:$A$37),A29=MIN($A$29:$A$37))

...etc...

[all4sport]

So will that let me choose one high cell out of those 3 groups?

On 2002-03-01 15:25, all4sport wrote:
So will that let me choose one high cell out of those 3 groups?

Yes, but your original request was to identify both the high and the low which what these formulas do when used with a Conditional Format. You'd highlight A1:A14 and setup a Conditional Format using the 1st formula above, and then you'd repeat these steps in succession for each cell range of interest.

[Mark W.]

On 2002-03-01 15:25, all4sport wrote:
So will that let me choose one high cell out of those 3 groups?

Yes, but your original request was to identify both the high and the low which what these formulas do when used with a Conditional Format. You'd highlight A1:A14 and setup a Conditional Format using the 1st formula above, and then you'd repeat these steps in succession for each cell range of interest.

[all4sport]

This is my goal.

This is an example of the data.

A1
A2
A3

A7
A8
A9

A14
A15
A16

What I want is to highlight the highest value out these 9 cells. What I'm having trouble with is the fact that they aren't all in succession of each other. Thanks in advance for you patience and help. If I can have it highlight the lowest at the same time is an added bonus.

[Aladin Akyurek]

On 2002-03-01 19:57, all4sport wrote:
This is my goal.

This is an example of the data.

A1
A2
A3

A7
A8
A9

A14
A15
A16

What I want is to highlight the highest value out these 9 cells. What I'm having trouble with is the fact that they aren't all in succession of each other. Thanks in advance for you patience and help. If I can have it highlight the lowest at the same time is an added bonus.

Use

=A1=MAX($A$1:$A$3,$A$7:$A$9,$A$14:$A$16)

for Condition 1 and

=A1=MIN($A$1:$A$3,$A$7:$A$9,$A$14:$A$16)

for Condition 2.