Creating a Chart with Stacked and Unstacked Columns

MORE INFORMATION

To achieve the desired result, the data to be stacked must be separated on the worksheet from the unstacked data by blank columns. The number of blank columns is determined by multiplying the number of unstacked data columns by 2.

For example, 1 column of unstacked data * 2 = 2 blank columns, and 4 columns of unstacked data * 2 = 8 blank columns.

Sample Data Where Columns D and E Are Completely Blank

1. Type the following into a worksheet:

          A1:       B1: 1991     C1: 1992     D1:     E1:     F1: 1993
          A2: a     B2: 0.24     C2: 0        D2:     E2:     F2: 0.74
          A3: b     B3: 0.16     C3: 0.34     D3:     E3:     F3: 0.67
          A4: c     B4: 0.81     C4: 0.72     D4:     E4:     F4: 0.5
          A5: d     B5: 0.17     C5: 0.21     D5:     E5:     F5: 0.68
          A6: e     B6: 0.84     C6: 0.58     D6:     E6:     F6: 0.67

2. Highlight all of the data including the blank columns. On the Insert menu, click Chart, and then click As New Sheet. (To create an embedded chart click On This Sheet, instead.) In the Chart Wizard Step 2 of 5, choose Combination as the chart type and click Next. In Step 3 of 5, click Next. In Step 4 of 5, choose Columns under Data Series In: and click Finish. Initially, the chart will have 2 unstacked columns and one line.
3. Because the primary (Y) axis should contain only the stacked data, the blank columns need to be plotted on the secondary (Y) axis. To change the Value axis of a blank series, select a visible series by clicking a data marker once. Do not double-click. The last argument in the series formula shown in the formula bar is the plot order number. The following is an example of a series formula for series 2 of a chart:

   =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$8,Sheet1!$B$2:$B$8,2)

4. Use the UP ARROW key on the keyboard to cycle through the series until the first blank series is selected. In the example, this is series 3 in column D.

   NOTE: You will not be able to select series 4 in column E, the second blank column. With the data used in the example, this, and all following series, are already plotted on the secondary value axis. By default, when creating a combination chart, Microsoft Excel will plot the first half of the data series to the primary value axis and the second half to the secondary value axis. If there is an odd number of data series, the extra series will be plotted to the primary value axis. Also, if the first series on the secondary value axis is empty, you will not be able to select this series.

5. On the Format menu, click Series. On the Axis tab, in the section marked Plot Series On, click Secondary Axis. This needs to be repeated for all blank columns not already plotted on the Secondary Value (Y) axis.

   NOTE: There are no other series on the primary axis in this example.

6. Because the desired chart has only columns, the line group needs to be changed to a column group. On the Format menu, click Line Group from the bottom of the list.
7. In the Format Line Group dialog box, click the Chart Type button. Change the chart type to Column, and then click Options. On the Options tab, change the Gap Width to 0, and then click OK.
8. The data to be stacked needs to be changed to the chart subtype of Stacked Column. To do this, click Column Group (not Column Group 2) on the Format menu. On the Subtype tab, select the stacked bar (the middle option), select the Options tab, and set the Gap Width to 200. Click OK.
9. Because it is not obvious which column is attached to which scale, both of the Value (Y) axes need to be set to the same scale. To do this, take note of the maximum value of the larger of the two axes, select the smaller of the two axes, and then click Selected Axis on the Format menu. On the Scale tab, change the maximum to match the larger axis, and then click OK.
10. To remove the secondary axis from the display, select the secondary axis and click Selected Axis on the Format Menu. On the Patterns Tab, select None in each of the four option boxes, and then click OK.

    NOTE: You must perform Step 9, regardless of whether the secondary axis is removed from the display.

11. To remove the blank series from the Legend, click the legend keys that have no corresponding labels. You must be sure to select the entire entry: label and marker. This can be verified by checking the name box on the far left of the formula bar. If the legend entry is properly selected, the name box will display Legend Entry #. If only the marker is selected, it will read Legend Key #. Click the Delete Key to remove the entry.