Conditional Formatting of Excel Charts

It’s relatively easy to apply conditional formatting in an Excel worksheet. It’s a built-in feature on the Home tab of the Excel ribbon, and there many resources on the web to get help (see for example what Debra Dalgleish and Chip Pearson have to say). Conditional formatting of charts is a different story.

People often ask how to conditionally format a chart, that is, how to change the formatting of a chart’s plotted points (markers, bar fill color, etc.) based on the values of the points. This can be done using VBA to change the individual chart elements (for example, VBA Conditional Formatting of Charts by Value), but the code must be run whenever the data changes to maintain the formatting. The following technique works very well without resorting to macros, with the added advantage that you don’t have to muck about in VBA.

Unformatted Charts

Here is the simple data for our conditional chart formatting example.

Data for Conditional Chart Formatting Example

The data makes a simple unformatted bar chart. . .

Unformatted Bar Chart

. . . or a simple unformatted line chart.

Unformatted Line Chart

We want our charts to show different colored points depending on the points’ values.

Except for some simple built-in formats, conditional formatting of worksheet ranges requires formulas to determine which cells should take on the formatting. In the same way, we will use formulas to define the formatting of series in the charts.

We will replace the original plotted data in the line and bar charts with several series, one for each set of conditions of interest. Our data ranges from 0 to 10, and we will create series for each of the ranges 0-2, 2-4, 4-6, 6-8, and 8-10.

Conditional Formatted Bar Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

=C1&"<Y<="&C2

The formula is cell C4 is

=IF(AND(C$1<$B4,$B4<=C$2),$B4,"")

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an apparent blank. The formula is filled into the range C4:G13.

Calculated Data for Conditional Bar Chart Formatting

When the bar chart is selected, the chart’s source data is highlighted as shown.

Unformatted Bar Chart Source Data

We need to change the source data, removing column B and adding columns C:G. This is easily done by dragging and resizing the colored highlights.

Formatted Bar Chart Source Data

The chart now shows five sets of colored bars, one for each data range of interest. It’s not quite right, though, since it’s a clustered bar chart, and each visible bar is clustered with four blank values.

Partially Formatted Bar Chart

This is easily corrected by formatting any one of the bars, and changing the Overlap property to 100%. This makes the visible bars overlap with the blank bars.

Conditionally Formatted Bar Chart

Conditionally Formatted Line Chart

The data for the conditionally formatted bar chart is shown below. The formatting limits are inserted into rows 1 and 2. The header formula in cell C3, which is copied into D3:G3, is

=C1&"<Y<="&C2

The formula is cell C4 is

=IF(AND(C$1<$B4,$B4<=C$2),$B4,NA())

The formula shows the value in column B if it falls between the limits in rows 1 and 2; otherwise it shows an error, #N/A, which will not be plotted in a line chart. The formula is filled into the range C4:G13.

Calculated Data for Conditional Line Chart Formatting

When the line chart is selected, the chart’s source data is highlighted as shown.

Unformatted Line Chart Source Data

We need to expand the source data, keeping column B as a line connecting all points and adding columns C:G for the separately formatted series. This is easily done by resizing the colored highlights.

Formatted Line Chart Source Data

The chart now shows five sets of colored markers and line segments, one for each data range of interest.

Partially Formatted Line Chart

A little formatting cleans it up. Remove the markers from the original series, remove the lines from the other series, and apply distinct marker formats to the added series.

Partially Formatted Line Chart

Remove the unneeded legend entry (for the gray line) by clicking once to select the legend, clicking again to select the label, and clicking Delete.

Conditionally Formatted Line Chart

Conditional Formatting Flexibility

This simple example has formatting formulas defined based on the Y values in the chart. It is possible to define formatting based on Y values, X values, or values in another column which is not even plotted. As in worksheet conditional formatting, the only limit is your own ability to construct formulas.

This technique works on most useful Excel chart types, including bar and line charts shown here, and XY charts as shown in Conditional XY Charts Without VBA.

Peltier Tech Chart Utility

