Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.


  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Guest

    Default

    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.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •