Introduction
One of the most commonly used chart types is the line chart. People often ask, "How can I fill the region between two lines with a particular color?" This article describes how to create a line chart with a shaded region between the lines.
Simple Line Chart
The table below shows an optimized data range for producing a chart. The data range does not need to start in cell A1, of course, but I've done that here for convenience. The top row of the data range contains series names, the left column of the data range contains categories (X values), and the other columns of the data range contain values (Y values) for each series. The top left cell of the data range is blank, which helps Excel determine whether to use the top row and column for series names and categories in cases of ambiguous data.
The chart is created by selecting one cell within the data range, or the entire data range, and starting the chart wizard. Start the chart wizard by selecting Chart from the Insert menu, or by clicking the Chart Wizard button, which has an icon like a small column chart, on Excel's Standard command bar.
|
Sample Data
|
| A
| B
| C
|
| 1 |
|
Alpha |
Beta |
| 2 |
a |
2.16 |
6.94 |
| 3 |
b |
3.27 |
6.62 |
| 4 |
c |
4.12 |
6.40 |
| 5 |
d |
5.13 |
6.03 |
| 6 |
e |
6.67 |
6.28 |
| 7 |
f |
7.87 |
6.30 |
| 8 |
g |
8.96 |
6.56 |
| |
Simple Line Chart
|
Area Charts
An observant reader following this example will notice that there is a chart type called an Area chart, which fills the areas of a chart under or between data series. The chart below left shows Alpha and Beta plotted as a Stacked Area chart, which is the default area chart type. The region below Alpha is colored yellow, and the region between Alpha and Beta is colored turquoise.
Closer inspection of the stacked area chart shows that it is not showing Alpha and Beta independently, but it is showing the values of Beta added to (stacked on) the corresponding values of Alpha. We don't see the two series crossing, and the turquoise shaded region does not represent the difference between the values of Alpha and Beta.
Below right shows Alpha and Beta plotted in an unstacked Area chart. This shows the unstacked values of Alpha and Beta, and the difference between the two series is shown in yellow. However, over the range where Alpha is greater than Beta, the area for Alpha obscures that of Beta, so we still haven't solved the problem.
|
Stacked Area Chart
| |
Unstacked Area Chart
|
The answer is straightforward. We can compute the difference between Alpha and Beta in the worksheet, and chart this as the second series in a stacked area chart. In the table below, we've added a column of formulas under the header "Delta". Cell D2 contains the formula =C2-B2, and the formula has been filled down to D8. The series Beta has been removed from the chart, and Delta has been added. The stacked area chart now clearly shows Delta, the difference between Alpha and Beta, in a unique color. The series Delta has been added to (stacked on) the values of Alpha: where Delta is positive, the stack extends upwards from Alpha; where Delta is negative, the stack extends downwards.
|
Expanded Data
|
| A
| B
| C
| D
|
| 1 |
|
Alpha |
Beta |
Delta |
| 2 |
a |
2.16 |
6.94 |
4.78 |
| 3 |
b |
3.27 |
6.62 |
3.35 |
| 4 |
c |
4.12 |
6.40 |
2.28 |
| 5 |
d |
5.13 |
6.03 |
0.90 |
| 6 |
e |
6.67 |
6.28 |
-0.39 |
| 7 |
f |
7.87 |
6.30 |
-1.57 |
| 8 |
g |
8.96 |
6.56 |
-2.39 |
| |
Stacked Area Chart
|
|
This is almost what we want. We can format Alpha so it has no fill, and Delta so it has the color fill we want. When we try to color the borders of Alpha and Delta, the entire border of the area is colored. Not just the line tracing the series data is colored, but so are the left and right sides and the bottom of the area, and the lower areas in the stack are obscured by upper areas. So this is not our desired solution.
| |
Stacked Area Chart
|
Combination Line-Area Chart
Finally, the way to shade the region between two lines is to create a combination chart, that is, a chart that combines multiple chart types in a single chart. The chart uses the original two line series for Alpha and Beta, plus two stacked area series, one reusing the Alpha data and another series using the Delta calculations.
Start by selecting A1:C8 of the data range and creating a line chart, as described at the beginning of this article. Choose Source Data from the Chart menu, and click on the Series to bring up the dialog shown below left. Click Add to create a new series which will duplicate Alpha, click in the Name edit box and select cell B1 in the worksheet for the series name, then click in the Values edit box, and select the range B2:B8 in the worksheet for the series values. Click Add to create a new series for Delta, click in the Name edit box and select cell D1 in the worksheet for the series name, then click in the Values edit box, and select the range D2:D8 in the worksheet for the series values. The image of the chart in the Source Data dialog updates to show the appearance of the chart as these changes are made.
|
Source Data dialog, Series tab
| |
Source Data dialog with two added series
|
|
At right is the chart with four line type series, including two identically positioned Alpha series.
The added series have to be changed to area series. Select the added Alpha series, choose Chart Type from the Chart menu, choose the Area type icon on the left, then the Stacked Area icon on the right (it should already be selected, because it is the default Area chart sub-type), and click OK (see below left). Select the Delta series, and repeat this procedure; if you do it immediately after changing the Alpha series, use the F4 function key shortcut to repeat the last operation (see below right).
| |
Line Chart with Added Series
|
|
One Series Converted to Area Type
| |
Combination Chart with Two Lines and Two Areas
|
|
The chart is nearly finished. Hide the Alpha area series: double click on the series, and on the Patterns tab, select None for Area and for Border, and click OK. Format the Delta area series: double click on the series, and on the Patterns tab, select None for Border, select the fill color you want, and click OK. Hide the legend entries for the Alpha and Delta area series: select the legend, then select the text label part of the unneeded legend entry, and press Delete. Repeat for both unneeded legend entries.
| |
Colored Region Between Lines in a Chart
|
This procedure is flexible and not too complicated, but is subject to the following limitations:
- It does not work with Smoothed Line option selected, because an area chart has no such option.
- It does not apply different colors for positive and negative differences, because an area chart series can be filled with only a one color or one continuous fill effect (pattern, gradient, or picture).
Different colors can be achieved, but it requires more detailed calculations and a more complicated chart.
- It does not work directly with an XY chart.
XY charts can use a derivative of this technique, but it requires the user to follow a more complicated procedure
(see XY Scatter Area Charts for some hints).
Jon Peltier is president of Peltier Technical Services, Inc., which provides custom Excel solutions to help clients in engineering, finance, marketing, and other fields analyze, visualize, and understand information more quickly and more thoroughly.
Peltier Technical Services uses the power of Excel's VBA programming environment to integrate Excel's worksheet functionality and charting capabilities with the presentation and reporting abilities of PowerPoint, Word, and other applications. The company web site also houses one of the most extensive collections of information about Excel charting, including tutorials, tips, special techniques, and sample VBA code for generating customized charts and charting features.
|
