Comment from Jeff Weir
Time: Monday, September 28, 2009, 4:35 am

Hi Jon. Great tutorial…I fiddled around for hours at the beginning of the year trying to do this before I succeeded.

If you want a quick and dirty way to achieve nearly the same thing, you could also plot the original data as an area graph and then tell Excel that the x axis is a date axis. It then plots the data like a scatterplot, albeit it rounds down any points on the x axis to the nearest whole number. But hey…if you’re in a hurry, and your numbers are generally big enough so that you don’t need decimal precision on that axis, then it works a treat.

I’ve posted an example using your data , and I’ve overlayed it over your scatterplot to show the effect of the rounding of X axis data. As you can see, it’s not a great match, but maybe good enough for most purposes.

But wait…there’s more. If you do need decimal precision on that x axis (i.e. you want a perfect match), then here’s a crafty work around: Say your x series has two decimal points like in the example above. Multiply your x axis data by 1000 and plot it as an area chart. Because we multiplied by 1000, every point in that x series is now an integer, and so it doesn’t get rounded down as per the previous example above.

Now here’s the crafty bit…select that x axis, select ‘Date axis’ as the axis type and then make the major unit ‘fixed’ at 1000 days.

Then change the custom number formatting to ##0.

You’ll end up with a pretty good imitation of a scatterplot where once again I’ve overlayed it on top of the actual scatterplot. Perfect fit.

I’ve uploaded a spreadsheet with this example to http://cid-f380a394764ef31f.skydrive.live.com/self.aspx/.Public/Scatterplot%20vs%20area%20graph.xls

Comment from Colin Banfield
Time: Monday, September 28, 2009, 12:32 pm

Jon, superb technique…as usual. In the above description, it appears that you’re missing the piece where you convert the area chart on the primary axis to an XY chart, followed by formatting the primary axis.

Jeff’s suggestion is not without merit. I’ve done a similar thing, but with a twist. For example, add the following two rows at the top of your data set (I’ve assumed that your first “real” data points (2, 37) start at A6 and B6):

0 =NA()
=A6 =NA()
2 37
3.27 31
6.26 19
7.58 42
8.33 14
9.79 41
11.2 29
13.86 25

Using the above data, create an area chart, followed by a line chart. Format the X axis as a date axis and set max, min & major unit as in your example.

Comment from Jeff Weir
Time: Monday, September 28, 2009, 3:15 pm

