Customize Excel Conditional Formatting Icons

Customize Excel Conditional Formatting Icons

In Excel 2007 and Excel 2010, you can use icon sets in conditional formatting. There are built-in icon sets, and in Excel 2010 you can Customize Excel Conditional Formatting Icons, to some extent. Here’s how to do that, and a workaround to create icons on the worksheet instead.

Built-in Conditional Formatting Icons

There is a good selection of built-in Excel Conditional Formatting Icon sets.

For example, use Red, Yellow and Green stoplight icons, to highlight the good, average, and poor results in your sales data.

Or, choose directional arrows, with a green Up arrow, a right-pointing yellow arrow, and a red Down arrow.

conditionalformaticon00

Grey Directional Arrows

There are also icon sets with grey directional arrows. Those are useful for data where an increased amount is bad, instead of good. For example, your data might show counts of part failures, or customer complaints.

  • Traditionally, green means “good” or “safe”, and red means “bad” or “danger”.
  • There’s no setting that lets you change the icon colours
  • The neutral grey arrows would be better than using a green Up arrow, for data where Up is “bad”

Or, see the section further down, with notes on how to create your own icon set, outside of the conditional formatting settings.

Limit the Colours

Rob emailed me recently, to ask how to limit the conditional formatting icons to 2 colours only, instead of the 3 or 4 default icon colours.

I am only interested in using 1 or 2 icons (a red X for “Off” and a Green light for “On” – not interested in the Yellow light). I want these icons to be triggered by a boolean (TRUE/FALSE) in another cell.

Create Your Own Icon Set in Excel 2010

Fortunately, if you’re using Excel 2010, you aren’t limited to the default icon sets – you can create your own icon sets , by mixing and matching from the available icons. (You can’t create your own icons, unfortunately, or change the look of the built-in icons.)

To create the icon set that Rob wants, I selected cells B2:B5, and set the following Formatting Rule.

  • The Show Icon Only option is checked
  • Green Circle icon when the value is greater than or equal to 1 (Number)
  • Red X icon when the value is less than 1 and greater than or equal to 0 (Number)
  • No Cell Icon when the value is less than 0

conditionalformaticon02

How It Works

In cell B2 there is a formula to multiply the value in cell A2 by 1:

=A2*1

That formula is copied down to cell B5.

  1. If the result in column A is TRUE, the formula result in column B is 1, and a green circle shows.
  2. If the result in column A is FALSE, the formula result in column B is 0, and a red X shows.

conditionalformaticon01

Get the Sample File

To get the Excel file, with the Create Your Own Icon Set example, go to the Conditional Formatting page on my Contextures site.

The zipped file is in xlsx format, and the file does not contain any macros.

Create Your Own Icon Set

If the built-in icon sets for conditional formatting don’t have what you need, you can create your own custom icons, outside of the conditional formatting settings.

See this Create Your Own Icon Set article, for the step-by-step details.

This workaround technique also works for earlier versions of Excel, where you can’t customize the icon sets, or in Excel versions where icon sets don’t exist

In this technique, you use the WingDings font, combined with conditional formatting, to show coloured symbols in the cell.

And if you’re still using Excel 2003, there are detailed instructions in this article: Conditional Formatting Icons in Excel 2003

image
_______________

