Fill Between XY Chart Series (XY-Area Combo Chart)
by Jon Peltier
Wednesday, September 30th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Fill Below an XY Chart Series (XY-Area Combo Chart) I introduced a technique for filling the area below an XY chart. This article shows how to extend that technique to filling between two XY series.
The trick is to use a chart that combines XY and Area type series. I described this technique in XY Area Chart: Fill Between XY Series, but that protocol doesn’t work the same in Excel 2007 as in earlier versions. Fortunately by rearranging some of the steps, a process can be designed that works for all versions of Excel, from 97 (maybe earlier) through 2007. This tutorial explains the redesigned protocol.
Here is the data for this example. The XY data is in columns A through C, and the area chart data in D through F. The X values of the area chart series will be plotted on a date scale which ranges from 0 to 1000 (1000 should provide plenty of resolution for a small chart). The minimum and maximum in column D are thus 0 and 1000. The other X values are scaled to the X values of the XY series, which will be scaled between 0 and 15.
The scaling formula in D4 is
=INT(1000*A4/15+0.5)
or in the general case
=INT(1000*(A4-Xmin)/(Xmax-Xmin)+0.5)
This formula is filled down to cell D11.
Cell D3 contains the formula =D4, and cell D12 contains the formula =D11.
The values in column E are linked to the corresponding values in column B, and the values in column F are the differences between the values in columns C and B, except for the zeros at the ends of the range. The duplicate X values produce a vertical edge in the area chart, when plotted on a date scale axis. The zeros at X=0 and X=1000 automatically scale the area chart axis, which is an improvement on the older technique.
The protocol starts by selecting the XY data range A3:C11 and creating an area chart (not an XY chart as in the old protocol, since that gets messed up in Excel 2007). The Excel 2003 chart is shown at the left, the Excel 2007 chart at the right.
Continue by copying the area data in D1:F13, selecting the chart, and using Paste Special to add the copied data as a new series with categories in the first column and series names in the first row.
The second two series are moved to the secondary axis. In 2003 (left), double click on the series, and on the Axis tab, choose Secondary. In 2007 (right), right click on the series, choose Format Data Series, and on the Series Options tab, choose Secondary.
The first two series are then converted to an XY type. Right click on each series, choose Chart Type, and select the XY type and subtype you want.
Excel only adds a secondary Y axis, and we need a secondary X axis, with a date scale. In Excel 2003 (left), go to Chart menu > Chart Options > Axes tab, check the box in front of Secondary Category (X) Axis, and select the Time Scale option. Excel 2007 (right) requires two steps. First, on the Chart Tools > Layout tab, click the Axes dropdown arrow, then Secondary Horizontal Axis, and select Show Left to Right Axis. Then right click the new axis, choose Format Axis, and under Axis Type on the Axis Options tab, select Date Axis
Now hide the secondary X axis and make sure the highlighting in under (not over) the XY series. In Excel 2003 (left), step 1 is to double click on the axis, and on the Patterns tab, choose none for major and minor tick marks, tick labels, and axis lines. Excel 2003 Step 2 is to double click on the secondary Y axis, and on the Scale tab, uncheck Category (X) Axis Crosses At Maximum. In Excel 2007 (right), right click the axis, choose Format Axis, and on the Axis Options tab, choose None for major and minor tick mark types and tick labels, and on the Line Color tab, choose No Line. In 2007, the fill is already under the XY series despite the position of the X axis.
Finally, in either version of Excel, select the secondary Y axis (right edge of the chart), and press Delete.
Finally, remove the fill below the lower XY series (unless you want to fill multiple areas).
Related Posts:
- Fill Below an XY Chart Series (XY-Area Combo Chart)
- Two Color XY-Area Combo Chart – Guest Post
- Integer Values on Line Chart Category Axis
- Plot Two Time Series With Different Dates
- Line-XY Combination Charts
- Area Chart With Gap
- Horizontal Bands in the Background of an Excel 2007 Chart
- Clustered-Stacked Column Charts with Vertical Separators
- Clustered-Stacked-Column Combo Chart With Lines
- Area Chart – Invert if Negative
Posted: Wednesday, September 30th, 2009 under Combination Charts.
Comments: 14
Comments
Comment from Mike Alexander
Time: Wednesday, September 30, 2009, 1:34 pm
I love the look of this technique, but I always seem to have to explain it to managers. I’ve stopped doing it because it was causing confusion.
Maybe I need to attach a laminated reference card to each report with these charts.
Comment from Martin
Time: Wednesday, September 30, 2009, 10:01 pm
I would really like to see a version of this where the area is shaded depending on whether one line is above the other or not. (Something similar to the invert if negative option on bar charts.)
Comment from Jon Peltier
Time: Thursday, October 1, 2009, 6:33 am
Mike -
You need to bill them an extra hour for the explanation. Either it makes it worth your while to give the explanaton, or they decide they don’t really need to know.
Comment from Jon Peltier
Time: Thursday, October 1, 2009, 6:44 am
Martin -
It is possible to build a chart that shades the area differently depending on which XY series is greater, but it’s much more complicated. You need to allow for calculation of crossing points, which may occur between any two points arbitrarily. I’ve used the relevant technique in a couple other posts, Area Chart – Invert if Negative and Stacked Area Chart Challenge.
Comment from John McEwan
Time: Wednesday, October 7, 2009, 10:41 am
Jon:
I was attempting to duplicate the chart using Excel 2007 and ran into a problem when moving the two value series to the XY chart. Excel moved both Area series to the primary y axis and grayed out the selection for moving them back to the secondary y axis. Any ideas on how to get around this?
Comment from Jon Peltier
Time: Wednesday, October 7, 2009, 2:58 pm
John -
The secondary axis is for the series that stay as areas. The series that will turn into XY series will not be moved, but will remain on the primary axis.
Comment from Brook
Time: Thursday, February 25, 2010, 6:32 am
G’day John,
Brilliant post. I’ve been using this method for a while now to display the time series for two groups. I use two graphs, one on top of the other.
I’m now trying to figure out how to automate the task. Is it possible to change the transparency of the a fill series using vba? If so, how?
Cheers. And thanks for the website in general. It’s an amazing learning resource.
Comment from Jon Peltier
Time: Thursday, February 25, 2010, 6:41 am
Hi Brook -
In Excel 2003 and earlier, you’re stuck with the colors Excel offers, with no transparency. Excel 2007 introduced a lot of excessive formatting options, but a potentially useful option hidden in the visual noise is the ability to control transparency in chart fill elements.
Comment from Jon Peltier
Time: Friday, February 26, 2010, 7:37 am
Brook followed up, asking how to change transparency using VBA. She uses Excel 2007 which allows transparency of chart series but which has a partially disabled macro recorder. Fortunately I have Excel 2010 installed, with its upgraded recorder, and this is what I get for the syntax to make an area chart series partially transparent:
Sub ChangeTransparency()
With ActiveChart.SeriesCollection(1).Format.Fill
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.Transparency = 0.5
End With
End SubI don’t know why you need the .ForeColor command before the .Transparency command, except that the formatting of the series was “Automatic”, and perhaps you can’t make an automatically formatted series transparent without applying a non-automatic setting first. Excel can be line that, and Excel 2007 especially.
Comment from Brook
Time: Friday, February 26, 2010, 8:26 pm
G’day Jon,
Thank you, you’re a champion. I had a play around with the code and found this also works in excel 2007 and 2010.
Sub ChangeTransparency()
With ActiveChart.SeriesCollection(1)
.Format.Fill.Solid
.Format.Fill.Transparency = 0.5
End With
End Sub
You’re right. It seems that you can’t change the transparency of a series fill if it is on ‘automatic’, so that’s why it needs to be changed to solid or the .forecolor referred to.
I didn’t realise they reintroduced the ability to record chart macros in 2010, that’s brilliant.
Thanks again for the help!
Comment from Jon Peltier
Time: Saturday, February 27, 2010, 1:46 am
Brook -
Glad you figured it out.
The macro recorder in 2010 isn’t brilliant so much as finally putting in what they ran out of time for in 2007. Nice to have it back.
Comment from Jaime
Time: Thursday, March 18, 2010, 1:21 pm
Great explanation.
I really like the way the space just get coluored!! I have a question about it. This technique works beautifully if x axis is not really important. The gap gets filled but if we want to maintain our x axis, which in my case is really important, it is not possible to fill it since area graphics don´t keep the x axis.
How can I fill the gaps between 2 lines but still maintain the x axis as it is in the xy scatter plot?
Thanks a lot in advance for your soon response!
Comment from Jon Peltier
Time: Thursday, March 18, 2010, 4:20 pm
Jaime -
The procedure as written fills the area between XY series, without altering the X axis. It requires setting up the table as shown in the screenshot, and converting the secondary X axis to a date scale. Try again and follow the steps closely.
Comment from Jaime
Time: Friday, March 19, 2010, 6:07 pm
You´re right! I was missing some instructions… Great job with this technique!
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.