Stacked Line Chart.

Want to place multiple lines on a chart, but separate them so you can visualize all at once? Here is an example of a four-high stack with offsets built into the series, plus formatting tricks to dress it up. It isn't exactly four independent charts stacked up, but that's our secret.

Assume A1:I13 is populated as shown. Cell A1 is blank, and A2:A13 holds our X data. Cells B1:E1 contains headers for the four series of data, and the data are in B2:E13. All the data series range between 1.0 and 2.5. F1:I1 contain the same headers as B1:E1. Cell F2 contains the formula =B2. G2 contains the formula =2+C2. H2 contains =4+D2. I2 contains =6+E2. These formulas are dragged down to fill the F2:I13.

     Series 1  Series 2  Series 3  Series 4  Series 1  Series 2  Series 3  Series 4
 50    1.84      1.35      1.88      2.07      1.84      3.35      5.88      8.07
100    1.81      1.30      1.72      1.79      1.81      3.30      5.72      7.79
150    2.01      1.75      1.27      1.43      2.01      3.75      5.27      7.43
200    1.92      1.43      2.37      1.75      1.92      3.43      6.37      7.75
250    1.34      1.81      1.81      1.38      1.34      3.81      5.81      7.38
300    2.02      1.06      1.26      1.62      2.02      3.06      5.26      7.62
350    1.06      1.51      1.28      1.88      1.06      3.51      5.28      7.88
400    2.20      1.48      1.31      1.76      2.20      3.48      5.31      7.76
450    2.11      1.82      1.60      2.42      2.11      3.82      5.60      8.42
500    2.50      2.16      2.03      2.09      2.50      4.16      6.03      8.09
550    1.97      1.57      2.18      1.36      1.97      3.57      6.18      7.36
600    1.69      1.44      1.38      2.37      1.69      3.44      5.38      8.37

First chart the data. Select F1:I13, then hold down the control key while selecting A1:A13 (including the blank cell A1). Start the chart wizard, and make an XY Scatter chart. Format these lines the way you like. Add a Y axis title if you want.

Select the Y axis, press Ctrl-1 (numeral "one") to format it. On the Scale tab, make the scale go from 1 to 9, with a major spacing of 2 and minor spacing of 0.5 (you'll see why). On the Patterns tab, select your favorite tick style for both major and minor, and select "None" for tick labels. Click "Okay."

If they are not there by default, go to Chart menu > Chart Options, and select Major Y Gridlines. Press Okay. Select the chart's plot area, and drag the left edge to the right to give more room for the axis labels we're going to add.

Now you need a dummy series for your custom Y axis labels. The points of this series will be lined up along the Y axis, and each point will have a label representing the Y values for each of the series we plotted already. In K2:K17, enter a column of zeros (the X values for the dummy series). In L1 enter "Labels" and in L2:L17 enter the series 1.0, 1.5, 2.0, 2.5, . . . 8.5 (the Y values for the dummy series). In M2:M5 enter 1.0, 1.5, 2.0, 2.5 (one set of Y axis labels). Make sure the number format here has one decimal digit showing, so the ".0" shows. Copy M2:M5 and paste it into M6:M9, M10:M13, and M14:M17 (the labels for the other three series). The dummy series data in K1:M17 looks like this:

     Labels
0       1     1.0	
0     1.5     1.5	
0       2     2.0	
0     2.5     2.5	
0       3     1.0	
0     3.5     1.5	
0       4     2.0	
0     4.5     2.5	
0       5     1.0	
0     5.5     1.5	
0       6     2.0	
0     6.5     2.5	
0       7     1.0	
0     7.5     1.5	
0       8     2.0	
0     8.5     2.5

Select K1:L17, copy it, select the chart, go to Edit menu > Paste Special, and select the options to Add a Series, By Column, Categories in First Column, and Series Names in First Row. This adds the series along the Y axis, shown here as a series of red crosses with a red connecting line.

Now we're going to need a little help. Go to http://www.appspro.com and download Rob Bovey's XY Chart Labeler, a free addin that's very useful to spice up your charts. It labels data points, and not just on XY charts, either. Install the addin and then continue.

Select the chart. Go to the Tools menu, and select the new XY Chart Labels command, Add New Labels. Select "Labels" from the list of series, click in the Range box, and select M2:M17 with your mouse, and under Label Position, choose Left. Click Okay, and the series we added alongthe Y axis now has labels.

Readjust spacing as needed. Format the dummy series' Pattern so it has no line and no markers. Select the legend, then select the legend entry for "Labels", and press Delete.

StackedLine.zip contains a sample file and these instructions for making a stacked line chart.

A more comprehensive treatment of this technique, covering line, column, and area charts, is given in Stacked Charts With Vertical Separation.

This class of charts is known as panel charts, which are described in Panel Chart Example: Chart with Vertical Panels and Panel Charts with Different Scales.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile