Fill Between XY Chart Series (XY-Area Combo Chart)

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 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 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 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 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 Jaime
Time: Friday, March 19, 2010, 6:07 pm

You´re right! I was missing some instructions… Great job with this technique!

Comment from John Broggio
Time: Sunday, September 5, 2010, 4:58 pm

Is it possible to calculate multiple fills between several xy-series that possibly overlap in places? I have to plot time series by males, females and persons together with their associated confidence intervals. Ideally, rather than having (up to) 9 lines that create a rather messy spaghetti effect, I’d have 3 lines plus 3 bands of colour to signify the respective CI’s (all of which may need to overlap in places).

I’ve tried the DIY approach (modifying your tutorial – we’re big fans here since discovering your site!) but can’t get my noodle around it yet.

Regards

John

Comment from John Broggio
Time: Tuesday, September 7, 2010, 4:59 pm

I think I might have cracked it – to use your http://peltiertech.com/Excel/Charts/VBAdraw.html#FilledPoly, I created a dummy data set that encapsulated both the upper and lower CI’s and filled this with a transparent colour so that the grid lines and central rates are still visible. This gives me what I wanted after a little tweaking (like removing the vertical lines of the polynomial shape where it meets the borders).

I need to neaten it up (and spend a little time coding up so that colleagues can just push a button to do the hard work) and then I’ll be able to share it if you’re interested.

Wonderful site – thanks!

John

Comment from Hugh
Time: Thursday, September 9, 2010, 10:14 am

Jon,
your insights are always excellent – thanks.

however I can not replicate this chart.. all goes fine until this step:
“The first two series are then converted to an XY type.”

when I do this the area series (second two) are automatically dumped to the primary axis – and I am unable to move the area series back to the secondary axis (the option is grayed out)

presently using excel 2007

am i missing something?

Cheers,
Hugh

Comment from Hugh
Time: Thursday, September 9, 2010, 1:18 pm

Jon,
thanks for quick reply!

office 2007 running sp2
it is for sure an x-y chart; when a line chart is selected the ‘plot series on’ area isn’t grayed out.

the moment i switch a series to x-y(scatter), i am unable to select the ‘plot series on’ for the area charts and assign them to the secondary axis – they get bumped to primary.

I just wanted to make sure there isn’t an add in i might be missing….

I’ll keep trying trial and error – looking for a reason.

btw – if i switch the value series to secondary, keeping the area series as primary I am unable to add a secondary horz. axis making it impossible to scale with date.
no worries…

Thanks again!
Hugh

Pingback from Combinatie tussen grafieken – Worksheet.nl
Time: Sunday, October 24, 2010, 7:02 am

[...] anders eens hier kijken, je moet enkel nog een beetje aanpassen Fill Between XY Chart Series (XY-Area Combo Chart) | Peltier Tech Blog | Excel Charts __________________ bsalv "Er gaat meer boven je petje dan eronder" Toon Hermans Is [...]

Comment from Justin
Time: Wednesday, November 10, 2010, 4:34 pm

Thanks for your work. This method is excellent to present data on my current project.

However, I’m having the same problem (in Excel 2007) as John McEwan above where:
“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.”

I wonder if JM has found a solution.

I’ve become comfortable with each step, replicating your example and with a portion of my data. My full data consists of 6 lines where I’d like fill between the 3 sets of 2 lines, making essentially 3 fat area-lines.

Comment from Justin
Time: Thursday, November 11, 2010, 10:46 am

I did find a work-around for my situation when only plotting 2 lines and filling between. After converting 1st series to XY chart type, Excel forces the 2nd series to plot on primary (and grays out secondary). The 1st series can still be changed to secondary plot, so this is what I did. Once the 1st series is changed to secondary plot, Excel also removes the (original, i.e., area axis) secondary axis. When the primary axis (only axis displayed) is changed to “date axis”, everything lines up properly and the chart can be formatted to look correct. So, if anyone else is having the problem I described this might work for you.

This work-around didn’t work for my 6 line (3 sets of 2 lines) situation. I tried adding the 6 lines together with 2 series, as well as 2 lines at a time with 6 series. Using my work-around resulted in the bottom 2 lines and their fill lining up properly. However, the fill for the middle 2 lines and top 2 lines did not line up vertically. It seems as if the series are not scaling properly. Almost like my lines are on linear, and the fills are on log. So, the top fill is further away from its 2 lines than the middle fill is from its 2 lines. Interesting situation, and it makes me wonder if it’s because this technique above only works for 2 lines or if it’s because I’m having the problem that forced the work-around.

Cheers!

Comment from Justin
Time: Thursday, November 11, 2010, 1:43 pm

Jon,
Thanks for the reply. I have been indeed moving the 2nd series before converting the 1st series to XY, but have tried it both ways. Here are my observations after adding data to the stacked area chart:

1. Move 2nd series to secondary axis. Success. Verified that 1st series is on primary axis.

2. Change 1st series chart type. Chart type is successfully changed, however during that single operation Excel does the following a) moves that line that I just changed to the secondary axis, b) moves all of the 2nd series to the primary axis and locks them there (grays out both axis options with primary selected). This is where the problem is and where I started the work-around.

