Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditional formatting for Top 5 numbers (Excel 2003)

    Can I use conditional formatting to shade the top 5 values of a column bright green or does it have to be a macro? thanks for the help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting for Top 5 numbers (Excel 2003)

    Let's say that the values are in A1:A100.
    Select this range.
    Select Format | Conditional Formatting...
    The first dropdown box should say "Cell Value Is" (the default setting).
    The second dropdown box should say "greater than or equal to".
    Enter the following formula in the box next to it:

    =LARGE($A$1:$A$100,5)

    Click Format... and specify the formatting that you want to apply to the top 5.
    Click OK twice.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting for Top 5 numbers (Excel 2003)

    ah, yes.. thanks very much

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting for Top 5 numbers (Exce

    Hi There

    You could do it with a hidden column, let's assume you have the numbers in cells A1:A7. In Cell B1 type =RANK(A1,$A$1:$A$7) and copy down to B7

    Highlight A1:A7 and use the following formula is =B1<=5, choose your formatting.

    I have attached an example of above
    Jerry

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting for Top 5 numbers (Exce

    Would this have worked? If the column contained duplicate numbers, would one way be preferable to another?
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Images Attached Images
    - Ricky

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting for Top 5 numbers (Exce

    That would have worked too. RANK and LARGE treat duplicate values the same way - if there are two cells with rank 5, both will be colored.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting for Top 5 numbers (Exce

    What if I don't want to include 100% in the test. I tried =if(B5="100%",b5,large(b6:b21))

    but it didn't work

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting for Top 5 numbers (Exce

    Let's say again that the range is A1:A100.
    Select A1:A100, A1 is the active cell within the selection.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the following formula in the box next to it:

    =AND(A1>=LARGE($A$1:$A$100,5),A1<>100%))

    Click Format... to specify the desired formatting.

Posting Permissions

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