Comments on: Excel Icon Sets conditional formatting: inbuilt and custom

Assuming you already know the basics of Excel conditional formatting, let's move on and see what options you have with regard to icon sets and how you can leverage them in your projects. Continue reading →

Comments page 2. Total comments: 80

  1. Is there truly no access to "color scales" (or an equivalent function under a different name) in Excel 2008? I no longer have 2007, and I'd hate to have to buy a new computer just to get space to add Office 2013 (and don't know if anyone will still sell me 2007 or 2010, and not sure 2011 is quite compatible).

  2. Hi

    How do I format a certain cell to change to a chosen colour depending on the wording in another cell?

    Is this possible? For instance, if cell A says YES then cell B turns green but if cell A says NO then cell B turns red?

    Regards

  3. Hi
    How can I show Icons for the following:

    Between -1 and +1 Green Light
    Less than -1 Red light
    More than +1 Red Light

    Many thanks

    Sishy

    1. Hello Sishy

      Select your data and create an Icon Sets rule for three icons.
      You can see the rule settings on this screenshot:

      To choose the icons, please click on a small arrow to the right of the icon.

  4. I've seen two questions above related to using negative percentages, but have not seen a response. I want to use an up arrow icon for percentage increases above 2%, a right arrow between 2% and negative 2%, and a down arrow for decreases greater than negative 2%. So, I have a data set that compares a percentage from last year and a percentage this year with a resultant percentage difference -- positive difference if if it went from say, 61% to 63% and a negative difference if it went from 63% to 61%. The change, then, is 2% and -2% respectively. Using these figures in the conditional formatting, I get an error message from Excel saying that "one or more of the values is not a valid percentage." Does Excel not allow a negative percentage?

    1. Hi Ken,
      I have the same problem. did you ever get an answer to this?
      Cheers
      Rodney

  5. Good Morning,
    I have a smiliar question as Vicki, from 11/14, but rather than a score value, I'd like for the text in the column to be read and an icon to display in another column.

    Can the be done or must I use numbers?
    Thank you...

    1. Hello Michelle,

      Yes, it is possible. If you describe your rules in more detail, i.e. what text corresponds with each icon, I think we'll be able to help you with formulas.

  6. Dear Svetlana,

    Would this be possible: I have a column with data bars representing a % of YES answers to my questionnaire, but would for example like to show ratio in YES answers as given Female or Male. Now i could do 2 colums, but it would be better if it could be done within the same data bar. I seems not possible, but just to be sure, I wanted to ask.

    thanks!

    1. Consider using Sparklines

      Regards,

      Sherin

      1. Hi Loes, perhaps you could try two columns side by side or M & F, format left one for right indent & right for left indent and hit each for own colour bar.

    2. Dear Loes,

      Regrettably, I don't know any way to do this either : (

  7. Hi, there is some great info here but I can't figure out how to do what I'm trying to do.

    NOTE: Rephrased/Not repeated

    I have a goal value in cell S9 (this is my daily goal). I have a column of values in C (actual value for each day). I want to compare the value in column c to cell S9 to see if I'm on track to hit my goal. For each cell in the column, if the value in the cell is within 95% or more of the value in S9, then make it green. If it's only 70% to 90% or so of S9, then make it Yellow. if it is between 50% and 70 or so is Dark yellow, and if it is below 50% make it RED.

    I was trying to "use a formula to determine which cells to format" and use a formula, but I fail to do so. Can anyone help me with this?

    1. Hi Yonas,

      Select all the cells in column C you want to highlight (without the column header, if any) and create the conditional formatting rules based on the following formulas:

      Green:=$C2/$S$9>=0.95

      Yellow: =AND($C2/$S$9>=0.7, $C2/$S$9<=0.9)

      Dark yellow:=AND($C2/$S$9>=0.5, $C2/$S$9<0.7)

      Red: =$C2/$S$9<0.5

      In the formulas, C2 is your fist cell with values.

      Also, please note that there is a small gap between 95% (lower bound of the red rule) and 90% (upper bound of the yellow rule), meaning that values within this range won't get colored. But I think you can easily change the figures in the corresponding formulas on your own if needed : )

  8. Hi, there is some great info here but I can't figure out how to do what I'm trying to do.

    I have a goal value in cell S9 (this is my daily goal). I have a column of values in C (actual value for each day). I want to compare the value in column c to cell S9 to see if I'm on track to hit my goal. For each cell in the column, if the value in the cell is within 95% or more of the value in S9, then make it green. If it's only 10% or so of S9, then make it red. 50% or so is yellow.

    I was trying to use a 3 color scale so I get the gradients to tell me if each day is close to hitting my daily goal. If I can't use the gradients, that's okay. Do you know how I can do this?

    1. I figured it out. Thanks!

  9. I am curious about using icon sets to warn me when if I've used a certain number of hours - like a warning if I'm close to using all hours (yellow) or if I'm in danger of going over my hours (red).

    For example, if I have a value in A1 which is the amount of hours used and A2 has the available hours for use. I want A3 to use icon sets (red/yellow/green) based off of the percentage of hours consumed.

    so,
    Red = x>90%
    Yellow = x 75 and 90%
    Green = x <=75%

    Is this possible?

    1. Hello Hanna,

      Please try the following:

      1 Enter the formula =A1/A2 in cell A3.
      2 Go to Conditional Formatting -> Icon Sets and create a new rule for cell A3.
      3 In the New Formatting Rule dialog box, specify the following settings:
      - Click the "Reverse Icon Order" button.
      - Select the "Show Icon Only" checkbox.
      - For the red icon choose ">"; 0.9 ; Number
      - For the yellow icon choose ">"; 0.75 ; Number

  10. I would like to make the data bars three colors with gradient fill to indicate a change in percentage Red for 0-30% Yellow for 30-70% and Green for 70-100% sort of what the 3-color scale does only in data bar format? Is this possible?

    1. Sorry, Nathan, I don't know any way to do this. Most likely this is not feasible.

  11. how to change the colors scale in format style more than three colors.

    1. Hi Tajamal,

      Regrettably, this is not possible.

  12. Is there a way to add an extra layer of formatting with the icon sets (ie add a blue circle in addition to the Red, Yellow, and Green options). I have a data set that needs to be analyzed into for different components. Thank You.

    1. Hi Jonathan,

      To my best knowledge, only one icon set can be applied at a time.

  13. When it comes to Conditional Formatting, I really was in the dark but after going through the notes I hv now at least seen some light thanks to the illustrations. Only I'm still confused as to why when I assign Icon set to represt values in a cell range which are in percentage type (%) do I have to change type to Number in the Manage the rules dialog box for it to give me the desired results?

    1. Hello Ramaite,

      Sorry, I am not sure I can follow you. Anyway, it is difficult to recommend anything without seeing your data. If you can post a data sample and explain what exactly result you are truing to achieve, I'll try to help.

  14. Hi,

    thanks for the useful info. After applying the conditional formatting, if we want to copy and paste it into Word or presentations how should be do it. I know we can paste it as image. But, the image doesn't work for me so is there a way to paste it as an excel table where the icons remains in the word as they are in the excel.

    Thanks!

  15. Hi there, I am stuck with a small problem I have in conditional formatting and came thru a blog by you and thought of writing here.

    Here I need to show if the % change in the cost driver is more than 2% higher or lower than the % change in actual price.

    Product Warehouse July'13 Aug'13 Sep'13 Oct'13
    A 1 Actual Price 1.65 1.67 1.71 1.77
    A 2 Actual Price 1.65 1.53 1.57 1.73
    Cost driver 1.62 1.67 1.74 1.69

    Can you please help.

    1. Hi,

      Our support team sent you a solution by email. Hopefully, it was helpful.

  16. Hello Sean,

    Regrettably, you cannot do without an additional column. Excel icon sets allow using formulas as a condition, but do not support relative cell references that are needed in your case.

    So, you can achieve the result you want in this way:

    1. Create an additional column.

    2. Enter the formula =AJ162-AJ161 to row 162 of the newly created column (assuming row 162 is the first row where you need the icons).

    3. Copy the formula to all other cells of this column until row 175 (or the last row where you want to display the icon set).

    4. Create the icon set rule for the column with the above formula. In the rule:
    - Select the 'Icon Set Only' check box.
    - For the green up arrow, set >=1.
    - For the yellow right arrow, set >0.
    - Set Type to "Number" for both icons.

    Hope this helps.

    1. Thank you Svetlana for the reply, it does help, unfortunately I have several tables (about 10), 22 columns where values may be changing randomly in each table, and nearly 300 rows of parameters (~600 total). Is adding a new column the only possible way to show a change in value or no change? I've looked up other forums where similar questions were asked, and I've seen "IF" or "AND" formulas being put in with the values in a cell, and then giving it an icon to put in if it matches the statement. Is that possible, or am I better simply going over the cells with my eyes, looking for a change, and going from there? Hope my questions aren't confusing and that there is some sort of solution other than adding columns.

      Thanks,
      Sean

      1. Hi Sean,

        Of course, you can use IF and AND formulas as well. The problem is that you cannot have both - a formula and your current data - in the same cell. So, an additional column will be needed anyway.

        As an alternative, you can consider changing the cell's fill or font color instead of adding icons to reflect the value change. If this approach may work for you, you can create 3 rules based on the below formulas:

        Green (a value increases): =$AJ162>$AJ161
        Yellow (a value is the same): =$AJ162=$AJ161
        Red (a value decreases): =$AJ162<$AJ161 For step-by-step instructions on creating rules with formulas, please see Excel conditional formatting formulas.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)