Comments

  1. Hi Jon,

    I like this technique. I have an immediate use for this approach with showing statistical outliers on xy charts.

    On the off scale or suspect points, I’ve set the points to be labelled and use the big circular point markers. It makes the point of calling out the bad in the context of the good.

    Thanks

    Bob

  2. Nice stuff. I did something similar with a scatterplot where I built myself a tool that takes X &Y data and bins it according to some z-axis variable. I made the plot flexible so I could go up to 10 z-variable bins. That allowed me the ability to add a trend line for each bin.

  3. Hi, You always have exellent posts, easy to follow and spot on. I do have a question on this one. I need to plot a comparison between forecast and baseline numbers, I’m using a bar chart. If the variance is x, the forecast bar should be red, if between x and y, it’s yellow, and over y, it’s green. I can do this with the non-vba process above, but here’s the catch. I want the forecast to overlap but not the baseline. I’ve tried using a second axis but the yellow (the middle value) overlaps with the baseline. Any thoughts??

    Thanks!

  4. Charlene –

    Are you plotting baseline and forecast together? I don’t know what you mean about overlapping.

  5. Thanks for responding. My intent is to have two bars per project, one is the baseline cost and the other the forecast cost. As you suggested, I set up three different series for the forecast cost so that the bar can be green, yellow or red based on the difference from baseline. My problem is that I end up with spaces for the empty series, and if I overlap, it also overlaps with the baseline bar (which I don’t want.) If I still am not making sense I can email you the chart in the morning, it’s on my work laptop.

    Thanks again.

  6. Charlene –

    Oh, I get it. When you overlap the conditional columns by 100%, it overlaps all columns.

    You need to follow a more intricate protocol. I’ve written a tutorial called Clustered and Stacked Column and Bar Charts. You need to set up such a chart, with one column clustered next to a stack of three coumns, and those three columns are the red-yellow-green.

    In other words, in the other article, in the table that has Q1 Actual, Q2 Actual, Q1 Budget, Q2 Budget, with the two actual values in the same row, and hte two Q2 values staggered one row lower, You would have:

    where Budget is in one row, and the red, yellow, and green conditional Forecast values are in the next. The chart looks like there’s only one of the conditional columns in each slot, because the zero-height bars don’t appear.

    Oh yeah, you might consider adjusting the red/green color scheme, for the 10% of the males in your audience with color vision deficiencies.

  7. Hi Jon – great post! How would you set something like this up for an XY Scatter that is being used as a 2×2 matrix to plot Ease (y-axis) against Benefit (x-axis)? I’m looking to do two things:
    1. Change the color of the marker based on which quadrant the data plots to
    2. Change the type of marker based on another variable that I will specify in one of the columns along with the data (e.g. Finance, HR, Sales)
    Thanks,
    Steve

  8. Joseph Souders says:

    This was a great directional idea. I applied the same logic to scatter plot charts and the results were exactly what we wanted to do. It really helped to visually see range of stores in color based on their tiering. So many uses for this approach. Glad I stumbled across it and so glad someone took the time to both show and explain the approach. Awesome article.

  9. So how would I change the colour of a value if it goes above a target value. for example:
    Week Target Value
    1 10 9
    2 10 8
    3 10 13
    4 10 5

    Thus plotted on a line graph any value above the target value would display in red and any value below target value would display in green

    Thanks

  10. Stuart –

    It’s actually easier than the line chart example above.

    Here is your data, with two more columns. The formula in column D displays a number if the value is less than or equal to the target, while the formula in column E displays a number if the value exceeds the target.

    I plotted all columns. The Target and Value columns are plotted as lines, while the Below and Above columns are plotted as distinct markers.

  11. I’m trying to display tank volumes, in gradients of green to yellow to red. Using Conditional Formatting, my data cells depict it nicely. However, how can I also see similar gradiation in my bar chart? I’m unable to get beyond manually coloring each bar to correspond to its data cell. Won’t Excel color the bars just like it colors the data cells?
    Thank you!

  12. Mac –

    If Excel colored its charts the way it colors the cells, I would not have had to write this article.

    If you need some kind of gradient, you could figure out how RGB values vary with your tank volumes, and write some kind of VBA to handle that. In fact, if you are willing to share your workbook (jon at peltiertech dot com), I could put it on my list of articles to write.

  13. Hi there,

    I was wondering if you could conditionally format 100% stacked bar charts. I am trying to create a tracking tool for NPS and would like to use the colours red , green and yellow.

    Regards,

    Richard

  14. I am trying to make a graph showing order time and how long the deliver took. I have column a “order time” and b “deliver time”. i wanted to make column c “# of guests” and in my legend i wanted to make it:

    1 GUEST
    2 GUESTS
    3 GUESTS
    4 GUESTS
    5+ GUESTS

    i was wondering if there was a way for my graph to have the different # of guests different colors when plotted. thanks.

  15. So thankful for your help! Really! This is so important to a project we are working on right now – a customer deliverable and am SO GLAD not to have to do VB.

  16. Jon,
    Is there a way to also show data labels conditionally?
    ie – the chart colors update beautifully, I have 4 bars and parameters on each bar that apply the chosen color for that range. But when I show data labels, there are 4 on each bar (I can see the logic of this) but only want 1 data label displayed per range. Can this also be done without VBA or manual work?
    Jen

  17. Novice excel 2003 user here trying to develop a line chart for the following: I have monthly data for the S&P 500 index since 1890, separated in two series, one is the average monthly close, the other is an exponential moving average of the same data. I would like to show the monthly line as one color when it crosses above the EMA, and another color when it crosses below the EMA.

    Can anyone help?

  18. Hi Jon,

    Is there a way to do conditional format on staked bar and 100% stacked bar

    Thanks for your support,

    Marc

  19. Marc –

    These routines do not care whether the chart is stacked or clustered, column or bar. It’s up to you to be creative with your formulas.

  20. Hi Jon,

    I have a stacked bar chart with 4 series. I want to be able to conditionally format each of them.
    When doing conditional formatting on stacked bar, the series that meets the formatting condition starts from 0% and not from its original position (>0%)

  21. I tried following your methods and was able to drag and resize top header but when I tried to resize data labels [blue lines] it wouldn’t let me resize that to capture the formula’s I had put in.

    Suggestions?

  22. niladri sarkar says:

    Hi Jon,

    Could You please share the excel sheet wherefrom you formatting graph ?

    Because I tried your method but unfortunately things are not working for me; I want to see where I am going wrong.

    My excel version MS2007!

  23. Hi Jon

    I loved your tip about using other objects as point markers.

    I chart wind speed and direction and was looking to place arrows on the chart points that align with the wind direction. Your tip got me started.

    I wrote a macro that used the down arrow from the autoshapes and rotated it according to the wind direction then pasted it to the chart point. I made this into an addin which I can use on any chart with a wind speed series.

    It looks great and communicates the information extremely well.

    Thanks
    Fred

  24. Jon,
    Awesome tip on this. I have a linked spreadsheet that I produce graphs off of. The problem is I have all my values in one column that I cannot change around without causing problems many other places. The autoformat already changes the colors of the values in the cell based on their values. The current chart uses column A as the serial identifier on the x axis. How can I get it to automatically change the bar color based on the autoformat color of the cell it is linked. This chart deals with individual serial aircraft and hours until inspection.

    Thanks
    Robert

  25. Robert –

    Seems like you could use the approach in VBA Conditional Formatting of Charts by Category Label.

  26. not really a comment but a request.
    i have a series of datasheets for different materials. each sheet has a significant amount of data, 1000’s, against time. so i can plot the variables against time, no problem. i also plot the data for one variable for each material, slighty harder because each series refers to a different sheet. what i would like to is displayed data values for specific x values. i can dsiplay all labels but obviously there are 1000’s.i want to be able to specify the x value, say 30 seconds, not datapoint and get a y value for all series on the chart (scatter plot). any clues gratefully recieved. thks

  27. Martin –

    Check out Excel Interpolation Formulas.

  28. Kate Rampersad says:

    I have followed your steps but when I actually expand the Y series from one column to 4 columns, it gives me separate series for each of the columns. Any advise?

  29. Kate –

    That is what is supposed to happen. Each series is then formatted uniquely. When a value changes, the value moves from one column to another based on the conditions written into the formulas.

  30. Hi Jon,

    Thank you for maintaining this great website. I’ve come here many times in the past seeking advice and tricks to improve my worksheets. This time however I do have a question that I was not able to find the answer to….

    Time is what I am working on: http://i45.tinypic.com/orrzpv.png

    What I would like is to have the portion of the column that surpasses the 100% mark to change to a different color, is this possible? For example if the column reaches 101%, the first 100% would remain green and the 1% above it would be red.

    Thanks is advance.
    Steve

  31. Steve –

    Make a stacked column chart, with two series. The green series uses formulas to plot MAX(Value,100%), and the red series on top of it uses formulas to plot MAX(Value-100%,0).

  32. Hi Jon,
    Very nice, it helped me a lot. I only have one issue left. I would like to have the value on the chart. When I do that it shows #N/A for the series that of course is empty. Can you remove that?
    Lars

  33. Lars –

    If it’s a bar chart, replace #N/A or NA() in the cells with “”.

  34. Dr Moradian MJ says:

    I have a two raws of logarythmic data and want to produce a conditional scatter plot:
    A 1.76 .31
    B 1.66 .46
    C 2.47 .63
    D 1.81 .27
    E 2.34 .32

  35. Do you have a question? Did you try and get stuck?

  36. Hi!
    I have a simple table with 10 categories and 10 numbers corresponding to it. So, the graph has 10 bars and under each bar it’s the name of the category. So… I want to automatically color the bars in different colors depending on the category. For instance, each time I write “House” as a category, the bar should get yellow. And so on..
    Anybody has an idea how to do it?

  37. Hope you understand my problem. And thanx :)

  38. Hi Jon,
    I appreciate you posting this technique, it is quite helpful. However, I am having difficulty modifying this approach to work with my data. I would like to use a line or scatterplot chart, but instead of plotting conditionally formatted markers, I would like one continuous line to show the formatting. The data are a daily time series of stream temperature and are categorized as below average, average or above average.
    Here is an example:
    Date Temperature Category (as numerical value)
    1/1/2011 14.5 1
    1/2/2011 15.3 1
    1/3/2011 16.9 2
    1/4/2011 15.6 1
    1/5/2011 14.8 1
    1/6/2011 12.3 0
    1/7/2011 12.6 0
    1/8/2011 11.3 0
    1/9/2011 14.2 1
    1/10/2011 14.8 1

    Using your suggestion but removing the markers and keeping the lines results in a connection between dates (e.g. 1/5 data point connected to 1/9). Is there a way to work around this without removing the connecting line by hand? I would appreciate any insight. Thanks!

  39. You want the lines to show the formatting? Each line segment is associated with a marker, so if you select one marker, its line segment is the one that connects it to the previous marker. I don’t see how to set this up automatically in Excel, and even doing something with VBA sounds tricky.

  40. Hi
    I have two raws of data (one x and one y) for each point and I want input condition for both i mean if X>a number or Y>another number it shows me a color:

    if x<=0 or y<=0 class one
    0<x<0.2 or 0<y<0.2 class two
    0.2<x<0,4 or 0.2<y<0.4 class three
    0,4<x<0.6 or 0.4<y<0.6 class four
    0.6<x<0.8 or 0.6<y<0.8 class five
    0.8<x<1 or 0.8<y1 or y=>1 class seven

  41. You can write formulas that refer to both X and Y, or to anything else you want.

  42. Hi Jon, congrats for your blog, it’s really really useful.

    I’ve a question about bar charts with a specific color based on value like bars in this link http://i.imgur.com/AzNkv.png.

    Do you think is possible make a charts like the attached one for a low-skilled excel user?
    How can I make the red-green bar?

    Thanks a lot for you help

    Cheers

  43. Hi Jon,
    Im having an issue with a data series i am trying to plot into a line chart. it plots all my points fine, but i need to see where there are gradient changes. i was wondering if there is any way to format the lines so that it is a positive gradient its green, negative red and unchanging as amber.
    Thanks

    Em

  44. Emma –

    Changing the format of the line segments cannot be done just with formulas, it requires VBA. An upcoming post will address this technique.

  45. Hi Jon,
    You have great instructions and examples for us to use. I hope you can help me on one step I am having trouble with. I tried to adjust the colored “blue” highlights of the “Conditional Formatted Bar Chart” from your instructions above where it says “We need to change the source data, removing column B and adding columns C:G. This is easily done by dragging and resizing the colored highlights.” For some reason I couldn’t change it so it highlights columns C:G. Please help.
    Thank you!
    Ken

  46. Ken –

    You can’t drag the colored highlight outlines? Can you change data by right-clicking and choosing Select Data? If it is a pivot chart, you can’t change its data.

  47. Hi!
    The instructions here has been a great help and I was able to conditional format the data points. I would like to know that if I want to give range in the formula -2<Y<=0, the excel is taking it as a NAME error.
    What should I do now?
    Please help as the instructions has beautifully helped me in making the plots for my datasets.

  48. A NAME error means Excel thinks something in your formula is a Name, that is, a defined range or defined formula. Perhaps you’ve misspelled a function. What is formula in the cell with the error?

  49. Hi Jon
    This is a great blog. My query, is both for something more simple yet seemingly more difficult. I’m after showing via a equal segmented pie chart (almost grapefruit like) the status of a certain contract with say 8 points to it, each with a need to show a RAG rating on it:
    Q1 = 2
    Q2 = 1
    Q3 = 1
    Q4 = 3
    etc with 1 = red 2 = amber 3 = green
    I have been trying to frantiacally find an easy solution as to how to show this, can you help?
    Thanks
    Dan

  50. You need to set up an algorithm that looks at the Qi rating to determine the format. For each segment in your chart, you need three possible values, for red, amber, and green (note that red and green are a poor choice for opposite extremes, given the prevalence of red-green colorblindness). So the data is like

    Segment A1 =1 if red, =0 otherwise
    Segment A2 =1 if amber, =0 otherwise
    Segment A3 =1 if green, =0 otherwise
    Segment B1 =1 if red, =0 otherwise
    Segment B2 =1 if amber, =0 otherwise
    Segment B3 =1 if green, =0 otherwise
    Segment C1 =1 if red, =0 otherwise
    etc.

    First use values of 1 for all segments, so you can apply formats. Then apply the formulas. The appropriately colored segment appears (with value of 1) and the others do not (with zero values).

    Smoke and mirrors.

  51. Thank you!! This was great!

  52. Thank you!! This was great! Really helped me out.

  53. On a spreadsheet someone sent me, I notice at the bottom right of a section is a small arrow you can pull down or to the left to expand the section. I”ve created a new sheet with conditional formatting to shade every other line, but how do I get that little arrow feature?

  54. Sounds like that formatted range is a Table. The formatting is Table formatting rather than conditional formatting.

    Insert Table is next to Insert Pivot Take on the Insert tab. Of course, Convert to Table would be a better name, because you’re not really “inserting” anything.

  55. Hi Jon,

    Great site!

    I’m able to resize the header, but when I try to resize the data set, it will not allow me to drag and select multiple columns.

    I’ve tried right clicking the line graph > select data, but I get this error:
    “The reference is not valid. References for titles, values, or sizes must be a single cell, row or column”

    I’m using Excel 2010. Have I missed a step?

    Thanks,
    Joe

  56. Joe –

    I don’t know which data you’re trying to resize. If it’s the data for a series (you’ve selected a series and clicked Edit, or you’ve clicked Edit above the category labels), the range can only be one row or column wide.

  57. Hi Jon, excellent site, thanks very much!

    I am having one issue I woud be grateful if anyone had any ideas on. I have added data values to my chart but sometimes, when I change either a value or a threshold in the base data, the value on the chart changes to 0% and I have to manually recreate it. It seems to happen when the value shifts from one group to another.

    Any ideas?

    Many thanks,

    David

  58. How are you changing the value?

  59. I am just typing over the number in the Y Values column – the relevant bar in the chart changes size and colour as expected but the data value changes to 0% as if it is still referencing the cell the value was in before (i.e. in columns C to G in your example).

    Thanks, David

  60. Also I am using Excel 2003, not sure if that is an issue…

  61. By data value, you mean data label, right? You need to add data labels to all series, not just selected bars, and use a number format that suppresses zeros. Whatever your number format, append three semicolons (which will suppress negatives, zeros, and text labels). For example, if you’re using percentages:

    0%;;;

  62. That’s perfect, thank you!

  63. Jose Daniel Villegas says:

    Hey ! Thanks a lot for this simple and useful technique. Now I can make a control chart with automatic alerts.
    Again, Thanks a lot, this is going to make me look very well with my boss :)

  64. Dave Dudas (from Feb post) or anyone else – I am wondering if you would share your scatterplot example? That is exactly what I need and was searching for when I found this post. I’ll try working it myself of course, but would love some help.
    Excellent stuff here everyone. Thanks for putting it out there. I’ll be sure to visit often now.

  65. Hi, Jon. I’m trying to change the colour of the bubbles in a bubble chart but I don’t know how to do it. My idea is to use the colour as the “4th dimension”. X would be business attractiveness, Y relationship attractiveness, Z (size of the bubble) amount sold, and then the colour would be the profit, being green “High Profit”; Orange “Medium Profit” and Red “Low Profit”, I also want to keep the name of the client. So far, I have X, Y, Z (Bubble size) and name of the Client but I don’t know how to move on with the Profit “thing”.
    I use Excel 2010.
    Thanks a lot in advance!!

  66. Juanjo –

    As in the line chart example above, set up three different bubble series (three sets of Y values), one for each color.

  67. Hi,

    This was extreemly useful however I have a problem. The colur of my bars in the chart change with no problem however when the value drops below the threshold my data labels display 0 instead of the true value. I.e. colour changes from green to red at value of 5 but data label only displays true value if it is 5 or above. otherwise it displays 0 is there a solution to this?

    Regards,

    Neil

  68. Neil –

    Use a number format that suppresses display of zero. For example:

    0;;;
    0.0;;;
    0%;;;

    The first item in the semicolon-delimited list shows how positive numbers are displayed, the second (blank) shows negative, the third (also blank) shows zeros. So negative values and zeros are not displayed with one of these formats.

  69. Jon,

    Great article, we have peculiar problem. We are connecting to a cube (MS SSAS) to fetch data for a dashboard which is showing set of graphs (bar,line charts). We need to depict different colors based on data obtained from cube( we are making use of slicers, as dashboard needs to dynamic based on parameters selected). We have used VBA to depict the color changes in graphs, however VBA doesn’t work when used in Excel Services. We are using Excel 2010 and we have observed that in Excel 2010, Conditional formatting icon is disabled when connected to cube. We would appreciate if you could provide us pointers on the same.

    Our problem statement is to change colors in Excel 2010 without using VBA for set of graphs (bar and line) which is fetching data from a cube. Pointers on this would really help, as we are struck.

    Thanks

  70. You could use formulas to set up different colored data points, per techniques in this article. However, the formulas have to be very smartly written to accommodate pivot tables with changing configurations.

  71. Jon,

    For some reason, when we access cube and show charts based on pivot data “conditional formatting” is getting disabled. Any inputs for the same.

    Thanks 7 Regards,
    Girish

  72. Girish –
    You mean Conditional Formatting in the worksheet? I can’t advise on this, because I don’t use cubes.

  73. Hi John,

    I need help in creating a bar graph with 4 columns of different data set and want to overlap all these 4 columns. I’ve successful with overlapping 2 columns but not with 3 or 4 columns. I need this chart for my report and will appreciate any help i can get.

    Thankyou in advance for your help!

    Maz

  74. Maz –
    You want bars in front to partially obscure the bars in back? I don’t know why you’re having trouble getting all the bars to do this, unless some are on the secondary axis. But obscuring data in this way may distort the impressions conveyed by the obscured bars.

  75. Gavin Lawrence says:

    I would like to be able to do something clever with the data points in a scatter plot: where the y-axis=Probability and the x-axis=$m. The data points represent the 80th percentile of a distribution for a number of variables.
    I want to represent the statistical uncertainty of each of the underlying distribution fro each of the data points and I felt the following uncertainty criteria might apply:-

    1. Magnitude of the data point represent by the colour of the marker – purple, blue, green, yellow,orange or red (low-high)
    2.Uncertainty measured by Standard Deviation determining the diameter of the marker
    3. Min-max range determining the thickness of the outer line.
    4. The colour of the line to reflect the Upper Quartile – Lower Quartile range using the colours in (1) above.

    Is there any way of achieving this attempt to describe the uncertainty around the data point.? Or some other solution to capture the uncertainty in the distribution?

  76. Gavin –
    A little formatting goes a long-long way, and you’re describing a lot of formatting.
    1. What’s the magnitude of a data point? If it’s X or Y value, we’re already using position to denote that. Color’s not good to represent value, other than in broad categories.
    2. Rather than diameter, which is a pain to do well, how about error bars? This also allows showing it in two directions, without having to try to scale an oval marker.
    3,4. If it’s a range that can be represented on the chart, draw it on the chart, don’t abstract it even further into line weight or color. Maybe use dashed lines, or a lighter line, for some kind of limits.

    Error bars are used in a lot of ways to try to capture uncertainty. Box plots show distribution in one direction. If you have lots of points, the density of a scatter cloud shows the distribution in the plane.

  77. Hi
    I have a table of values with X and Y number for each cell and I want to have a conditional fomatting for both x and y values.

  78. You have two values (X and Y) in each cell?

  79. Gavin Lawrence says:

    To clarify this is a common method of displaying risks in a graph. Risk is calculated as the product of probability (Bernoulli distribution) and impact (a continuous distribution). The y-axis is probability 0 to 1, x-axis for impact 0 to 50. Obviously since the product of two distributions is a third continuous distribution and you cannot plot a number of risk distributions on a scatter plot. Companies are often interested in the 80th percentile so the scatter plot locates the marker of the basis of the P() v. P(80) impact.

    This tells the reader nothing about the nature of the underlying distribution’s uncertainty in terms of standard deviation, min-max range, kurtosis, upper/lower quartile range.

    This is the challenge to demonstrate the relative value of a set of risks by P(80) value and also their distribution uncertainty.

  80. Yeah, “common method”. So you have to follow existing patterns while trying to include more information to help show the confidence in the plotted values. So some color coding is useful, and some bubble sizing also. Or maybe not.
    Can you use your error bars to show the uncertainty in the position of your points, that is, to indicate where an 80% point would be if the probability ranges between 75 and 85%?

  81. Gavin Lawrence says:

    Here is an oddity I observed:
    Marker size ranges from 2pts to 72pts
    The outer line 0pt to 144pts
    If you set the centre marker size to 72pts. Then start increasing the outer marker line.
    As outer line increases in width the cenre marker starts decreasing in size although the format tool still shows 72pts.
    When the outer line reaches 72pts width the centre marker starts to increase again until it reaches 72pts.
    When the inner markets is fully back to 72pts the width of the outer circle is 144pts.
    This is very odd, what is the cause of this?

  82. Gavin Lawrence says:

    Hi John,

    There are no constraints or limitations on how I can do the graph. Anything that is an improvement is a step forward.

  83. Bruce Williams says:

    John,

    This is exactly what I needed, and I wanted to reach out and say thanks for all your hard work! It’s very much appreciated, and I look forward to reading more of your blogs.

    Cheers,
    Bruce Williams (OE Specialist)

  84. A big show gratitude you for your post.Much thanks again. Great.

  85. This is infact a very good post to learn conditional formatting. It is helping me a lot in improving my sales chart report. i am still very new in this thing so i am taking it as a slow process to learn, but definitely getting more quality report this time than the earlier which I submitted in June last month.

  86. Hi Jon,

    I tried to adjust the colored “blue” highlights of the “Conditional Formatted Bar Chart” from your instructions above where it says “We need to change the source data, removing column B and adding columns C:G. This is easily done by dragging and resizing the colored highlights.” It won’t let me resize this area.

    I tried right-clicking and changing the data source using “Select Data,” but when I try to highlight both columns an error message pops up stating, “The reference is not valid. References for titles, values, or sizes must be a single cell, row, or column.”

    I’m using Excel 2007. Do you have any suggestions for fixing this?

  87. Divya –
    The behavior you describe is what happens if you select one series, and the data for just that one series is highlighted. Select the plot area (the box defined by the axes) or the chart area (the rectangle that everything else fits in). This will highlight the source data range for the whole chart.

  88. Hi Jon,

    Thanks for setting up such a great website. I’ve used your waterfall graph guideline multiple times and it works great. I tried following your tutorial here as well to get the results I wanted but fell a bit short. I’ve got a line chart, which I’d like to format green when above 0 on the y-axis, and red when below 0. The issue here is that for a connecting line that goes from above 0 to below 0, the color only switches once it reaches the point below 0. Is there a workaround for this?

    Thanks in advance for your help.

  89. Christophe –
    This is the way lines work in line charts and scatter charts.
    The workaround, which is totally painful, is to determine where the line crosses the axis, put a point with no marker at that intersection, and format the line segments on either side of this point.
    In an XY scatter chart, this is not too big a deal.
    In a line chart, you can’t place a point at any arbitrary X value, but only on the X axis categories. You could overlay a scatter chart, but that’s got its own issues.

  90. Is it possible to conditional format chart series colors.

    I.e If the values is 0 or “” , then the series color and the corresponding legend color should be white else the series color and the corresponding legend color should change.

    Any help would be highly appreciated

  91. Dinakar –
    If this technique isn’t applicable, try
    http://peltiertech.com/vba-conditional-formatting-of-charts-by-value/
    This uses VBA, so the code must be run any time the chart data changes.
    You can add code to hide the legend entry, or you can use data labels instead of a legend.
    Also see http://peltiertech.com/mind-the-gap-charting-empty-cells/ to learn about how Excel charts zero values and text (i.e., “”).

  92. Hi Jon,

    I have just stumbled across you posts this morning and found them very informative and have tried out the two examples,; “conditional formatted charts” and the post, dated Tuesday 27th March 2012 08:47. However I have had difficulty with the latter.

    The Chart draws as per your example except the “below” line continues across all 4 weeks ie from value 8 to value 5. I have checked and rechecked values and formulae but cannot see what is wrong. I would appreciate any assistance that you can offer.

    Thank you

  93. Peter –
    Is there a discrete data point for the “Below” series at Week 3, or is it just the line connecting the Week 2 and Week 4 points? The “Above” and “Below” series should be formatted as marker-only, without the connecting lines.

  94. Hi Jon
    I have a question regarding conditional formatting of stacked columns which I am struggling with.

    I want to create 2 stacked columns which have different segments coloured according to some indicator, as an example my data is as follow. The two columns are at points 1 and 2 on the x axis, the height of each segment is given by the y values, and the colour I want of each segment is given by some indicator, I have suggested colours blue and green here.

    x y Indicator
    1 10 Blue
    1 15 Green
    1 25 Green
    1 50 Green
    1 50 Blue
    1 50 Blue
    2 25 Blue
    2 25 Blue
    2 50 Green
    2 50 Green
    2 50 Green
    2 0 Blue

    To achieve this (and it be automatic) I believe I need to plot a total of four stacked columns, and at each point 1 and 2 on the x axis there will be two columns on top of each other (i.e. overlapped 100%) so I can then format one blue and one green. I therefore set up my data like this:

    x y1 (Green) y2 (blue)
    1 0 10
    1 15 0
    1 25 0
    1 50 0
    1 0 50
    1 0 50
    2 0 25
    2 0 25
    2 50 0
    2 50 0
    2 50 0
    2 0 0

    The problem I have is that when I plot this data, I end up with 12 segments (or series) at point 1 on the x axis, and 12 segments (or series) at point 2 on the x axis i.e. not 2 separate stacked columns which are plotted ‘on top of each other’. The total height of the stacked column ends up being much higher than I want it to be, as it is plotting all of the y values as if they belong in the same column.

    Any ideas how I can stop this from happening, and therefore plot two stacked columns on top of each other at the same point on the x axis? I read your article on clustered stacked columns but that didn’t help either, as the stacked columns still end up side-by-side instead of completely on top of each other.

    Thanks so much!
    Richard

  95. Richard –
    Your initial data indicates 6 separate bars at X=”1″ (a blue one, three green ones, and two more blue ones) and 6 separate bars as X=”2″ (two blue ones, three green ones, and one final blue one). Of course, you can’t get this chart using your existing data layout.
    I suspect you only want one green and one blue bar for each category.

  96. Hi Jon
    Many thanks for the response. That is what I wanted (as described in my first post) – basically a stacked column which had some segments coloured blue and some coloured green. My problem was getting two different stacked columns to sit on top of each other, but I have now figured this out – I had to select the series’ for the second column to be on a secondary axis which I hadn’t done before. I then played around with the transparency settings to get what I wanted, so all sorted! Thanks so much for getting back though
    All the best, and thanks for all your fantastic pages
    Richard

  97. I’m not sure how using a secondary axis would let you stack the data the way you want.

  98. Hi,

    I am having an issue… I about to create a chart that displays deviation between a reference value and a measured value. My issue is, the template my chart is in have various number of measurement points (2 to 10), and I want the chart to display the points to be measured. So.. just catching up here… i.e. I have 5 points to be measured, but my chart displays 10 points, the first 5 with the correct deviation, and the last 5 points are suited at zero. I do not want the 5 points at zero to be displayed on chart. How to do?
    Thanks in advance!

  99. Bo –
    Here’s how to make your chart dynamic: Dynamic Charts, Easy Dynamic Charts Using Lists or Tables, Dynamic Chart Review.

  100. Hello Jon,
    I hope you can help. I am a novice Excel user with no VBA skills and I am trying to create a 3D Bubble Chart with 4 data dimensions where I would like one of the dimensions to have conditional formatting depending on the cell’s value. The data categories (columns) are as follows:
    Customer Name Discount % Volume Revenue Margin %

    I would like x-axis to be volume, the y-axis to be discount percentage (discounts are represented by negative percentage values – e.g. -20% is a greater discount than -10%), the bubble size to be revenue and and the bubble colour to be a different colour depending on the margin percentage (e.g. margin less than 20% is red, less than 30% is yellow, less than 40% is green and 40% or more is blue).

    Visually, customers with low volume and deep discounts would end up in the lower left quadrant – undesirable, especially if they are in red, reflecting low margin).

    I would appreciate any help you could provide.

    Thank you

  101. Henry –

    This is pretty much the same as above. You need to separate your data into separate columns, so the points are plotted as separate series.

    Here’s some sample data:

    The bubble data is in columns A:C (X values, Y values, and bubble sizes):

    We’ve split the bubble data into four additional columns using the following formulas:

    D2 (filled down to D17):
    =IF(C2<0.2,C2,NA())

    E2 (filled down to E17):
    =IF(AND(C2>=0.2,C2<0.3),C2,NA())

    F2 (filled down to F17):
    =IF(AND(C2>=0.3,C2<0.4),C2,NA())

    G2 (filled down to G17):
    =IF(C2>=0.4,C2,NA())

    Make a chart using the first three columns:

    Right click on the chart, choose Select Data, click on the only series in the list on the left, and click Edit. The dialog looks like this:

    I’ve already changed the series name to cell D1, and moved the bubble data range from column C to column D. Make sure the X, Y, and bubble size data starts in row 2, not row 1.

    By changing the data, I now only show the points that have numerical values in column D:

    Click the Add button in the Select Data dialog, and add a new series that uses the same X and Y data range, and series name and bubble size from column E. Repeat with columns F and G. Click OK, then format the series:

  102. Thank you so much, Jon. This has helped very much. Is it possible to add one more dimension/data set which would be revenue that would drive the size of the bubble while the margin would only conditionally effect the colour of the bubbles?

    Also, is there a way to label each bubble with the applicable customer name?

    Thank you so much for your assistance.

  103. Henry –

    Minor differences

    Columns A-D contain Volume, Discount, Revenue, and Margin. Margin isn’t used in the chart, but it is used to decide which values of Revenue appear in which conditional column. Columns E-H contain the conditional data, with these formulas:

    E2 (filled down to E17):
    =IF(D2<0.2,C2,NA())

    F2 (filled down to F17):
    =IF(AND(D2>=0.2,D2<0.3),C2,NA())

    G2 (filled down to G17):
    =IF(AND(D2>=0.3,D2<0.4),C2,NA())

    H2 (filled down to H17):
    =IF(D2>=0.4,C2,NA())

  104. To add company labels, put the company names into another column, then if you’re using Excel 2013, add data labels to the points using the labels from cells option, and select these labels in the worksheet. If you don’t have Excel 2013, download and install Rob Bovey’s Chart Labeler from http://appspro.com, and use it to label the points.

  105. Thank you, Jon. Your advice was invaluabe and I very much appreciate your help. I don’t mean to monopolize your time; however, I do have one more query related to this chart. I have one other column containing the customer’s name which I did not include in the chart data set; however, if possible, I would like to include the customer name and their revenue in the data label.

    From the Data Label options, I am able to include any of the 4 data cateogires; however, i cannot include the customer name. I can create the cell reference to the customer name, but then I am unable to include a second cell reference to their associated revenue. Is there any way to include two or more cell references into a data label? I did try to combine the two values in a separate cell, but then I lose the currency format of the revenue and I would prefer to keep it, if possible.

    If there is any way to accomplish this, I would, once again, greatly appreciate your guidance.

    Thank you

  106. Sorry, Jon, I did not see your additional response on the labels prior to sending my follow-up question. I will try that; however, our corporate guidelines may not allow me to download unsupported appkications onto my desktop.

    Thanks again for all your help.

  107. Hi Jon,
    I actually found a work-around. It may not be pretty, but I inserted a column after the customer name column and then referenced that new column to the revenue column. I was then able to select the two consecutive cells in the row which included the customer name and their revenue. My data table has a couple of extra columns now, but I guess I could always hide them, if necessary.

    Thanks, again, for all your great help.

  108. Hi, I would like to know that how can I format/change the color of a single value in axis label. For eg – If the axis label consists name of all the months and I want to Change color of one single month like lets say June.

  109. Here is how to format axis labels individually: Individually Formatted Category Axis Labels.

  110. Jon, is there a simple way of changing the formatting for one data point in a bar chart (without VB). I have 3 bar series(on same y axis) in the chart and don’t want to overlap just want the data point of interest to be different than the others. Obviously can manually format but that’s ok for a few not for many hundreds!

  111. Greg –
    There are no “simple” ways to do this. The article above shows how to set it up ahead of time, but if you already have a lot of charts not set up this way, it’s too late. VBA may give you the power and flexibility to hunt through all your charts for data that meets particular criteria for highlighting.

  112. I am very unskilled at VBA and reading your instructions sent me into panic mode. I have a simple and basic graph that shows a line for my goal and a bar for each month. I am wanting to have my YTD average show red if we are above our max goal and green if we are below our max goal. Can you help me with this?

  113. Hi Jon, I’m hoping you can help and I apologise if you have answered this elsewhere. Is it possible to have the range 0<Y<=4 and 9<Y<=12 so that any number that falls into this range is added into the applicable cell.The other ranges I have are 7<Y<=8 etc. I have data that sometines falls into the upper and lower ranges but these only need to be the same colour as the 4 range for the lower number and the 8 range for the higher number. I would prefer not to have to add columns to cover the ranges 0 – 1, 1 – 2, 2 – 3 and 3 – 4. Any help would be appreciated

  114. Jenny –

    You only need to add one column for each distinct format. Notice in the screenshots, there are endpoints in C1:G2, and these are set up in ranges two units wide (8-10, 6-8, etc.). You can use as many of these as you need, and set them up however you want, and you can use other formulas if they make more sense. This was just an example with a set of guidelines.

  115. MICHAEL COLLINS says:

    I WOULD LIKE TO SET UP TRAFFIC LIGHT BACKGROUND ON A RADAR CHART SO LESS THAN 25% IS RED AND ORANGE FROM 25- 75% AND THEN GREEN FROM 75% -100% – ON AN 8 AXIS SPIDER RADAR DIAGRAM. CAN YOU SET THAT UP

  116. 1. Your caps lock button is broken.
    2. As I’ve written elsewhere on this blog, radar charts do not effectively show data.
    3. The formulas to split the data into three series are pretty much the same as those in the article and in other follow up comments.

  117. URGENT: How do I change the colors rendered when I apply the conditional data range to the bar graph? I need to use a specific color scheme based on my company’s protocol, and while this method does allow me to apply different conditional coloring to the bars, there is no info here on how to change the colors the data automatically maps to when following your method. How do you change these colors?

  118. Jack –

    You format these colors just like any other chart feature. Select the object, press Ctrl+1 to open the Formatting dialog or task pane, and choose the colors you need.

