+ Reply to Thread
Results 1 to 8 of 8

conditional formatting with percentage color scale

  1. #1
    Registered User
    Join Date
    12-16-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    conditional formatting with percentage color scale

    I have a sheet that calculated percentages. I want to set up a conditional format color scale where 0% is green, 50% is yellow and 100% is red.

    However, every time I use the three-color scale and percents, it applies to color scale based on the range of values present, not the full 0-100 range. So for example, if I have three cells with 5% 15% and 30%, then 5% will be green, 15% will be yellow and 25% will be red. If it were working in the way I would like, they would all be various shades of green and yellow green. I don't want to compare the values to each other, I want them compared to the range 1%-100%. Any ideas?

    Also, can you do a color scale that changes the font color and not the cell color?


    Thanks!
    Last edited by timharper; 12-16-2010 at 12:35 PM.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: conditional formatting with percentage color scale

    Hi,

    Set the minimum midpoint and maximum type to number, and there relative values to 0, 0.5 and 1.

    I don't think you can alter the colour scale of the font.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    12-16-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: conditional formatting with percentage color scale

    I've tried that. I set the minimum to 0, the max to 100 and the midpoint to 50 (all with percent selected in the drop down box). I tried to do the same thing with values of 0, .5 and 1. It still only applies the color gradient based on the range of values, not a set range.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: conditional formatting with percentage color scale

    Workaround: You could insert two rows and enter 0 and 100, include them in your range and then hide them.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: conditional formatting with percentage color scale

    I've tried that. I set the minimum to 0, the max to 100 and the midpoint to 50 (all with percent selected in the drop down box). I tried to do the same thing with values of 0, .5 and 1. It still only applies the color gradient based on the range of values, not a set range.
    You need number selected in the dropdown box, and values of 0, .5 and 1.

    The values you enter on the worksheet are percentages? ie, you're entering a number followed by a % sign?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-16-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: conditional formatting with percentage color scale

    Quote Originally Posted by sweep View Post
    You need number selected in the dropdown box, and values of 0, .5 and 1.

    The values you enter on the worksheet are percentages? ie, you're entering a number followed by a % sign?
    I have them formatted as percentages. The actual cell contains a formula that calculates the value.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: conditional formatting with percentage color scale

    Sweep's method works for me.

  8. #8
    Registered User
    Join Date
    12-16-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: conditional formatting with percentage color scale

    That did the trick. Thanks very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1