Conditional Formatting of Excel Charts
by Jon Peltier
Monday, February 13th, 2012
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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.
The data makes a simple unformatted bar chart. . .
. . . or a simple 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<="&C2The 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.
When the bar chart is selected, the chart’s source data is highlighted as shown.
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.
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.
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 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<="&C2The 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.
When the line chart is selected, the chart’s source data is highlighted as shown.
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.
The chart now shows five sets of colored markers and line segments, one for each data range of interest.
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.
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.
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.
Related Posts:
- VBA Conditional Formatting of Charts by Category Label
- VBA Conditional Formatting of Charts by Value
- VBA Conditional Formatting of Charts by Series Name
- VBA Conditional Formatting of Charts by Value and Label
- Conditional XY Charts Without VBA
- Split Data Range into Multiple Chart Series without VBA
Posted: Monday, February 13th, 2012 under Formatting.
Comments: 19
Comments
Comment from Bob
Time: Wednesday, February 15, 2012, 6:33 pm
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
Comment from Dave Dudus
Time: Saturday, February 18, 2012, 10:04 pm
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.
Comment from Charlene
Time: Thursday, February 23, 2012, 12:59 pm
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!
Comment from Jon Peltier
Time: Thursday, February 23, 2012, 3:55 pm
Charlene -
Are you plotting baseline and forecast together? I don’t know what you mean about overlapping.
Comment from Charlene
Time: Thursday, February 23, 2012, 11:15 pm
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.
Comment from Jon Peltier
Time: Friday, February 24, 2012, 3:57 pm
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.
Comment from Steve
Time: Wednesday, February 29, 2012, 5:27 pm
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
Comment from Joseph Souders
Time: Friday, March 23, 2012, 3:13 pm
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.
Comment from Stuart Gibson
Time: Tuesday, March 27, 2012, 5:54 am
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
Comment from Jon Peltier
Time: Tuesday, March 27, 2012, 8:47 am
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.
Comment from Mac
Time: Monday, April 2, 2012, 7:04 pm
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!
Comment from Jon Peltier
Time: Monday, April 2, 2012, 9:24 pm
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.
Comment from Richard
Time: Wednesday, April 4, 2012, 9:46 pm
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
Comment from katie
Time: Saturday, April 7, 2012, 4:18 pm
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.
Comment from Jen Graves
Time: Tuesday, May 1, 2012, 11:04 am
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.
Comment from Jen Graves
Time: Wednesday, May 2, 2012, 8:52 am
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
Comment from Glenn
Time: Monday, May 7, 2012, 2:25 pm
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?
Comment from Marc
Time: Tuesday, May 15, 2012, 2:49 am
Hi Jon,
Is there a way to do conditional format on staked bar and 100% stacked bar
Thanks for your support,
Marc
Comment from Jon Peltier
Time: Tuesday, May 15, 2012, 12:09 pm
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.
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.
Read the PTS Blog Comment Policy.