ID: Q152381
The information in this article applies to:
In Microsoft Excel, columns of data plotted to the same Value (Y) axis will be stacked in a single column when the stacked column subtype is selected. To achieve a combination of stacked and unstacked columns that appear side by side, both data manipulation and the use of a second Value (Y) axis is required.
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.
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.
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.
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 #" (without the
quotation marks). If only the marker is selected, it will read "Legend
Key #" (without the quotation marks). Click the Delete Key to remove
the entry.
"User's Guide," version 5.0, Part 3, "Creating Charts from Worksheet Data," Chapters 15 - 19
For more information about chart creation, click Answer Wizard on the Help menu and type:
Charts, Creating
Keywords : kbtool xlchart
Version : WINDOWS:5.0,5.0c,7.0,7.0a; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbinfoLast Reviewed: February 5, 1998