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 3. Total comments: 162

  1. Dear Team,
    please I would need your expertise touch here.
    I have three cells (say A1, B1 and C1) .
    A1 is titled "%completed"; B1 "%Tartget" and C1 "Rating/Status"

    I would like to be able to compute (use nested if to determined the status of the two cells if the difference btn cell A1 and B1 meets a condition.

    ***This explains how the ratings are computed.
    --Not Started , if value equals zero (0) (black Arrow down)
    --Achieved, if %completed is greater than 90 (green Flagged)
    --Partly Achieved , if %Comp greater than 50 but less than 90 (Yellow Flagged)
    --Not Achieved , if %comp not equal to zero but less or equal to 50 (Red Flagged)

    the above is working fine for me but there are instances where cell B1 (%target has a mark of say 48 which will not be achieved according the above creteria.

    Please who can achieve this ?

    worried,
    Fred

  2. i am using the icon set red, yellow and green circles. is it possible to change the color of the icons, they are a bit pale according to my boss. i am in excel 2010

  3. Hi. I'm using Excel 2016. For the Icon Sets, mine are rust, gold and
    muted green, not a normal red, yellow, and green as I see in the screenshots above. I tried changing the Theme, but no change. What can I change to get the more traditional colors for the traffic lights?

  4. hi

    i have a table consist of 3 columns filled with numbers. i want 3 icons which tell me which data value is maximum, which one is minimum and which is between them?

  5. Hello, I have an excel pivot table that has pulled in data from a sharepoint dashboard file. The cell content indicates RAG of Green, Yellow, Red. Is there a way to apply conditional formatting to interpret those rags and provide icons?

  6. can you help in conditional formatting. I want to know how to fill a cell like vertical bar bottom to above

  7. Hi
    Please help me.. i try to make custom icon in conditional formatting, but it cant be save..after reopen the file, the conditional formatting formula not shown..

  8. Need to put stocks but i need put it in one cell i have one target like ...

    Target : 90%
    The cell : 95.3%

    Then i need put stock next to 95.3% in the same cell

    Thanks...

  9. Hi - conditional formatting was working well on the data I pulled in to my sheet via a CSV URL feed. The publisher of the feed changed the CSV format (putting the header rows on the bottom--causing me to have to reverse the rows in order to use the first row as the column headers--no big deal there); however, now the conditional formatting no longer works. All the data / columns still the same but it's as if Excel no longer recognizes the type of data in the column--raw from the CSV or formatted manually in Excel. Seems it won't recognize the format. Could formatting be an issue why conditional formatting STOPS working?

  10. Also, i have a formula for that particular column and it is formatted to %, the conditional formatting doesn't work. However, if i change the format to number, apply conditional formatting and then convert the column to %, then it works. Why is this happening?? What is the problem with applying conditional formatting in % formatted cells?

  11. Hi,
    I'm trying to get icon sets with following conditions-
    If values are >75% represent with Upward arrow
    If values are between 30% & 75% represent with Straight line
    If values are <30% represent with downward arrow

    Please help me on this as i am getting a down arrow for 33% as well?

    Thanks

  12. I have conditional formatting on column N which fills different colors based on containing word on it. I want same color to fill on the column A:M based on column N color. Tried many ways to get the same color from formatted column but not succeed. Is there any way to get same color from other the cell?

  13. I am using the Data Bar in Conditional Formatting and my data contains percents. I want the length of the bar to be relative to 0% to 100% but the length of the bar is always relative to the highest percentage value in my data. I've tried changing all the minimum and maximum settings but have not found a fix. Is there a way to do this?

  14. Hi. For an Excel/PPT presentation i would need data change visualization. I would like to do it with the help of the Conditional Formatting - Icon Sets. But i would need other Icons then arrows or the given ones in Excel. Is there a known possibility to somehow import other new icons to Excel for this setting? (For Excel 2010 and 2013)
    Thanks in advance.

  15. I am trying to create an excel chart has a date column (Column A) and tasked organizations (columns F thru AB). I am trying to format it so that if the date passes, the organizations that are tasked (not all are assigned at any given point) the box will automatically go black/overdue. Until that date, the box should just be yellow or "pending". I am really confused and tried multiple sites. I am using Excel 2013.

  16. I have a spreadsheet and I am trying to automate a row that will show a green arrow up or red arrow down depending on the relationship between the 2 previous columns. So if the # in column A is greater than the # in column B, I want a green arrow in column C. And vice versa. Any help would be appreciated. I have played around with the Rules under Conditional Formatting, but can't seem to find one that fits what I am trying to do. (Excel 2013) Thanks.

  17. I want to set a cell can display the cut-off time itself and use solid fill function to show may time left.

    For example, our time start from 8:30 AM and one of our counter's cut-off time is 9:30 AM. I want this cell always display 9:30 and the data bar will become shorter base on time pass.

  18. Thanks Svetlana!
    You really saved my day. Great and clear info. Greetings from Bogotá. :)

  19. I want to use a text value to trigger a conditional formatting icon. Text value being red =(red dot), yellow = (yellow dot), green = (green dot). Can this done?

  20. I want to make a checklist, is there a way to put a checkbox before each item and have code that if there is a check mark in the checkbox it highlights whatever the cell (not row) is to the right of the checkbox?

  21. Hi,
    I'm trying to set the conditions to act or function this way,
    If values are >0% represent with Upward arrow
    If values are =0% represent with Straight line
    If values are <0% represent with down ward arrow

    Anyone has an idea about this?
    Please help
    Thanks

    1. Hello,

      You need to create a conditional formatting rule based on cell values, choose "Icon sets" as the format style, pick the necessary set of icons in the "Icon style" drop-down list and enter the following rules:
      - Show upward arrow when the value is >0 and choose "Number" under "Type"
      - Show a straight line when the value is >=0
      - The downward arrow will automatically get the condition "when <0":
      Conditional formatting with icon sets

  22. Hi Svetlana,

    I'm that you are helping everyone in the forum with solutions for their question.

    I too have a quick question and hope it's easy for you. I would like the values in the column to be represented by icons the condition would be
    1. If the values are >10% it must represented by by a upward arrow
    2. If the values are >0 %<10% It must be represented by a straight arrow
    3. If the values are <0 % It must be represented by a downward arrow.

    Hope it's easy for your level of expertise. Many thanks in advance and look
    forward to hear from you.

    Regards,
    Gabriel

    1. Hi Gabriel,

      Hopefully, this is what you are looking for:

      Icon set

  23. Hi,need your support on below case, thanks a lot,

    I want to the sixth number/color in icon sets, but only five labels in excel sheet,

  24. I have created conditionally formatted icon sets (stoplight) for an array of data. I would like to set up a word mail merge and have the icons (red, yellow, green stoplights) appear in the word doc on mail merge. Instead I seem to only be getting the value of the cell. Any help?

  25. Hello! Thanks for all the great info re conditional formatting, very helpful. In my case, conditional formatting works perfectly based on the parameters set and I have the spreadsheet set to display the icons only. As an added benefit for soft copy readers, I'd like the data value of the cell to be displayed when the mouse hovers over the cell. For example, the resulting data value of cell A10 is +10bps and a green light is displayed but the value is hidden. I've seen other spreadsheets (creator is not available) where I can hover my mouse over the cell and +10bps is momentarily displayed. Thanks in advance for your help!

  26. Hi,

    I'm having trouble with 3 colored arrows, when applying the previous solution I do not get the expected results; Formatting is in Column C, when it's positive I want green arrow, when negative red and zero value should be marked with yellow arrow. e.g. for negative value, -13.94% i'm getting a Yellow arrow and it should be marked red. I'm making a wrong rule but i'm not seeing the mistake. Any help would be appreciated.

    Regards,

    ===

    A B C
    10435 10512 0.74%
    967 1041 7.65%
    622 759 22.03%
    1231 1579 28.27%
    452 389 -13.94%

  27. how to show positive and negative number through icon sets

    1. Hi Imran,

      You can create a rule similar to this:
      Icon set for positive and negative numbers

  28. Hi,

    Can we change the Icon Color based on our requirements in conditional formatting.

    Regards
    Ravindra Tiwari

    1. Nope. You can only choose from the predefined icons.

  29. HI MARY how to separate cells in excel by color using formula by coloring the actual months from the projections (april to March)

    1. Hello Khaya,

      You can create a conditional formatting rule with a formula similar to this:
      =OR(MONTH($A2)=4, MONTH($A2)=5)

      Where A2 is the top-most cell with a date.

  30. Hi,
    I want to use flags on a cell 'A' based on a dropdown list (green, ornage or red), when I select any color, the corresponding flag is not displayed.

    How can I fix it ?

    Thank you

  31. How do I change the colour of a icon set I want the quadrants but in blue instead of black?

    1. Hi Mandy,

      It's not possible to change the icon's color because they are "hard coded" by Microsoft.

  32. Good Day,

    Please assist, I hope this finds you well,

    I am trying to change the conditional formatting rules, to have the 3 color sets only apply, I do not require color to scale at all.

    Thus,
    Green, Should be if 100% is achieved,
    Yellow, Should be between 0.01% and 99,99%
    Red , Must be 0%

    Is there a way to have conditional formatting change this, I tried doing various ways, nothing seems to work, have you encountered a similar request and will you be able to help.

    Thank you.

  33. Hi Svetlana,
    I am currently trying to create relative references on conditional formatting icons as well. I had an idea of possibly using the INDIRECT() formula to accomplish this.. not sure if it is possible, but thought I'd run it past an expert.
    Thanks,
    Terry

    1. Terry,

      You can use the INDIRECT() function to create relative references in Icon Sets rules.
      The key problem is that the rule must be applied only to ONE cell, in this case the formula will work correctly. It means that for 10 cells with icons you need to create 10 separate rules.

  34. Hi Svetlana,

    Is there any way to use data bars in Excel 2010 to format so that 100% is treated the same as anything above that? In other words, I'd like the scale for what the bar indicates to go from 0% to 100% but leave room for the ability to go over 100% (say 285% for example) in the percentage represented, even though the bar itself would look identical to a cell with 100%....Hope that makes sense

    1. Hi Ryan,

      When you apply data bars to a range of cells, Excel treats the maximum value in the rage as 100%, so the scale simply cannot go any higher than 100%.

      In your rule, you can set the Minimum and Maximum Type to "Percent" and the Maximum value, say to 70. In this case, 70% and above will have the identical bar length (entire cell).

  35. Is there any way that i can drag Icon set conditional formatting to other cells also ?

  36. Hi!

    Where you have used Icons above, to the right of the examples shown there are boxes with keys/descriptions as to what the icons actually mean. How do I get the same? Is this some automatic function?

    1. Hi Phil,

      Yes, this is the default functionality. In fact, these descriptions reflect your settings, i.e. the values and logical operators that you choose for each icon.

      You can try creating a new rule (Conditional Formatting > Icon Sets > More rules) and you will see that the descriptions change as soon as you change a value or logical operator.

  37. Also, this page is very helpful, as is the rest of your site. Is there a way to send you a couple of dollars for the benefit this provides to people who are not regular paying customers? This is a few thousand times more helpful (and less painful) than microsoft, apple, or any other paid support.

    1. Thank you very much for your kind words! Your feedback is worth much more than a few dollars :)

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

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

  40. Hello,
    I am trying to figure out what type of formula I would use.

    I have a spreadsheet where I input data. As long as the cell has data in it, it turns green. I want to be able to keep track of what percentage is green in another column.
    For example

    NAME Test A Test B Test C Percentage
    John Complete Complete Complete
    Kevin Complete
    Jack

    The cells that have information in it are green while the other cells are red. How do I make a formula for the percentage cells based on the color the cell is?

  41. Hi
    I am trying to show icons for what I would think would be a simple task but I can't seem to make it work.
    In column "N" I have a current rate percentage. In column "O" I have a target rate percentage. I want to use the icons to show a green check if the target has been met, a yellow exclamation point if the target has not been met, and a red stop light if the current rate is 50% or more below the target rate. Basically just showing which rates have been met, are almost there, and are in danger of not being met.
    Can anyone help with this?
    Thanks,
    Heather

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

  43. Hi
    I've tried to apply the icon sets to the variance report for my budget. As this is the capital expenditure budget for the period I need to monitor the budget at each asset category level and department level to alert on their budget consumption.
    My requirement is as follows.
    Red icon - Actual value is more than 75% of the budget
    Yellow icon - actual value is more than 50% of the budget
    Green icon - actual value is less than 30% of the budget

    I have sent an email to the support team. Please check on this matter.

  44. So I am trying to use ICON sets to conditionally format percentages.
    What I am doing is calculating percentages of certain measures by calculating a Budget target versus and ACtual performance. I am putting the % of attainment into a cell by entering a formula similar to A1/A2 to return the % of attainment. When I go to create the Icon Sets I want a Green Arrow to show next to the result if attainment was 100% or better, a sideways Yellow arrow if attainment was between 90% up to 100% , and a downward Red Arrow if attainment was lower than 90%.

    My issue is, once I have set up these parameters only Green arrows show up regardless of the value of the data in the cell? What am I doing wrong?

  45. Let's say column A has a unit cost that I need to multiply by 15% to give me the new unit cost in column B. Then I'll need to multiply the new unit cost in column B by the quantity in column C to give me a grand total in column D. How do I write a formula in order to achieve this? There are 166 rows of unit cost in Column A and 166 quantities in column B. Please help.

  46. Hi,
    Guys is there any way My row automatically moves up and down when i change its value? Means i have applied conditional formating in a column when i change one of its value its color changes OK. But what i want is that when the value change it must move up or down as according to the ascending or desending order that i have assigned it

  47. Hi,
    Is it possible to apply a color scale conditional format based on values, but then have text in that cell that differs from those values? This would be like in your example above if the expenditure categories themselves were shaded (e.g. House Rent dark green, Car Payment light green, etc.). I know that the conditional formatting depends on the numerical values, but can the numbers be somewhere behind the scenes while the text in each cell gets shaded?
    Thank you!

  48. Hi!

    There is specific set of icons. I want to use my own customized icon set. Is there any possibility of customized icon set in conditional formatting?

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

  50. I want to change format of a cell range, A1 to B10" based on a cell reference, cell A4 and value can be Income or %Income.
    For Ex. If Cell reference is "Income" then format of cell range should be "Numeric" and if cell reference is "%Income" then format should be "Percentage" for the cell range.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)