44 thoughts on “Customize Excel Conditional Formatting Icons”

  1. Nice post. Although you cannot customize this in Excel 2007, i did find that if you choose the “3 Symbols (Circled)” icon set it will get pretty close. If true = 1 and False = 0, if you leave the default conditional formatting, “Green Check Mark Circled” >=67 percent and “Yellow Exclamation Point” >=33 percent, and “Red X Circle” <33 percent, you will get the desired result of a Green Circle for True and Red X (in a circle) for False.

  2. Great post, Deb! I had not realized that Excel 2010 allowed for customization of the icon sets.

  3. I’m using conditional formating on spreadsheet with icon sets (flags). It is working sometimes and other times in the same spreadsheet it is not working. I wanted a red flag if =>50% and yellow flag if > 20% and green flag if < 20%. The formual works in some areas and in others it doesn't.

  4. I have a customer who was trying to configure conditional formatting as described from Patty. Whenever we format the cells to Percentage, it doesn’t work. Nothing happens when values are changing. With numbers only it works. No difference of what value is entered. Cell and Formatting was changed to percentage.. We where even trying other options in formatting.

  5. My experience with conditional formatting on cells with percentages is often times you can select Number instead of Percent in the conditional formatting and then enter (for example) .95 (number) as opposed to 95 (percent). It all comes down to the way that the number is actually formatting in the cell vs. how conditional formatting is setup.
    FG

    1. THANK YOU FRED!!!! I was tearing my hair out with the percentages wondering why they wouldn’t work. I changed the conditions to “number” and used decimals (.50) etc. and they work!!!! (I must have watched a dozen videos, but never saw that tip.)
      You made my day!

  6. Hi
    How can I change the colour of the arrow pointing down from red to green?
    This would be handy, as I need to indicate a positive reduction.
    Thanks for your support!
    Albert

  7. Hi,
    does anybody have an idea how I can create my own icon set (based on graphics or pictures)? Is there any possibility to “load” them into the conditional formatting function?
    Thank you very much in advance!
    Cheers,
    Simone

  8. I am having the exact same problem that Debra is having. Sometimes it works and sometimes it does not, I was embarrass to submit a spreadsheet with wrong data. the first entries worked but didn’t bother to check the rest of the spreadsheet. I will try changing to number as suggested by Fred .95 and number cell.

  9. With Excel 2012, is there a way to integrate a customized set of icons into the application? I have not disected the registry or dlls to determine where/how Excel retrieves an icon set when prompted. It would be interesting to have the ability to have a customized icon set to retrieve.

  10. Hi,
    I am trying to get my traffic lights to display only red or green for the below 3 criteria:
    Adherance: green for above 100% and red for below 99.9%
    Talk time: green for below 2:30 and red for above 2:31
    Interactions: green for above 80 and red for below 79
    I am not having any luck
    Adherance to schedule Talk Time Average Interactions per day
    123.7% 02:49 67
    98.9% 02:23 72
    101.2% 02:49 50
    114.1% 02:24 73
    141.1% 02:44 63
    106.2% 01:53 90
    104.4% 04:15 42
    105.4% 03:07 62
    117.1% 03:05 64
    102.0% 02:38 75

  11. Hi,
    How to set arrow icon display in front of column A value after compared A column value to B column value.
    Right now I can set for one cell but copy to next row if need to adjust the reference cell manually.
    Anyway to solve this ? Thanks

    1. I have the exact same problem Linda. I am trying to fill down the same condition. However when I do fill down, the conditioning factor defaults to the oompared cell in the first row instead of comparing to the one directly next to it in hte cooresponding row. When I tried to condition the whole column as complared to the whole of the column to the left, I get an error message. Let me know if you figured a way around this because I have way to many cells to condition each manually.
      If anyone has solved this please let me know.

      1. I have tried using dynamic formulas (indirect/index/offset) to try to solve this problem and have had no success. It seems to me that there is a bug in excel in that when using dynamic formula, the icons are defined by the current cell that the cursor is pointing at and not to the cell which has the formula in it.
        For example, using index($B:$B,row()) I would expect to see icons based on a comparison to column B and the row that the formula is located in. However, the row() function returns a different result every time I move the cursor to a different row.
        Has anyone noticed this problem? Anyone know of a solution?

  12. Hi!,
    I am trying to highlight a 5% variance in my data, I have cell A1 as Budget figure, B1 as Actual figure and C1 as the difference, I want C1 to go red when it is 5% > or < the budget figure, can anyone help please ? I am using MS Excel 2010
    Thanks
    Clodagh

  13. I too would like to have the option to have an up arrow coloured red (it doesn’t make sense to indicate a rising temperature with a green up arrow – unless you are trying to reach an optimum temperature – ) and a down arrow as green (a reduction in sickness in the department is definitely a green indicator)

  14. Excel should have more fancy icons to choose when doing conditional formatting.
    The selection is too small and boring today.
    Also, it should be possible to customize and insert your own icons!
    Strange this is not an option in Excel.

  15. Here’s a workaround for Excel 2007 to display only two of the three icons.
    A series of drop-down lists (Status_01, Status_02, etc.) have two selections: “Yes” or “No”. The list may also be left empty is it isn’t needed.
    Conditional Formatting Rule #1:
    Format only cells that contain
    Cell Value equal to =0
    Cell Font = white, Fill = white
    Stop If True is selected
    Conditional Formatting Rule #2
    Format all cells based on their values
    Format Style: Icon Sets
    Icon Style: 3 Symbols (Uncircled)
    Show Icon Only is selected
    Checkmark when value >= 2 Number
    Caution when 0 Number
    X when <= 0
    Formula of cell: =IF(Status_01="Yes",2,IF(Status_01="No",-1,0))
    "Yes" in Status_01 displays the green checkmark.
    "No" in Status_01 displays the red X.
    No selection in Status_01 displays an "empty" cell. Note: default value.

  16. sorry to add more question to the above, i need to show the equal icon if the value of the cell I’m formatting is equal to the value of a cell next to it…help please. thank you

  17. Is there a way to count all the individual colors of the stoplight in a column? Green is current, red is expired, and yellow is will expire within 90 days. I need to have a total of each status.

  18. Hi,
    Please help me out someone,how to set icon in excel on data column compare with another cell data.Like- I want to compare B1 cell data to A1 if it is greater than A1 then display green up arrow in B1 cell with data value and if not meet specified condition then display down red arrow in B1 cell with data value.

  19. Hi all,
    I’m wanting to put a green light icon where the a value is equal to or above a target number in the cell above it, or red light icon if it’s under.
    Item Year1 Year2 Year3
    TargetItem1 10 20 30
    CurrentItem1 8(red) 19(red) 35(green)
    TargetItem2 10 25 35
    CurrentItem2 11(green) 25(green) 30(red)
    etc.
    Is there a way to do this directly or do I need some hidden cells?

  20. Debra, How would I create budget vs. actual, three conditions:
    1. budget > actual – green icon
    2. budget = actual – yellow icon
    3. budget < actual – red icon
    Icons would be located in the account discription cell and would act on the budget and actual cell values.
    Column B holds account descriptions
    Column E holds budget values
    Column F holds actual values

  21. is there a way to do this with words instead of numbers if “yes” = check mark
    “no” = x “not found” = “?” …. thanks!!

  22. Hi there!
    I would like to see arrows in one column be based on the relative value vs the previous column. For example, green arrow on B12 if it’s larger than the value in A12, etc. How can this be accomplished?
    Thank you!

  23. Hi,
    In Share Point 2013 I have created stop lights with 5 different colors.
    Now that I want to export this to Excel I can´t use all five colors because the bullets are only four in Conditional Formatting.
    Is there any code or anything else I can use to add the fifth color in Excel or is this out of the question?
    I’m grateful for any help I can get!

  24. Hi guys, i have a bit of a tricky one.
    I’m using the arrow icon set with the colours. The trick is (not sure if possible though), i’d like to change a green (upward pointing) arrow to a red updward pointing arrow?
    Has anyone done this before?

  25. Hi i’m using the conditional rules for icons.
    I’ve got my column to change Yes = 1 & No = 0
    I’ve got all my green ‘ticks’ and red ‘crosses’
    is there a way i can get these icons to auto generate when additional data is added to following rows?
    This is a living spreadsheet…
    Thanks in advance!

  26. Hayley: you need to copy/paste the conditional formatting (e.g. with Format Painter) to the currently blank cells that you wish to use it.

  27. I would like to create a rule where a blank cell = red x. This is for an attendance sheet. The only thing I can see to do is to have blank cells filled with a color or pattern. Currently I am using “1” to create a green checkmark. Just trying to save myself having to put in everyone who didn’t attend as a “0”. But if that is the only other workaround, I’ll have to do that.

    1. Yes, you’ll need to add a zero, if you want to use the icons with conditional formatting.
      Or, enter a formula in another column, e.g.: =IF(B2,””,0)
      Then, add the conditional formatting to the formula column — it would just show the red X marks

  28. I though t I had submitted a question yesterday. Is there any way to create red up arrows and green down arrows based on whether a value is greater or less than 0? It is easy to do with a red down arrow and a green up arrow, but not to reverse the colors.

  29. I have a columns, with percentages – for example –
    -2%, -10%, 13%, -19%, 17%, 30%, -31%,
    I’m trying to get the Icon sets (Arrows) – green, yellow and red. For negative percentage, I want the Red Down Arrow icon, for positive percentage Green Up arrow icon.
    I tried changing several conditions on the manage rules of conditional formatting but wasn’t successful. Please assist.

    1. For the Green Up arrow icon,
      –select Greater Than or Equal to as the operator
      –enter zero as the value
      –select Number as the type
      Change the 2nd icon to a Red Down arrow, and
      –select Greater Than or Equal to as the operator
      –enter -9999999 as the value
      –select Number as the type
      Leave the 3rd icon as a Red down arrow

  30. Hi, I have a five star rating for my movies (all yellow) in Excel 2010. I want to give each rating a different colour star. How?
    my ratings are: 5 stars = Yellow, 4 stars = Orange, 3 stars = Green, 2 stars = Blue and 1 star = Red
    Please reply

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.