Subscribe without commenting

Trackbacks

  1. [...] I found this tutorial on Conditional Formatting in a chart using numerical values Conditional Formatting of Excel Charts | Peltier Tech Blog | Excel Charts However I have a series of ticket types and I want to put a trend chart together that has the [...]

  2. [...] Hi Welcome to the board There are several approaches to the problem, using charts or shapes, and formulas or vba. The way I'd choose is to use a conditional formatted chart. I would do it the following way. 1 – use a scatter chart to draw the circle. I would test to determine the number of points per segment. I guess 10 points are enough, but maybe for a very big circle you could use 20 or 100. Just try with 10 and if you still think it's not round enough add more 2 – split the series in 2, one series with the segments that correspond to the Yes and the other with the other segments Maybe this article from Jon Peltier will help: Conditional Formatting of Excel Charts | Peltier Tech Blog | Excel Charts [...]

  3. [...] Hi Have a look at Jon Peltier's site. Conditional Formatting of Excel Charts | Peltier Tech Blog | Excel Charts [...]

  4. [...] See if this helps Conditional Formatting of Excel Charts | Peltier Tech Blog | Excel Charts [...]

  5. [...] if these links help. Conditional Formatting of Excel Charts – Peltier Tech Blog Creating an Excel Chart with Conditional Formatting Bar Chart: Conditional [...]

  6. […] This may help you Conditional Formatting of Excel Charts – Peltier Tech Blog […]

  7. […] Originally Posted by Herbiec09 Good evening All, Is it possible for the colour of a bar on a bar graph to be determined by the size of the bar e.g. values between 0 & 2 are green, values between 3 and 5 are orange etc? If so, how do i do it? Thank you Herbiec Hi have you tried Peltier's solution? link here. specifically this method here: link. […]

  8. […] This may help you as well Conditional Formatting of Excel Charts – Peltier Tech Blog […]

  9. […] Simple Conditional Chart Example 1 – Simple Conditional Chart Example 2 […]

  10. […] could apply the technique described in Conditional Formatting of Excel Charts. Mark a data point with an entry in another column, then make your formulas indicate marked points […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.