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
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!
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.
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,
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!
Dear Loes,
Regrettably, I don't know any way to do this either : (
Consider using Sparklines
Regards,
Sherin
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.
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?
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 : )
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.
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.
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?
I figured it out. Thanks!
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?
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
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?
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.
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?
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.
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?
Sorry, Nathan, I don't know any way to do this. Most likely this is not feasible.
how to change the colors scale in format style more than three colors.
Hi Tajamal,
Regrettably, this is not possible.
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.
Hi Jonathan,
To my best knowledge, only one icon set can be applied at a time.
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?
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.
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!
Hi Mahesh,
You can embed the entire Excel workbook in Word, please see this link for more details:
http://www.excel-easy.com/examples/embed.html
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.
Hi,
Our support team sent you a solution by email. Hopefully, it was helpful.
I have 3 columns: CURRENT YEAR, PRIOR YEAR, CHANGE.
I want to conditionally format the Change column so that decreases are in red & increases are in green.
Additionally, I want to use Icon Sets (arrows) to show:
* green up arrow if it's an increase that would have a positive impact
* red up arrow if it's an increase that would have a negative impact
* green down arrow if it's a decrease that would have a positive impact
* red down arrow if it's a decrease that would have a negative impact
Is this doable w/ Icon Sets? Sort of a 2-dimensional icon set: direction of arrow to denote increase vs decrease, but color of arrow to denote impact this change has on bottom line.
Thanks!
Hi Kertran,
It's a bit difficult to figure out what positive / negative impact is without seeing your data. If you can send us your sample workbook at support@ablebits.com, we will be able to help you better.
Hey, I've been having trouble with the icon sets, i wonder if you could help me.
I'm want to add an icon set,the circles red, green and yellow. I want to compare changes in percentage between 1 month and another.
I need to set the icons: green for when its equal or greater to 0.01%; yellow: when there is no change 0.00%; and red the the number reflects a negative %.
Could you please advice how to do it.
Thank you, it will be of great help.
Hey Mary,
I need to know a bit more about your data structure to be able to suggest a solution. Do you have values for 2 months in 2 separate columns? Or, do you already have a column with the calculated percentage of change? Maybe you can provide a sample of your data - what exactly values you have in column A, B, C etc. You can send me a sample workbook at support@ablebits.com and I'll try to help.
Hello Sean,
Regrettably, you cannot do without an additional column. Excel icon sets allow using formulas as a condition, but do not support relative cell references that are needed in your case.
So, you can achieve the result you want in this way:
1. Create an additional column.
2. Enter the formula =AJ162-AJ161 to row 162 of the newly created column (assuming row 162 is the first row where you need the icons).
3. Copy the formula to all other cells of this column until row 175 (or the last row where you want to display the icon set).
4. Create the icon set rule for the column with the above formula. In the rule:
- Select the 'Icon Set Only' check box.
- For the green up arrow, set >=1.
- For the yellow right arrow, set >0.
- Set Type to "Number" for both icons.
Hope this helps.
Thank you Svetlana for the reply, it does help, unfortunately I have several tables (about 10), 22 columns where values may be changing randomly in each table, and nearly 300 rows of parameters (~600 total). Is adding a new column the only possible way to show a change in value or no change? I've looked up other forums where similar questions were asked, and I've seen "IF" or "AND" formulas being put in with the values in a cell, and then giving it an icon to put in if it matches the statement. Is that possible, or am I better simply going over the cells with my eyes, looking for a change, and going from there? Hope my questions aren't confusing and that there is some sort of solution other than adding columns.
Thanks,
Sean
Hi Sean,
Of course, you can use IF and AND formulas as well. The problem is that you cannot have both - a formula and your current data - in the same cell. So, an additional column will be needed anyway.
As an alternative, you can consider changing the cell's fill or font color instead of adding icons to reflect the value change. If this approach may work for you, you can create 3 rules based on the below formulas:
Green (a value increases): =$AJ162>$AJ161
Yellow (a value is the same): =$AJ162=$AJ161
Red (a value decreases): =$AJ162<$AJ161 For step-by-step instructions on creating rules with formulas, please see Excel conditional formatting formulas.
I'll give an example to hopefully make this faster.
In cell AJ161, I have a value of 200. Going down the table (each row is a new parameter for what I'm doing, something is going to be altered), the value of 200 increases by 10 each cell (AJ162, cell value = 210, etc.), until AJ166. From AJ166 to AJ169 the cell value stays at 250. The value then goes up again by 10 in AJ170. In AJ171, the value drops to 240, and continues to drop until AJ175.
What I would like, is to have Conditional Formatting Icon arrow sets, that show if a value increases, decreases, or stays the same, from the cell directly above it, rather than being given a specific number. I hope you are able to help me with this.
Many thanks!