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: 162

  1. Dear Good morning,
    One question... it's possible to display in a gantt chart a mailstone with flag that use color variable green red or yellow flag in function of status value ?
    Many thanks in advanced
    Paolo

  2. I want know can conditional format a data for 4 employees in 4 different rows for 3 months in 3 different columns. If they are doing 2 3 4 hours work for 1st employee 4 3 2 for 2nd employee & 3 4 6 hours for 3rd employee & 7 8 9 hours for 4th employee
    Now I want to continual format with icon set plz help

  3. hello every one

    I would like to use two columns.first one will have temperature data, second one will have symbols like (Suspect or good).Every cells at second column will check the data which at tle left cells at the left column and if there are persist data more than determined number( l will chose it like if there are 10 same temperature data next to next) than will write SUSPECT at the righ column cell .

    thank you

  4. Hi,

    Please help me to make the icon sets for below calculation.

    >85% (Green Color)
    >61% to 84% (Yellow)
    <61 % (Red)

    How can I make?
    Thanks

  5. Hi

    I am not an advanced Excel user but I wondered if you could help. I have a budget sheet that runs on month after month and I use filters to see what I'm spending in the current month (by filtering out all the previous months). I also assign a different category as I enter each invidual expenditure (food, petrol etc). I also have the monthly budget total for each catagory on a separate sheet.

    What I would like to do is see easily, at a glance, when I have spent each budget - whether this is using icons or simply having a list of categories that decrease in value as I spend. I can do this manually by using filters and cross checking on the separate budget sheet but is there a way to do this automatically using forumulas?

  6. Can i change icon colour?

  7. hello friends,
    is it possible to put formula depending on icon color,
    suppose i already have green, yellow & red in row & i want one cell which represent if all green then green or any one yellow then yellow or any red then red

  8. I wonder if i could do thing like : if cell A equal to cell B format cell C with a symbol tick, if the two cell are not equal format with a symbol X and if i have nothing in cell A or cell B format with symbol!. any help ?

  9. Hi Need help with the below.

    I have created a conditional format for one of the cell where the cell, if the cell value is great than the value from the last week show Up arrow, if the same cell value is lower than the value of the previous week show Red down arrow. I am not able to copy this conditional format to the other cell as conditional format marks the initial cell value as $ so when I copy the format it still looks up the first cell from the previous week. Please help.

  10. Hi Svetlana
    I have a row of data containing formula that returns a number. I have applied the icon sets to return an icon based on <33rd,67th percentile but when I manually check the sets based on the colours that should be showing they are wrong. Is there anything obvious i should check for? I have copied and pasted values but still get the same result.
    Thank you!

  11. Hi Svetlana
    I have a row of data containing formula that returns a number. I have applied the icon sets to return an icon based on <33rd,67th percentile but when I manually check the sets based on the colours that should be showing they are wrong. Is there anything obvious i should check for? I have copied and pasted values but still get the same result.
    Thank you!

  12. Hi Svetlana,

    I could really use your assistance. Is it possible to apply an Excel icon set based on the sum/difference of another cells formula =IMSUB(E2,F2)? this is for a running inventory sheet. E2 would be starting stock, F2 would be number of stock used, G2 then shows current stock. I want cell G2 to show icons according to the result of =IMSUB(E2,F2) Thank you for any help.

  13. Hi there, the circular icon sets in excel is limited to three; red, green and yellow. I want to indicate the status of my Action Log as:
    -In progress
    -Overdue
    -Completed
    -Postponed
    -Cancelled

    More than three colours (actually 5) here, any advise on how we can get around to get 5 different coloured circular icons??

    Appreciate you advise

    John

  14. Sorry * Yellow when >=D2*0.8

  15. I have posted only a part of data I have a load of such target V/s actual figures which are to be indicated with icons. Please suggest any formula in condition formatting icon sets so that I can drag or format paint without the error of relative references

    Thanks in advance

  16. D E F G

    2 Budgeted 80 90 100 100
    3 Actual 80 89 97 97
    Icons required in actual (Row 3)
    Green-when value >= D2
    Yellow-when =D2*0.8
    Red-when<formula
    But this only works for cell D3, and not for E3, F3 or G3. Need it for a dashboard urgent help please

  17. Update to problem above

    Hi,

    I want to use icon set formatting in the below scenario:

    D6 - revenue 100

    D7 - revenue 90

    D8 - revenue 95

    I want to show icon set in D6 and D7 comparing to D8. so for example D7 is less than D8 so Green up arrow, if it was greater than D8 red arrow down but D6 is greater than D8 so red down arrow and if it was less than D8 then green arrow up.

    Any help would be hugely appreciated.

  18. Hi,

    I want to use icon set formatting in the below scenario:

    D6 - revenue 100

    D7 - revenue 90

    D8 - revenue 95

    I want to show icon set in D6 and D7 comparing to D8. so for example D7 is less than D8 so Green up arrow but D6 is greater than D8 so red down arrow.

    Any help would be hugely appreciated.

  19. Hi

    I am trying to create a spreadsheet which column A is (Issue Date)Column B (Purposed Date) and Column C (Competition Date ) there are column D (Status)

    I want column D highlight (Red, Amber, Green) based on Purposed date if the issue date past purposed date highlight red on column D, and if competition date is before purposed date highlight Green on column D or else Highlight amber if Issue date is not exceed purposed date.

    Hope these make sense

    Hoping for your views and reply on this

    Many Thanks

    Emmanuel

  20. Hi,
    Suppose i have created a table where i will update my data daily or weekly wise.
    Now i want to create another chart or table which just shows a particular color corresponding my values in the table i created but not the value itself.
    Is there any formula for that in excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 :)