Cheers!

Comment from Justin
Time: Thursday, November 11, 2010, 4:50 pm

Your reply disappoints me. Not that I’m disappointed you haven’t fixed my problem, but that you imply I have a problem following instructions. It should be clear based on my previous comments that I followed the technique properly, yet have confusing results. I’ve come to think that there is some kind of glitch with the software on my end, not that there is a problem with the technique or with me following instructions. Since my problem is likely unrelated to the technique, which I still find impressive and helpful, our discussion may not be a value-added contribution to the page.

Thanks again for your time.

Comment from Justin
Time: Friday, November 12, 2010, 5:17 pm

Jon,
Thanks for the clarification and I’m sorry I misunderstood you. Sure, I’ll send over the data even just for the sake of curiosity!

Cheers!

Comment from Hugh
Time: Saturday, November 13, 2010, 9:00 am

Justin (& Jon)
This is not an isolated problem, as my above posts indicate, I am (was) having the same issue, (also same as John McEwan above) ie..

“Excel does the following a) moves that line that I just changed to the secondary axis, b) moves all of the 2nd series to the primary axis and locks them there (grays out both axis options with primary selected).”

I did accomplish my objective; shading between 2-complex curves. However, it required a somewhat different implementation than the way Jon outlined – it does use the same underlying methodology though…. fundamentally, the problem(s) manifest as a scaling issues….

The key to my breakthrough was to NOT follow the instructions too closely, but to rather understand what Jon was doing with multiple-X & multiple-Y axes.

This is a very useful visualization trick!

Thanks again Jon for this post!

Cheers,
Hugh

Comment from David
Time: Wednesday, January 19, 2011, 10:15 am

Hello Jon,

You may want to specify that the first chart to be created is a stacked area, otherwise that may be confusing to your more “junior” readers: “The protocol starts by selecting the XY data range A3:C11 and creating *a stacked* area chart…”.

Thank you for your great site.
David

Comment from Dmitry
Time: Tuesday, February 8, 2011, 3:58 am

Hello Jon,

First of all, thank you very much.
I have followed your instructions and got a nice result (though I had to make some minor adjustments along the way).

But when I have tried to change size of the chart (by dragging one corner of it) this nice picture messed up: the filled areas moves separately from the lines.

What can I do to make the chart scalable?

Thanks,

Dmitry

Comment from Dmitry
Time: Tuesday, February 8, 2011, 5:02 am

Jon,

I have fixed it. The X scale for XY chart was set on Auto, I have changed Max and Min to fixed values and the whole chart is scalable now.

Dmitry

Comment from Khalid Rajab
Time: Monday, April 11, 2011, 4:33 am

I found this post with a Google search. Just want to say thanks a lot because your instructions are brilliant. Really saved me a lot of time that I would’ve had to have spent figuring this out.

Comment from Jayan
Time: Wednesday, June 29, 2011, 8:47 am

Hey, that’s powferul. Thanks for the news.

Comment from bob
Time: Thursday, August 18, 2011, 3:28 pm

Jon,

Is it possible for you to post an excel file with the steps you take or a template to use?

When I try to replicate your process I end up with a similar chart but when I resize it or change location to put the chart in its own tab, the area chart and line chart don’t line up.

thanks,

bob

Comment from wbeard52
Time: Friday, September 9, 2011, 5:30 pm

Jon,
I followed your instructions carefully in Excel 2007. Everything worked out on the sample data perfectly. When I went to apply this technique to my already created XY chart, the technique did what I wanted it to do.

The issue was upon saving the spreadsheet and reopening it, the area chart shading moved to a different part of the chart. Obviously, not what I wanted so I went to Chart Tools –> Layout –> Axes and reselected the secondary horizontal axis. The axis showed up above the chart and upon going to Format Axis saw that the Axis type went back to Text axis. Once I switched it back to date axis, the problem was solved.

The only issue I had left was the secondary horizontal axis still in my chart. The easy fix was to go to Chart Tools –> Format –> Word Art Styles and change text color to transparent. Remove the major and minor axis to return the chart back to normal. Now when I save and reopen the chart, the shading works as expected.

It was a pretty interesting error and wanted to share in case another person decided to modify an existing XY chart.

Comment from Vaishali
Time: Wednesday, September 28, 2011, 2:58 pm

Jon,

I have used this perfectly with excel 2007 in the past, but can’t get the shading right in excel 2010. the second xy series keeps moving to left corner of the chart when I add secondary horizontal axis. Please help.
Thanks.
VS

Comment from Jacob
Time: Tuesday, December 6, 2011, 9:04 pm

Hi Jon,

Thanks for sharing the great info on your site.

Question for you: I want to add and fill between multiple xy series but desire to maintain fixed values on the y-axis with variations on the x-axis (inverse of your tutorial). Do you know how I can accomplish this?

If this is unclear, my data format is:
x1 x2 x3 x4 y

where the format in the tutorial is:
x y1 y2

