|
Books that I own and use while developing in Excel |
|
2008 US East Coast Excel User Conference |
XY Area Chart: Fill Between XY Series.
Did you ever want to fill the area between two XY Chart series? You tried Stacked Area charts, but that didn't work; the X axis didn't scale properly, and you couldn't get the lines and markers you wanted. You can fill the area between XY series by using a combination XY - Stacked Area chart, plus a little creative treatment of the X values of the Area series and of the scaling of the secondary X axis.
Note: This is an extension of the XY Area Chart: Fill Below an XY Series technique found elsewhere on this web site.
I used the following sample data in A1:D11. A3:A10 holds my XY Series X data, B3:C10 holds my two XY Series Y data, B2 and C2 hold my XY Series names. D2:D11 holds my Area Series X data, E2:F11 holds my Area Series Y data, E1 and E2 hold my Area Series names.
| A B C D E F ---+------------------------------------------------ 1 | Area1 Area2 2 | Value1 Value2 134 0 0 3 | 2.00 13 37 134 13 24 4 | 3.27 18 31 219 18 13 5 | 6.26 15 19 418 15 4 6 | 7.58 28 42 506 28 14 7 | 8.33 10 14 556 10 4 8 | 9.79 21 41 654 21 20 9 | 11.20 23 29 748 23 6 10 | 13.86 24 25 925 24 1 11 | 925 0 0
The data in A:C is the "regular" data; that in D:E is derived with formulas. E3:E10 simply point back to B3:B10, while E2 and E11 are zero. F3:F10 show the differences between the values in column B and C, while F2 and F11 are zero. The formula in D3 is:
=INT(1000*A3/15 + 1.5)
or more generally:
=INT(1000*(A3-Xmin)/(Xmax-Xmin) + 1.5)
where Xmin and Xmax are the min and max of the X axis for the data (0 and 15 for this sample data). This formula rescales the X axis range to run from 1 to 1001 for the area chart; I could have used ROUND in place of INT and the extra .5 in the formula, but I thought of this approach first. The formula in D3 is dragged down to fill D3:D10. D2 has the formula =D3, and D11 has the formula =D10, so the first and last values are repeated, to line up with the zeros added in E2:F2 and E11:F11.
Select A2:C10 and make an XY chart.
Add the Area data as a new series. Select and copy D1:F11, select the chart, and Paste (Ctrl-V is the easiest way here, or use Paste Special from the Edit menu to add the data as new series). The two pairs of series in our chart do not line up at all, but we'll fix that in a minute.
Before you panic about the scaling, right click on the new Area1 series, choose Chart Type from the popup menu, and pick a stacked area chart style.
Now it really looks like we've broken our chart, but like most conditions, this is only temporary. Select the Area2 series, and repeat the Chart menu - Chart Type steps (or press the F4 function key to repeat the previous action).
Double click on the Area1 series, and on the Axes tab, select Secondary. At this point, on the Patterns tab I also changed the fill color of the Area series, and set the border to None.
Like before, select the Area2 series, and repeat the Double Click - Secondary Axis steps (or press the F4 function key to repeat the previous action).
Note that I've given the Area 2 series is ultimate fill color, and while Area1 will eventually be transparent, I'm keeping it pale yellow so we can watch it. I've also removed the border around both area series.
Right click on the chart, choose Chart Options, and on the Axes tab, check the Secondary Category X Axis, and select the Time Scale option. Excel automatically puts the new category axis at the top of the chart, so everything above the XY series are filled. Or it would be if we had lined it up yet.
Double click the secondary horizontal axis (top of the chart), and on the Scale tab, set the minimum to 1 and the maximum to 1001 (replace the dates), and set Base Unit = Days. On the Patterns tab, choose None for Major and Minor Tick Marks and Tick Mark Labels. The area series now fits the XY series perfectly, above the XY series.
Double click on the secondary Y axis (right side of the chart), and UNcheck the Category (X) Axis Crosses at Maximum Value box.
Now right click on the chart, select Chart Options, and on the Axes tab, UNcheck the Secondary Y Axis box.
Finally, double click on the Area1 series, and on the Patterns tab, choose None for the fill color to make it transparent. Select the extraneous legend entry (click once on the legend, then once on the text label for Area1), and press Delete to hide the entry. Don't select the space containing the clear rectangle, or you'll delete the series and have to start again (or use the CTRL+Z Undo shortcut).
