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

all4sport

New Member
Joined
Feb 28, 2002
Messages
16
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top