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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi!
    I have a trend analysis looking at data from Sept-August and a column that ranks the results in the trend 1-12 based on their type. I have conditional formating applied to the column using icons ex. green is for everyting from 1-3, etc. It works great with a data that's there, but when I try and paste new updated data over the trend, the conditional formating in the ranking column disapears and does not work when i try to reapply it. Do you know why that happens and what would be the remedy for it.

    Thank you, Lesya

  32. can add new icon in excel to use them in conditional formatting? like smileys (emotions for showing progress of any activity e.g. smiling face in completion of activity, sad smiley in case of incomplete activity)??

  33. Ok, I want each column to show the Up, down arrows in comparison to the previous column. For example if the value in B2 is greater than B1 the green up arrow appears in B2. I know how to do this separately, but is there a way to copy and fill series this formatting?

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

  35. Good morning,

    I am trying to insert an icon (X, check, exclamation point) into a cell based on the absolute value in another cell of the same row but can't figure out the right way to accomplish this. The value will be a score - 1, 2, or 3 - and the icon would correspond to the value.

    When I go into the conditional formatting menu and try to create the formula based on a number I cannot choose an equal to option, only >= or >

    Can you help? It must be so simple I am missing it!

  36. We operate a business that has credit accounts. We update the accounts weekly, but make changes do to purchases frequently. Is there a way to show by using the arrow icons, what type of action was last performed? i.e., was there a credit added or was there a deduction made. Would ideally love for this to happen every time the cell text is altered.

  37. Dear Svetlana,

    I have a change column and I would like the values to be gradients of red or green if they are positive or negative and then anything = zero to be white/ no formatting. Right now the default gradients will highlight the lowest positive number as red, which makes it look like it was negative.

    Thanks,

  38. 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 : (

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

  40. Hi there,

    I agree with some of the other commenters that there is some really valuable information found here.

    I am having some trouble accomplishing something and I was hoping to find some help.

    I am creating a log for perishable items. In Column F (Expiry Date) I have conditionally formatted the cells to fill yellow if within 30 days of expiry, and red the day of expiry (and beyond).

    I have Column G (Status) which I would like to conditionally format using a colour scale (3 colours: Green, Yellow, Red) based on the time left (from the dated of entry into the log) until the expiry date in Column F.

    I know the expiry date column is already conditionally formatted, but I think the colour scale provides a better visual control for the status of the product and how far into it's shelf life it is. I hope that makes sense.

    Any help would be much appreciated!! Thanks.

    1. Hi Jeremy,

      I believe we will be able to help you better, if we can have a look at your data. If possible, please send a sample workbook at support@ablebits.com and specify the expected result.

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

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

  43. Svetlana - what was the answer to Mary's question - I have same, where I have MtM data in separate columns and want to use Icons to mark if the increase MtM is greater than 10%, Less than -10% or between.

    How do I sent you the data set?

    1. Hi Ben,

      Mary's question was a few months ago and I do not remember the details, sorry. You can send me your data set at support@ablebits.com and I'll try to help. Please also specify in what exactly column you want to add the icons.

  44. Hoping someone can help!

    I have created a stock monitoring/forecast sheet, including 3 columns -

    A - Stock at the end of the month prior
    B - Expected stock at the end of current month
    C - Stock in hand

    I want to format cell C for each line (list of items we stock) to have a data bar highlighting how close it is to reaching the value in cell B.
    I can do this by selecting 'Format all cells based on their values', and choosing the shortest bar as a formula, linking to A, and the longest bar as a formula, linking to B.

    In addition I also want a format overriding the data bar to show the cell in a solid colour when it reaches the value in B.
    I can do this by selecting 'Format only cells that contain', and choosing 'less than' and formula linking to cell B.

    I want to copy this formatting to apply to all cells in the same column, but when I try to copy and paste it in the next cell below C, it is linking to the original cells A and B for the formatting, not the one's below them (absolute formulas, not relative). I have read that you cannot set relative formulas when using data bars.

    Is there a way to apply my above formatting over a number of cells at once, without having to format each one individually?

    1. Hello Kate,

      I believe it would be easier for me to suggest a solution if I could see your data. If you can send your sample workbook at support@ablebits.com, I'll try to help.

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

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

    1. Hi Tajamal,

      Regrettably, this is not possible.

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

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

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

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

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