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.

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<="&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.

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<="&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.

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.

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.

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 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.

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.

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

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.

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.

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!

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.

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.

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

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.

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%)

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.

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.

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

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….

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.

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

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?

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!

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.

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

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

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.

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

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

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?

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.

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.

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).

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:

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.

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!!

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?

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.

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.

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.

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.

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.

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?

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.

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.

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%?

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?

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.

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.”

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.

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?

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.

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.

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.

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.

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:

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.

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.

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

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!

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).

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:

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?

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:

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.

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.

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.

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.

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.

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!

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.

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?

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

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.

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.

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?

If you want both the min and max of your axis scale to be labeled, you need to choose a major unit that evenly divides the span (max minus min). For your chart, 37 minus 10 is 27, which leaves you with unconventional divisions of 3 or 9.

Does the rating table represent points extracted from the data in columns A and B of the sheet? The scores in the rating table are monotonically increasing, but the data plotted in the chart are not. I don’t understand why you’re doing what you’re doing, I’m not sure I understand what you’re trying to do, and I don’t understand what the shaded bars (or rating table) are supposed to represent, since the data doesn’t follow the rating table smoothly. Shading something at the top of the chart disconnects it somewhat from the chart.

Thank you for your great work! I have a question concerning the change of colors in a bubble chart. I whish the color of the bubbles to be dependant of different value than Y. Is this possible?

I will explain what I’m trying to create. I’m making a chart of some boys who are making a lot of trouble. They are assessed each month and get a score that I put into the chart. The score is dependant on how much trouble they’ve caused. I can then easily present the general change of behavior of the boys. Besides the score it would also be nice to instantly see if they are attending school or not which also changes frequently. I’m trying to find a way to make the bubbles change color based on this condition. Lets say the bubble is blue if the boy attends school and red if the boy doesn’t. How could I set up the following example:

You can vary four parameters in a bubble chart. X and Y, obviously, as well as bubble size. Take care not to make the bubble size too quantitative: if you have more than small, medium, and large, the sizes will be difficult to tell apart.

Finally, you can vary color. Even more so than bubble size, don’t make the color be a quantitative value, but make it categorical. This way, you can use multiple series of bubbles, one for each color you need. For example, add a series for Martin, and color it red; add a series for George, color it blue. You shouldn’t use too many different colors (different series), because the chart will become cluttered, and too many similar shades will make the colors hard to distinguish.

That’s a great post! However I miss some more chart formatting functionality – formatting axis.

Is there a way to format the X axis where I present the dates, so the weekends were somehow distinguished? As the presented data drops during weekends, it would be nice to have them marked in order to avoid unnecessary questions :)

Superb!! I just needed a way to take a 15 column chart (6 near identical 15-column charts to be honest) and change the colour of one column, based on a particular X-axis label. I thought it was going to be so difficult, but this was a breeze.

Hi Jon,
I’ve used your technique to conditionally format one chart column differently, but I’m having trouble with labels. I have two series which are 100% overlapped so I get zeroes for the “hidden” series. In the formulas that make the series I tried replacing 0 with “”, but that did not help.

Maybe I’m not explaining it very well, so the following file might help http://www.mediafire.com/view/t9upz9xkq496khk/Book1.xlsx Is there a way to either (1) not show the label for the irrelevant columns or (2) format them the same as a background colour so they appear invisible?

This has been most helpful but I a am still having trouble, I want to graph a basic three bar chart, if its below zero I want the bar to have the RGB 175,29,45 (a red color), and if its above I want it to have RGB 0,131,62. I tried following the line plotting example but not sure how you go the color to change and I put my target as zero but it still seems off. Can you help? Thanks!!

Set up the data like this. The formulas for columns C and D are below the data range. Basically you want the value in column B to appear in column C if it’s negative, or in column D if it’s positive. Select the blue shaded cells (select one range, then hold Ctrl while you select the second range), and insert a chart (top chart).

Select one of the series and press Ctrl+1 (numeral one) to open the Format Series dialog or task pane. Below left is the Excel 2013 task pane, but other versions of Excel are substantially the same. Select solid fill, click the down arrow next to the Color button, and choose More Colors. The color selection dialog below right pops up, so enter your RGB values, and click OK. Repeat for all series you need to apply colors to.

Your second example is much the same. The range is slightly larger, and the formulas are different, but the process is the same.

I have a question. If, using your example, the value in column B is 8 then it appears under the range covered in column D. I want it to appear in column C. Or using another value in your demonstration, if the value in column B is 6 it appears under the range established in column E (green)and i would like it to be color coated by the range in column D (light blue). Is there any way you can help me modifying your formula so that if or when the value equals the border between the two colors, it displays the color of the upper value range instead of the lower value range? Thank you!

Hey Jon,
I would be very happy if you could help me.
I just transform a Radar Chart into a Radar/Pie chart, i would like to know if it is possible to set the colours of the deferents series by %.
Like from:
0 to 25 – red
25 to 50 – yellow
50 to 75 – blue
75 to 100 – green
I saw your post and tried to adapt for the radar/pie chart, but as i have 360 degrees os values, it got a little more complex that i thought.
Thanks Very Much.

Did you took a look at the exemple at the link?
I used your exemple to make a bar chart with de condicional formatting for the colours to change according to the values.
I try to think how could i do it the same with the Radar/Pie chart, the problem i think is that we can’t use the overlap with the diferente data, so i think maybe it is impossible! No?

Trackbacks

[…] 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 […]

[…] 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 […]

[…] 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. […]

[…] non-standard with Excel charts I always look at Jon Peltier's site. In this case I found this: Conditional Formatting of Excel Charts – Peltier Tech Blog The basic process is that you will probably need to plot three data series, not just one. Start […]

[…] Sounds like conditional formatting on a chart is what you are looking for. Try this: Conditional Formatting of Excel Charts – Peltier Tech Blog Also, you can google "conditional formatting excel chart" and there are a ton of links to check […]