Excel conditional formatting Icon Sets, Data Bars and Color Scales
The article provides the detailed guidance on how use conditional formatting Icon Sets, Data Bars and Color Scales in Excel 2016, 2013, 2010 and 2007. It will teach you how to extend these conditional formats beyond their common uses and apply icons based on another cell's value.
Last week we started to explorer various features and capabilities of Conditional formatting in Excel 2016, 2013 and 2010. If you have not got a change to read that article, you may want to do this know. If you already know the basics, let's move on and see what format options you have with regard to Excel's icon sets, color scales and data bars and how you can leverage them for your projects.
When you click the Conditional Formatting button, a drop-down menu offers you a handful of pre-defined formatting rules. Let's quickly go through them, one at a time.
Excel conditional formatting Icon Sets
Excel conditional formatting icon sets will help you visually represent your data with arrows, shapes, check marks, flags, rating starts and other objects.
You apply the icon sets to your data by clicking Conditional Formatting > Icon Sets, and the icons appear inside selected cells straight away.
In the screenshot above, you can see a table listing the household spendings with a green circle representing the highest value and red circles lower values. I cannot say that Excel has interpreted the data in the way I wanted, so let's customize the icon set a bit. To do this, click More Rules... underneath the icon sets list to bring up the New Formatting Rule dialog.
From here you are able to choose other icons and assign them to different values. I have decided to stick with the same icons but assign them in a different way:
- To change the order of icons, click the Reverse Icon Order button.
- To hide the cells' values, select the Show Icon Only check box.
- To assign icons based on a cell's value instead of specifying a number or percent, type the cell's address in the Value box or click the Collapse Dialog icon to select a cell, as shown in the screenshot below.
How to apply an Excel icon set based on other cells' values
A common opinion is that Excel conditional formatting icon sets can only be used to format cells based on their own values. But this is a delusion. With just a little creativity, you can assign icons depending on the values of other cells in a row or based on another cell's value, as demonstrated in the following examples.
Example 1. Apply an icon set based on other cells in a row (blanks vs. non-blanks)
Suppose, you have a list of products that includes units in stock, delivery and other details. What you want is to add different icons at the beginning of each row depending on whether other cells in the same row are blank or non-blank. That is, you will add a check mark in Column A if all cells in a row are filled in with data, an exclamation mark if some cells are blank, and a cross icon if all cells in a row are blank. To use an Excel icon set in this way, perform the following steps:
- Add an empty column in front of your table (Column A).
- Copy the following formula across Column A:
=COUNTBLANK(B2:F2), where F is the last column in your table. This formula will count the number of blank cells in each row. - Apply the conditional formatting icon set rule to Column A by clicking More Rules…, as explained above.
- In the New Formatting Rule dialog, do the following:
- Click the Reverse Icon Order button to change the order of icons.
- Select the Icon Set Only checkbox.
- For the cross icon, set >=5 (where 5 is the number of columns in your table, excluding the first "Icon" column).
- For the exclamation mark icon, set >=1.
- Set Type to "Number" for both icons.
You can verify the settings in the screenshot below:
And here is the result:
Example 2. Add icons based on another cell's value
Suppose, you have a list of orders and you need to add a green flag to column A when a certain product is delivered, i.e. there is any value in the corresponding cell in column D (Delivery Date). If there is no value in column D, a red flag should be placed in column A.
In fact, this example is very similar to the previous one, with the difference that you apply a 2-icon set based on the value in another column.
- And again, you start by adding a formula to column A. This time we will use the IF function:
=IF($D2<>"",3,1). The formula says to put 3 in column A if there is any value in the corresponding cell in column D, otherwise put 1. Naturally, you will need to replace D with the column you base your formatting on.
- Select all cells in column A, except for the column header, and create a conditional formatting icon set rule by clicking Conditional Formatting > Icon sets > More Rules...
- In the New Formatting Rule dialog, select the following options:
- Click the Reverse Icon Order button to change the icons' order.
- Select the Icon Set Only checkbox.
- For the green flag, set >=3.
- For the yellow flag, set >2. As you remember, we do not really want a yellow flag anywhere, so you set a condition that will never be met, i.e. a value greater than 2 and less than 3.
- Set Type to "Number" for both icons.
And here is the result of your effort - the green and red flag icons are added to column A based on the value in column D.
Excel conditional formatting Data Bars
You use conditional formatting Data Bars to represent data graphically inside a cell. The longest bar represents the highest value and, as you can easily guess, shorter bars represent smaller values. Data bars can help you spot large and small numbers in your spreadsheets, for example top-selling and bottom-selling products in your sales reports.
You apply data bars in Excel in the already familiar way - simply select the cells, click Conditional Formatting > Data Bars and choose the bar type you want. Once you do this, the colored bars will be placed inside the selected cells.
As you can see in the screenshot above, Excel conditional formatting data bars work very well to visually show your household spendings.
If you want to apply your own data bar style, click More Rules... as usual to bring up the Edit Formatting Rule window, where you choose the desired options:
- Place a check in the Show Bar Only checkbox to hide the cells' values and display the colored bars only.
- To select the Minimum and Maximum data types, click the little black arrow next to the corresponding box. While the Automatic type works fine in most cases, you can also choose some other data type such as percent, number, formula, etc.
- Experiment with Fill color, Border and Bar direction and click OK when you are happy with the Data Bar Preview.
Excel conditional formatting Color Scales
Using conditional formatting Color Scales, you can format your cells with two or three color gradients, where different color shades represent different cell values.
You can start by clicking Conditional formatting > Color Scales and hovering over the thumbnails of ready-to-use color scales Excel has already created for you. The 2-color green-white color scale seems to have worked pretty well too for my household budget:
But if you really want to impress someone and have the time and desire to experiment, try out the More Rules... option again and play with the colors a bit. First off, you choose either a 2-Color or 3-Color scale, then select your own colors and assign them to the minimum, maximum, and midpoint values.
This is how you use Excel's icon sets, color scales and data bars. Very intuitive and user-friendly, aren't they? If you are curious to learn other conditional formatting rule types, the following tutorials may come in handy:
In the next article we are going to investigate arcane twists of Excel conditional formatting formulas, please stay tuned!
132 Responses to "Excel conditional formatting Icon Sets, Data Bars and Color Scales"
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!
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.
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.
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.
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.
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
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.
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.
how to change the colors scale in format style more than three colors.
Hi Tajamal,
Regrettably, this is not possible.
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.
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.
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.
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
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!
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.
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 : )
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
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,
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.
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!
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.
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?
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)??
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
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.
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
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?
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,
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
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.
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?
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.
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 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
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
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/2014/06/10/excel-conditional-formatting-formulas/
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
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 :)
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.
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/2014/05/29/use-conditional-formatting-excel/#copy-conditional-formatting-rules
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
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).
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
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.
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.
How do I change the colour of a icon set I want the quadrants but in blue instead of black?
Hi Mandy,
It's not possible to change the icon's color because they are "hard coded" by Microsoft.
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
HI MARY how to separate cells in excel by color using formula by coloring the actual months from the projections (april to March)
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.
Hi,
Can we change the Icon Color based on our requirements in conditional formatting.
Regards
Ravindra Tiwari
Nope. You can only choose from the predefined icons.
how to show positive and negative number through icon sets
Hi Imran,
You can create a rule similar to this:
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%
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!
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?
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,
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
Hi Gabriel,
Hopefully, this is what you are looking for:
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
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":
I want to make a checklist, is there a way to put a checkbox before each item and have code that if there is a check mark in the checkbox it highlights whatever the cell (not row) is to the right of the checkbox?
Hello Jamie,
You can do this with the help of VBA in Excel, you can try posting your question on the VBA branches of the following websites: http://www.excelforum.com/ or http://www.mrexcel.com/
I want to use a text value to trigger a conditional formatting icon. Text value being red =(red dot), yellow = (yellow dot), green = (green dot). Can this done?
Thanks Svetlana!
You really saved my day. Great and clear info. Greetings from Bogotá. :)
I want to set a cell can display the cut-off time itself and use solid fill function to show may time left.
For example, our time start from 8:30 AM and one of our counter's cut-off time is 9:30 AM. I want this cell always display 9:30 and the data bar will become shorter base on time pass.
I have a spreadsheet and I am trying to automate a row that will show a green arrow up or red arrow down depending on the relationship between the 2 previous columns. So if the # in column A is greater than the # in column B, I want a green arrow in column C. And vice versa. Any help would be appreciated. I have played around with the Rules under Conditional Formatting, but can't seem to find one that fits what I am trying to do. (Excel 2013) Thanks.
I am trying to create an excel chart has a date column (Column A) and tasked organizations (columns F thru AB). I am trying to format it so that if the date passes, the organizations that are tasked (not all are assigned at any given point) the box will automatically go black/overdue. Until that date, the box should just be yellow or "pending". I am really confused and tried multiple sites. I am using Excel 2013.
Hi. For an Excel/PPT presentation i would need data change visualization. I would like to do it with the help of the Conditional Formatting - Icon Sets. But i would need other Icons then arrows or the given ones in Excel. Is there a known possibility to somehow import other new icons to Excel for this setting? (For Excel 2010 and 2013)
Thanks in advance.
I am using the Data Bar in Conditional Formatting and my data contains percents. I want the length of the bar to be relative to 0% to 100% but the length of the bar is always relative to the highest percentage value in my data. I've tried changing all the minimum and maximum settings but have not found a fix. Is there a way to do this?
I have conditional formatting on column N which fills different colors based on containing word on it. I want same color to fill on the column A:M based on column N color. Tried many ways to get the same color from formatted column but not succeed. Is there any way to get same color from other the cell?
Hi,
I'm trying to get icon sets with following conditions-
If values are >75% represent with Upward arrow
If values are between 30% & 75% represent with Straight line
If values are <30% represent with downward arrow
Please help me on this as i am getting a down arrow for 33% as well?
Thanks
Also, i have a formula for that particular column and it is formatted to %, the conditional formatting doesn't work. However, if i change the format to number, apply conditional formatting and then convert the column to %, then it works. Why is this happening?? What is the problem with applying conditional formatting in % formatted cells?
Hi - conditional formatting was working well on the data I pulled in to my sheet via a CSV URL feed. The publisher of the feed changed the CSV format (putting the header rows on the bottom--causing me to have to reverse the rows in order to use the first row as the column headers--no big deal there); however, now the conditional formatting no longer works. All the data / columns still the same but it's as if Excel no longer recognizes the type of data in the column--raw from the CSV or formatted manually in Excel. Seems it won't recognize the format. Could formatting be an issue why conditional formatting STOPS working?
Need to put stocks but i need put it in one cell i have one target like ...
Target : 90%
The cell : 95.3%
Then i need put stock next to 95.3% in the same cell
Thanks...
Hi
Please help me.. i try to make custom icon in conditional formatting, but it cant be save..after reopen the file, the conditional formatting formula not shown..
can you help in conditional formatting. I want to know how to fill a cell like vertical bar bottom to above
Hello, I have an excel pivot table that has pulled in data from a sharepoint dashboard file. The cell content indicates RAG of Green, Yellow, Red. Is there a way to apply conditional formatting to interpret those rags and provide icons?
hi
i have a table consist of 3 columns filled with numbers. i want 3 icons which tell me which data value is maximum, which one is minimum and which is between them?
Hi. I'm using Excel 2016. For the Icon Sets, mine are rust, gold and
muted green, not a normal red, yellow, and green as I see in the screenshots above. I tried changing the Theme, but no change. What can I change to get the more traditional colors for the traffic lights?
i am using the icon set red, yellow and green circles. is it possible to change the color of the icons, they are a bit pale according to my boss. i am in excel 2010
Dear Team,
please I would need your expertise touch here.
I have three cells (say A1, B1 and C1) .
A1 is titled "%completed"; B1 "%Tartget" and C1 "Rating/Status"
I would like to be able to compute (use nested if to determined the status of the two cells if the difference btn cell A1 and B1 meets a condition.
***This explains how the ratings are computed.
--Not Started , if value equals zero (0) (black Arrow down)
--Achieved, if %completed is greater than 90 (green Flagged)
--Partly Achieved , if %Comp greater than 50 but less than 90 (Yellow Flagged)
--Not Achieved , if %comp not equal to zero but less or equal to 50 (Red Flagged)
the above is working fine for me but there are instances where cell B1 (%target has a mark of say 48 which will not be achieved according the above creteria.
Please who can achieve this ?
worried,
Fred
Hi,
Suppose i have created a table where i will update my data daily or weekly wise.
Now i want to create another chart or table which just shows a particular color corresponding my values in the table i created but not the value itself.
Is there any formula for that in excel.
Hi
I am trying to create a spreadsheet which column A is (Issue Date)Column B (Purposed Date) and Column C (Competition Date ) there are column D (Status)
I want column D highlight (Red, Amber, Green) based on Purposed date if the issue date past purposed date highlight red on column D, and if competition date is before purposed date highlight Green on column D or else Highlight amber if Issue date is not exceed purposed date.
Hope these make sense
Hoping for your views and reply on this
Many Thanks
Emmanuel
Hi,
I want to use icon set formatting in the below scenario:
D6 - revenue 100
D7 - revenue 90
D8 - revenue 95
I want to show icon set in D6 and D7 comparing to D8. so for example D7 is less than D8 so Green up arrow but D6 is greater than D8 so red down arrow.
Any help would be hugely appreciated.
Update to problem above
Hi,
I want to use icon set formatting in the below scenario:
D6 - revenue 100
D7 - revenue 90
D8 - revenue 95
I want to show icon set in D6 and D7 comparing to D8. so for example D7 is less than D8 so Green up arrow, if it was greater than D8 red arrow down but D6 is greater than D8 so red down arrow and if it was less than D8 then green arrow up.
Any help would be hugely appreciated.
D E F G
2 Budgeted 80 90 100 100
3 Actual 80 89 97 97
Icons required in actual (Row 3)
Green-when value >= D2
Yellow-when =D2*0.8
Red-when<formula
But this only works for cell D3, and not for E3, F3 or G3. Need it for a dashboard urgent help please
I have posted only a part of data I have a load of such target V/s actual figures which are to be indicated with icons. Please suggest any formula in condition formatting icon sets so that I can drag or format paint without the error of relative references
Thanks in advance
Sorry * Yellow when >=D2*0.8
Hi there, the circular icon sets in excel is limited to three; red, green and yellow. I want to indicate the status of my Action Log as:
-In progress
-Overdue
-Completed
-Postponed
-Cancelled
More than three colours (actually 5) here, any advise on how we can get around to get 5 different coloured circular icons??
Appreciate you advise
John
Hi Svetlana,
I could really use your assistance. Is it possible to apply an Excel icon set based on the sum/difference of another cells formula =IMSUB(E2,F2)? this is for a running inventory sheet. E2 would be starting stock, F2 would be number of stock used, G2 then shows current stock. I want cell G2 to show icons according to the result of =IMSUB(E2,F2) Thank you for any help.
Hi Svetlana
I have a row of data containing formula that returns a number. I have applied the icon sets to return an icon based on <33rd,67th percentile but when I manually check the sets based on the colours that should be showing they are wrong. Is there anything obvious i should check for? I have copied and pasted values but still get the same result.
Thank you!
Hi Svetlana
I have a row of data containing formula that returns a number. I have applied the icon sets to return an icon based on <33rd,67th percentile but when I manually check the sets based on the colours that should be showing they are wrong. Is there anything obvious i should check for? I have copied and pasted values but still get the same result.
Thank you!
Hi Need help with the below.
I have created a conditional format for one of the cell where the cell, if the cell value is great than the value from the last week show Up arrow, if the same cell value is lower than the value of the previous week show Red down arrow. I am not able to copy this conditional format to the other cell as conditional format marks the initial cell value as $ so when I copy the format it still looks up the first cell from the previous week. Please help.
I wonder if i could do thing like : if cell A equal to cell B format cell C with a symbol tick, if the two cell are not equal format with a symbol X and if i have nothing in cell A or cell B format with symbol!. any help ?
hello friends,
is it possible to put formula depending on icon color,
suppose i already have green, yellow & red in row & i want one cell which represent if all green then green or any one yellow then yellow or any red then red
Can i change icon colour?
Hi
I am not an advanced Excel user but I wondered if you could help. I have a budget sheet that runs on month after month and I use filters to see what I'm spending in the current month (by filtering out all the previous months). I also assign a different category as I enter each invidual expenditure (food, petrol etc). I also have the monthly budget total for each catagory on a separate sheet.
What I would like to do is see easily, at a glance, when I have spent each budget - whether this is using icons or simply having a list of categories that decrease in value as I spend. I can do this manually by using filters and cross checking on the separate budget sheet but is there a way to do this automatically using forumulas?
Hi,
Please help me to make the icon sets for below calculation.
>85% (Green Color)
>61% to 84% (Yellow)
<61 % (Red)
How can I make?
Thanks
hello every one
I would like to use two columns.first one will have temperature data, second one will have symbols like (Suspect or good).Every cells at second column will check the data which at tle left cells at the left column and if there are persist data more than determined number( l will chose it like if there are 10 same temperature data next to next) than will write SUSPECT at the righ column cell .
thank you
I want know can conditional format a data for 4 employees in 4 different rows for 3 months in 3 different columns. If they are doing 2 3 4 hours work for 1st employee 4 3 2 for 2nd employee & 3 4 6 hours for 3rd employee & 7 8 9 hours for 4th employee
Now I want to continual format with icon set plz help
Dear Good morning,
One question... it's possible to display in a gantt chart a mailstone with flag that use color variable green red or yellow flag in function of status value ?
Many thanks in advanced
Paolo
Dear Svetlana,
I am trying to make a "% completed" column using progress bar that is dependent on "Status", which are of texts instead of numbers. My question here is, is it possible to create progress bar with "Status" e.g. "Stage 1" displaying 10% on the progress bar?
I have tried to use IF formula to relate the "% completed" column to "Status" column, i.e.; =IF($C$6, 0, IF($C$7, 10, IF($C$8, 15, IF($C$9, 20, IF($C$10, 40, IF($C$11, 50, IF($C$12, 60, IF($C$13, 65, IF($C$14, 70, IF($C$15, 80, IF($C$16, 90, IF($C$17, 95, IF($C$18, 100))))))))))))). However, the result is #VALUE!
Hi,
Need support in fetching a particular date from a set number of days of the month (30,31,28)
please see if the formula for the same is pre-fed or has to be calibrated by combining formulae.
Thanks
hi, i need to use Data Icons in Conditional Format.
if Delivery Date is Greater than or equal today , it should be GREEEN
if Delivery Date is less than today with one week , it should be YELLOW
if Delivery Date is less than 7 Days and lesser , it should be RED
pls send