by The article provides detailed guidance on how to use conditional formatting Icon Sets in Excel. It will teach you how to create a custom icon set that overcomes many limitations of the inbuilt options and apply icons based on another cell value.
A while ago, we started to explorer various features and capabilities of Conditional Formatting in Excel. If you haven't got a chance to read that introductory article, you may want to do this now. If you already know the basics, let's move on and see what options you have with regard to Excel's icon sets and how you can leverage them in your projects.
Excel icon sets
Icon Sets in Excel are ready-to-use formatting options that add various icons to cells, such as arrows, shapes, check marks, flags, rating starts, etc. to visually show how cell values in a range are compared to each other.
Normally, an icon set contains from three to five icons, consequently the cell values in a formatted range are divided into three to five groups from high to low. For instance, a 3-icon set uses one icon for values greater than or equal to 67%, another icon for values between 67% and 33%, and yet another icon for values lower than 33%. However, you are free to change this default behavior and define your own criteria.
How to use icon sets in Excel
To apply an icon set to your data, this is what you need to do:
- Select the range of cells you want to format.
- On the Home tab, in the Styles group, click Conditional Formatting.
- Point to Icon Sets, and then click the icon type you want.
That's it! The icons will appear inside the selected cells straight away.
How to customize Excel icon sets
If you are not happy with the way Excel has interpreted and highlighted your data, you can easily customize the applied icon set. To make edits, follow these steps:
- Select any cell conditionally formatted with the icon set.
- On the Home tab, click Conditional Formatting > Manage Rules.
- Select the rule of interest and click Edit Rule.
- In the Edit Formatting Rule dialog box, you can choose other icons and assign them to different values. To select another icon, click on the drop-down button and you will see a list of all icons available for conditional formatting.
- When done editing, click OK twice to save the changes and return to Excel.
For our example, we've chosen the red cross to highlight values greater than or equal to 50% and the green tick mark to highlight values less than 20%. For in-between values, the yellow exclamation mark will be used.
Tips:
- To reverse icon setting, click the Reverse Icon Order button.
- To hide cell values and show only icons, select the Show Icon Only check box.
- To define the criteria based on another cell value, enter the cell's address in the Value box.
- You can use icon sets together with other conditional formats, e.g. to change the background color of the cells containing icons.
How to create a custom icon set in Excel
In Microsoft Excel, there are 4 different kinds of icon sets: directional, shapes, indicators and ratings. When creating your own rule, you can use any icon from any set and assign any value to it.
To create your own custom icon set, follow these steps:
- Select the range of cells where you want to apply the icons.
- Click Conditional Formatting > Icon Sets > More Rules.
- In the New Formatting Rule dialog box, select the desired icons. From the Type dropdown box, select Percentage, Number of Formula, and type the corresponding values in the Value boxes.
- Finally, click OK.
For this example, we've created a custom three-flags icon set, where:
- Green flag marks household spendings greater than or equal to $100.
- Yellow flag is assigned to numbers less than $100 and greater than or equal to $30.
- Green flag is used for values less than $30.
How to set conditions based on another cell value
Instead of "hardcoding" the criteria in a rule, you can input each condition in a separate cell, and then refer to those cells. The key benefit of this approach is that you can easily modify the conditions by changing the values in the referenced cells without editing the rule.
For example, we've entered the two main conditions in cells G2 and G3 and configured the rule in this way:
- For Type, pick Formula.
- For the Value box, enter the cell address preceded with the equality sign. To get it done automatically by Excel, just place the cursor in the box and click the cell on the sheet.
Excel conditional formatting icon sets formula
To have the conditions calculated automatically by Excel, you can express them using a formula.
To apply conditional formatting with formula-driven icons, start creating a custom icon set as described above. In the New Formatting Rule dialog box, from the Type dropdown box, select Formula, and insert your formula in the Value box.
For this example, the following formulas are used:
- Green flag is assigned to numbers greater than or equal to an average + 10:
=AVERAGE($B$2:$B$13)+10 - Yellow flag is assigned to numbers less than an average + 10 and greater than or equal to an average - 20.
=AVERAGE($B$2:$B$13)-20 - Green flag is used for values lower than an average - 20.
Note. It's not possible to use relative references in icon set formulas.
Excel conditional format icon set to compare 2 columns
When comparing two columns, conditional formatting icon sets, such as colored arrows, can give you an excellent visual representation of the comparison. This can be done by using an icon set in combination with a formula that calculates the difference between the values in two columns - the percent change formula works nicely for this purpose.
Suppose you have the June and July spendings in columns B and C, respectively. To calculate how much the amount has changed between the two months, the formula in D2 copied down is:
=C2/B2 - 1
Now, we want to display:
- An up arrow if the percent change is a positive number (value in column C is greater than in column B).
- A down arrow if the difference is a negative number (value in column C is less than in column B).
- A horizontal arrow if the percent change is zero (columns B and C are equal).
To accomplish this, you create a custom icon set rule with these settings:
- A green up arrow when Value is > 0.
- A yellow right arrow when Value is <=0 and >=0, which limits the choice to zeros.
- A red down arrow when Value is < 0.
- For all the icons, Type is set to Number.
At this point, the result will look something like this:
To show only the icons without percentages, tick the Show Icon Only checkbox.
How to apply Excel icon sets based on another cell
A common opinion is that Excel conditional formatting icon sets can only be used to format cells based on their own values. Technically, that is true. However, you can emulate the conditional format icon set based on a value in another cell.
Suppose you have payment dates in column D. Your goal is to place a green flag in column A when a certain bill is paid, i.e. there is a date in the corresponding cell in column D. If a cell in column D is blank, a red flag should be inserted.
To accomplish the task, these are the steps to perform:
- Start with adding the below formula to A2, and then copy it down the column:
=IF($D2<>"", 3, 1)The formula says to return 3 if D2 is not empty, otherwise 1.
- Select the data cells in column A without the column header (A2:A13) and create a custom icon set rule.
- Configure the following settings:
- Green flag when the number is >=3.
- Yellow flag when the number is >2. As you remember, we do not really want a yellow flag anywhere, so we set a condition that will never be satisfied, i.e. a value less than 3 and greater than 2.
- In the Type dropdown box, pick Number for both icons.
- Select the Icon Set Only checkbox to hide the numbers and only show the icons.
The result is exactly as we were looking for: the green flag if a cell in column D contains anything in it and the red flag if the cell is empty.
Excel conditional formatting icon sets based on text
By default, Excel icon sets are designed for formatting numbers, not text. But with just a little creativity, you can assign different icons to specific text values, so you can see at a glance what text is in this or that cell.
Suppose you've added the Note column to your household spendings table and want to apply certain icons based on the text labels in that column. The task requires some preparatory work such as:
- Make a summary table (F2:G4) numbering each note. The idea is to use a positive, negative, and zero number here.
- Add one more column to the original table named Icon (it's where the icons are going to be placed).
- Populated the new column with a VLOOKUP formula that looks up the notes and returns matching numbers from the summary table:
=VLOOKUP(C2, $F$2:$G$4, 2, FALSE)
Now, it's time to add icons to our text notes:
- Select the range D2:D13 and click Conditional Formatting> Icon Sets > More Rules.
- Choose the icon style you want and configure the rule as in the image below:
- The next step is to replace the numbers with text notes. This can be done by applying a custom number format. So, select the range D2:D13 again and press the CTRL + 1 shortcut.
- In the Format Cells dialog box, on the Number tab, select the Custom category, enter the following format in the Type box, and click OK:
"Good";Exorbitant";"Acceptable"
Where "Good" is the display value for positive numbers, "Exorbitant" for negative numbers, and "Acceptable" for 0. Please be sure to correctly replace those values with your text.
This is very close to the desired result, isn't it?
- To get rid of the Note column, which has become redundant, copy the contents of the Icon column, and then use the Paste Special feature to paste as values in the same place. However, please keep in mind that this will make your icons static, so they won't respond to changes in the original data. If you are working with an updatable dataset, skip this step.
- Now, you can safely hide or delete (if you replaced the formulas with calculated values) the Note column without affecting the text labels and symbols in the Icon column. Done!
Note. In this example, we've used a 3-icon set. Applying 5-icon sets based on text is also possible but requires more manipulations.
How to show only some items of the icon set
Excel's inbuilt 3-icon and 5-icon sets look nice, but sometimes you may find them a bit inundated with graphics. The solution is to keep only those icons that draw attention to the most important items, say, best performing or worst performing.
For example, when highlighting the spendings with different icons, you may want to show only those that mark the amounts higher than average. Let's see how you can do this:
- Create a new conditional formatting rule by clicking Conditional formatting > New Rule > Format only cells that contain. Choose to format cells with values less than average, which is returned by the below formula. Click OK without setting any format.
=AVERAGE($B$2:$B$13)
- Click Conditional Formatting > Manage Rules…, move up the Less than average rule, and put a tick into the Stop if True check box next to it.
As a result, the icons are only shown for the amounts that are greater than average in the applied range:
How to add custom icon set to Excel
Excel's built-in sets have a limited collection of icons and, unfortunately, there is no way to add custom icons to the collection. Luckily, there is a workaround that allows you to mimic conditional formatting with custom icons.
Method 1. Add custom icons using Symbol menu
To emulate Excel conditional formatting with a custom icon set, these are the steps to follow:
- Create a reference table outlining your conditions as shown in the screenshot below.
- In the reference table, insert the desired icons. For this, clicking the Insert tab > Symbols group > Symbol button. In the Symbol dialog box, select the Windings font, pick the symbol you want, and click Insert.
- Next to each icon, type its character code, which is displayed near the bottom of the Symbol dialog box.
- For the column where the icons should appear, set the Wingdings font, and then enter the nested IF formula like this one:
=IF(B2>=90, CHAR(76), IF(B2>=30, CHAR(75), CHAR(74)))With cell references, it takes this shape:
=IF(B2>=$H$2, CHAR($F$2), IF(B2>=$H$3, CHAR($F$3), CHAR($F$4)))Copy the formula down the column, and you will get this result:
Black and white icons appear rather dull, but you can give them a better look by coloring the cells. For this, you can apply the inbuilt rule (Conditional Formatting > Highlight Cells Rules > Equal To) based on the CHAR formula such as:
=CHAR(76)
Now, our custom icon formatting looks nicer, right?
Method 2. Add custom icons using virtual keyboard
Adding custom icons with the help of the virtual keyboard is even easier. The steps are:
- Start by opening the virtual keyboard on the task bar. If the keyboard icon is not there, right-click on the bar, and then click Show Touch Keyboard Button.
- In your summary table, select the cell where you want to insert the icon, and then click on the icon you like.
Alternatively, you can open the emoji keyboard by pressing the Win + . shortcut (the Windows logo key and the period key together) and select the icons there.
- In the Custom Icon column, enter this formula:
=IF(B2>=$G$2, $E$2, IF(B2>=$G$3, $E$3, $E$4))In this case, you need neither the character codes nor fiddling with the font type.
When added to Excel desktop, the icons are black and white:
In Excel Online, colored icons look a lot more beautiful:
This is how to use icon sets in Excel. Upon a closer look, they are capable of a lot more than just a few preset formats, right? If you are curious to learn other conditional formatting types, the tutorials linked below may come in handy.
Practice workbook for download
Conditional formatting icon sets in Excel - examples (.xlsx file)
162 comments
Is there any way that i can drag Icon set conditional formatting to other cells also ?
Hi Khalid,
You can copy the conditional formatting to other cells using the Format Painter button. You can find the detailed instructions with screenshots in the following article:
https://www.ablebits.com/office-addins-blog/excel-conditional-formatting/#copy-conditional-formatting-rules
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?
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.
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.
Thank you very much for your kind words! Your feedback is worth much more than a few dollars :)
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).
Sorry, I have never used Excel for MAC, so I am unable to help with this.
According to the following page, color scales exist in Excel for Mac 2011, but there's no mentioning of earlier versions.
https://support.office.com/en-za/article/Highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621
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
Hi Sam,
In this case, you have to create a rule for cells in column B based on a formula. For example,
Green: =$A2="YES"
Red: =$A2="NO"
Please check out the following article for the detailed instructions:
https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/
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?
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
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
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.
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.
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?
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.
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
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!
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?
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?
Hi Ken,
I have the same problem. did you ever get an answer to this?
Cheers
Rodney
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.
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
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)??
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?
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...
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.