In hindsight, I see my workaround doesn’t address the point in your post that an Area chart does not provide markers…to do this, I’d need to add another series using the same y data , and then change series type to line with markers. (Example at http://screencast.com/t/jWQALUXAaoBT )

Also I see that your scaling formula does the same thing as in my suggestion of multiplying the X series by 1000…the only difference being that I handle the implications of this to the x axis by using a custom number format to rescale the axis, wheras you add another axis.

Many ways to skin a graph, I suppose…

Comment from Bill Gates
Time: Monday, September 28, 2009, 4:10 pm

The one TRUE path, Jon…

Comment from Colin Banfield
Time: Monday, September 28, 2009, 7:27 pm

“The problem with the Area/Line approach (left) is that the X values are all truncated to the next lower whole number value by the line and area series, while the XY series (big and red) considers fractional parts of its inputs (right):”

Yes, I expected that there would be a difference because the date axis only gives us full days (whole numbers). However, I didn’t realize how much of a difference there was between the two approaches until I did a close comparison. Because the numbers are small, the decimals takes on significance.

The moral of this exercise is that the area/line chart is OK in the limited case of whole numbers (or if the decimals aren’t significant or if you can round up the numbers…until such time as Excel gets a date axis that can handle fractions of a day). The XY/area is the way to go if decimal precision is important. It’s certainly a great solution.

Comment from Colin Banfield
Time: Tuesday, September 29, 2009, 7:21 am

“Also I see that your scaling formula does the same thing as in my suggestion of multiplying the X series by 1000…the only difference being that I handle the implications of this to the x axis by using a custom number format to rescale the axis, wheras you add another axis.”

I ended up doing this with my version of the Line/Area chart. Since the area chart in the XY/Area combo has to be scaled to the precision of the XY chart, it amounts to the same thing…with less work.

Comment from Colin Banfield
Time: Tuesday, September 29, 2009, 8:58 am

In creating the “scaling” range from the original dataset, you could use a formula to check the extremes of the dataset to determine how to scale (multiply or divide). I tend to agree with you that line and XY charts should be combined (after all, the date axis is a whole number numeric axis). It would certainly save a lot of hassle when making combo charts.

Comment from David Montgomery
Time: Wednesday, September 30, 2009, 10:48 pm

Hi Jon,

Great post! I got the chart to work just fine, but I ended up modifying it slightly. The data that I would plot on a chart like this tends to intersect quite a bit, so I wanted to use different colors for positive and negative area.

I’m sure there is an easier way to do this (maybe in the sister post you were referring to?), but I ended up calculating the points where the lines intersected using SLOPE() and INTERCEPT(). It gets a little ugly, but I put an explanation and the workbook up on my blog if anyone is interested:

Two Color XY -Area Combo Chart

The end result turned out pretty well, and I haven’t played with it too much but all of the formulas should be scalable.

Comment from David Montgomery
Time: Thursday, October 1, 2009, 11:19 am

Thanks Jon! I think I overshot the comment section I wanted though, I meant to respond to the post above this one.

Comment from haberler
Time: Sunday, March 14, 2010, 8:52 pm

I ended up doing this with my version of the Line/Area chart. Since the area chart in the XY/Area combo has to be scaled to the precision of the XY chart, it amounts to the same thing…with less work.

Comment from Richard Groff
Time: Friday, April 23, 2010, 11:41 am

Great site! In re-creating the exercise, when I displayed the secondary-x axis the area changed from under to over and didn’t return until I deleted the secondary-y. In your tutorial you write “Now hide the secondary X axis and make sure the highlighting in under (not over) the XY series.” Mine did and I can’t figure out how to fix it at this step. Does anyone know what causes it to flip over?

Comment from Richard Groff
Time: Saturday, April 24, 2010, 6:34 am

Thanks for the write back. I finally figured my issue out. It is necessary to choose a stacked area chart from the beginning to keep the secondary chart from flipping when you turn on the secondary x-axis. If you choose side-by-side (as I originally did), it will work, but the secondary series area won’t flip to “under” until you delete the secondary y-axis. In the end you get the same result, but I like your method better as it is a better process for the more complex charts.

Thanks again for an amazing site!!

Comment from Rebekah
Time: Thursday, April 29, 2010, 9:20 am

Hi Jeff,

Thanks for a great post. I was able to follow it easily and acheive the same results that you described and illustrated. However, when I try to apply the same protocol to my data, it doesn’t turn out the same. I am not sure, but I think that I may be having some difficulties with scaling for my data.

X Y
-1 283
-0.43 249
-0.29 271
0 260
0.57 105
0.86 108

My Xmax will be 6 and Xmin is -1.

So I changed the scaling equation for Column C to =INT(1000*(A4+1)/7+.5).

I am not sure what I am doing incorrectly, but the XY series and the Area series are not matching up at the end.

Will you let me know if you fiqure out why this isn’t working for my data?

Thank you!

Comment from Rebekah
Time: Friday, April 30, 2010, 11:24 am

Hi Jon,

Thank you for your quick and helpful reply. I’ve been able to apply this to my other data as well now. Do you have a tutorial for applying this same concept to different series of data so that the shading under each series line is a different color?

Here is the same raw data that I am working with. I am trying to shade the area under the data points measured prior to treatment in a different color than the data points measured after treatment.

Weeks Prior to Treatment After Treatment
-1 283
-0.43 249
-0.29 271
0 260 260
0.57 105
0.86 108

Thank you again for your help, Jon!

- Rebekah