XY Area Chart: Fill Below an XY Series.

Did you ever want to fill the area under an XY chart series? You tried an Area chart, 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 under an XY series by using a combination XY - Area chart, plus a little creative treatment of the X values of the Area series and of the scaling of the secondary X axis.

I used the following sample data in A1:D11. A3:A10 holds my XY Series X data, B3:B10 holds my XY Series Y data, B2 holds my XY Series name. C2:C11 holds my Area Series X data, D2:D11 holds my Area Series Y data, D1 holds my Area Series name.

   |     A       B       C       D
---+--------------------------------
 1 |                           Area
 2 |          Values    134      0
 3 |   2.00     37      134     37
 4 |   3.27     31      219     31
 5 |   6.26     19      418     19
 6 |   7.58     42      506     42
 7 |   8.33     14      556     14
 8 |   9.79     41      654     41
 9 |  11.20     29      748     29
10 |  13.86     25      925     25
11 |                    925      0

The data in A:B is the "regular" data; that in C:D is derived with formulas. D3:D10 simply point back to B3:B10, while D2 and D11 are zero. The formula in C3 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 C3 is dragged down to fill C3:C10. C2 has the formula =C3, and C11 has the formula =C10, so the first and last values are repeated, to line up with the zeros added in D2 and D11.

Select A2:B10 and make an XY chart.

Add the Area data as a new series. Select and copy C1:D11, 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 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 series, choose Chart Type from the popup menu, and pick an area chart style.

Double click on the area 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.

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

Finally, right click on the chart, select Chart Options, and on the Axes tab, UNcheck the Secondary Y Axis box.

To fill the area between two XY series, use the modification of the above technique on this page: XY Area Chart: Fill Between XY Series.