This is a simple procedure for an xy plot but I’m having difficulty with the area plot.

Sincerely,
Jacob F

Comment from Alex
Time: Wednesday, December 21, 2011, 9:44 am

Hi at all,

thanks for the great description.
For those who have the same problem as Justin (I had it too):
In colum D I inserted the same values as in A (and filled empty cells with 0′s)
Then followed the steps, but didn’t move the areas to secondary axis.
Converted the 2 lines to xy. After that, I changed the lower x-axis to date axis. Now, I moved all lines and areas to primary axis.
That worked for me; however, it’s not excactly the same anymore and the axis might be labeled differently.

-Alex

Comment from Alex
Time: Wednesday, December 21, 2011, 11:17 am

(I use Excel 2007)
Sry, to write again in a new post, but I just found out, how to do everything excactly as described: after you’ve changed your first line to xy, make sure that the primary, secondary attributs are still correct (my excel changed them, but i could rechange them here, one step farther, it would become grey). Then finish the procedure.

Now, it still wasn’t correct. So I randomly changed the attributs from second to primery and the other way round and after several changes, I had the right outcome (with date as primary, area as second) , don’t ask me why.

I hope this helps and maybe someone will find out one day, why it behaves that strange…

-Alex

Comment from Phil
Time: Tuesday, January 3, 2012, 4:51 am

Hi Jon,

As everyone else has said, great post!

I follow your method perfectly up until:
“Excel only adds a secondary Y axis, and we need a secondary X axis, with a date scale.”

On following these steps I get a graph which looks very different to yours. I have even resorted to using your data table, to make sure I’m following your method with an identical graph, but still get the same problem.

I am using Excel 2003 SP3… if that makes a difference?

Cheers,
Phil

Comment from Vincent
Time: Thursday, February 23, 2012, 2:51 pm

I have successfully found a workaround to the 2007 excel problem that Justin, Hugh, and others have pointed out. I thought I should share it here because it was driving crazy and I’m sure others have the same problem.

Follow Jon’s step 1&2:
“The protocol starts by selecting the XY data range A3:C11 and creating a stacked 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.”

-Instead of following the next step, convert the two “value series” into the xy chart type you want. These should be automatically be placed in the secondary axis. Now you have to change them both to the primary axis instead. By doing this, you have locked out (grayed out) the two (other) area graphs to be on the primary axis.
-To un-gray out these areas, convert the two area series to the xy chart type. These should now be placed automatically on the secondary graph. Now convert them back to stacked area charts and voila the stacked area series is on the secondary axis as you want it. Follow Jon’s instructions from there on out and you should get the same results.
-By the way, the primary axis may need to be reformatted to scale the graphs correctly. The axis should start at the minimum (0, in the example) and end at the maximum (15, in the example).
-The final step of Jon’s protocol where he tells you to “remove the fill below the xy series…” should be ignored. Instead, format the fill area (format data series/fill) to have no fill.

That should help.

Comment from Sayaz
Time: Wednesday, March 14, 2012, 9:26 am

I did this exercise as a demo and it worked well. However, I am trying to appy it to production data over a two year period and ran into a lot of difficulties. My data is tabulated in months and cubic meters (of LNG) for the period 2009 to 2011 (Jan to Dec in each year)….how can I plot the data on one chart and shade the (increased) production over the period. I have figured out how to plot the data on one chart, but shading under curves, with an x-axis having date format, makes application of the above approach difficult…thoughts? Can you help?

Comment from Hervé
Time: Monday, March 19, 2012, 1:58 pm

Hi Jon,

I don’t really understand the following :

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.

What’s the number 1000 as to do with “date” ?
And where does your 15 come from ?

Thanks for your answer

H.

Comment from Melody
Time: Monday, March 26, 2012, 6:13 pm

I tried using this technique with a graph containing 9 lines, it did not seem to work as well – and some of the differences were negative numbers. I did insert negative numbers into your worksheet and it did not work there either. Do the resulting numbers have to be positive for this to work or is there something I should be doing differently to make this work?

Thank you!

Comment from Chris
Time: Monday, April 2, 2012, 7:34 pm

Hi,

This looks good, but it does not work unless you use basic numbers and who ever works with basic numbers!!

What if you have the X Axis as a date…then your method does not work!

So essentially I found this useless.

Comment from Melody
Time: Wednesday, April 4, 2012, 4:49 pm

Update- don’t know what I did differently, but it works wonderfully now!! Great, thanks so much!

Comment from Melody
Time: Wednesday, April 4, 2012, 4:50 pm

Oh, and I did not have to do the date axis or the secondary axis… but it works.

Comment from Erik Jan
Time: Thursday, April 12, 2012, 3:14 pm

I’m trying Combo-XY-Area.xlsx (too lazy to try it myself so I thought I’d take the example and tweak it so it takes my data). So far, so good.
Using Excel2010, I see that if I size the chart vertically, all continues to fit and work fine. If I size it horizontally, it starts OK but after the chart becomes about twice as wide the fills (shapes) don’t ‘fit’ anymore… Is that ‘logical’ given your approach and what can I do about this?