You are currently offline, waiting for your internet to reconnect

How to create charts with multiple groups of stacked bars in Excel 2000

This article was previously published under Q214119
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 97 version of this article, see 77241.
Normally, data series in a stacked column or bar chart have one stackper category. However, it is possible to arrange your data so that thechart appears to have more than one stack per category.
The following sample data produces 10 different stacks with two columns each. The first column has the Y2 value stacked over the Y1 value; every other column has the Y3 value stacked over the Y4 value.

To create the chart, follow these steps:
  1. Type the following data into a new worksheet:
       A1 :  X      B1 :  Y1     C1 :  Y2     D1 :  Y3     E1 :  Y4   A2 :  X1     B2 :  1      C2 :  2      D2 :         E2 :   A3 :         B3 :         C3 :         D3 :  3      E3 :  4   A4 :  X2     B4 :  1      C4 :  2      D4 :         E4 :   A5 :         B5 :         C5 :         D5 :  3      E5 :  4   A6 :  X3     B6 :  1      C6 :  2      D6 :         E6 :   A7 :         B7 :         C7 :         D7 :  3      E7 :  4   A8 :  X4     B8 :  1      C8 :  2      D8 :         E8 :   A9 :         B9 :         C9 :         D9 :  3      E9 :  4   A10:  X5     B10:  1      C10:  2      D10:         E10:   A11:         B11:         C11:         D11:  3      E11:  4					
  2. Select cells A1:E11, and then click Chart on the Insert menu.
  3. Click the Standard Types tab. Click Column in the Chart type list, click the Stacked Column icon (the second selection) under Chart sub-type, and then click Next.
  4. In step 2 of the Chart Wizard, click the Series tab. In the Series list, click X, and then click Remove.
  5. Type the following in the Category (X) axis labels box:
  6. Click Next twice to move to step 4 of the Chart Wizard.
  7. Click As new sheet, and then click Finish.
The labels X1 through X5 appear along the horizontal axis. Y1 throughY4 appear in the legend as series names. You can extend this chart toinclude more groups by adding more data in two rows for each new group.

The number of rows per category label defines the number of stacks pergroup. To add another group of two bars, insert a row before each X label, and then add two more columns of data in the new row, such as Y5 and Y6.

The number of columns of data per row defines the number of columns ineach stack. For example, if the values for Y3 in the chart (displayed in step 1) are moved to the first row of each group, the chart still has two stacks per group. However, the first stack contains three columns and the second stack contains only one column.
For more information about chart types, click Microsoft Excel Help on the Help menu, type examples of chart types in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Article ID: 214119 - Last Review: 12/05/2015 12:29:11 - Revision: 3.2

Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbchart kbhowto kbinfo KB214119