XL: How to Add Totals to a Stacked Column Chart

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 97 for Windows

SUMMARY

This article contains information about adding total values to a stacked column chart in Microsoft Excel.

MORE INFORMATION

In Microsoft Excel, it is possible to add data labels that automatically display the values of an individual series in a stacked chart. It is not possible to automatically add the total value of all series. However, you can work around this behavior.

The following examples add the total values to a sample stacked column chart. Use the example that is appropriate for the version of Microsoft Excel that you are using.

Example for Microsoft Excel 97

To add the totals to a stacked column chart in Microsoft Excel 97, follow these steps:

1. Enter the following sample data in a worksheet:

         A1: Widget 1     B1: 4897      C1: 2990    D1: 3889      E1: 2098
         A2: Widget 2     B2: 1231      C2: 1232    D2: 3123      E2: 2543
         A3: Widget 3     B3: 2019      C3: 4323    D3: 1232      E3: 3122
         A4: Widget 4     B4: 3245      C4: 1234    D4: 4214      E4: 2421
         A5:              B5: 11392     C5: 9779    D5: 12458     E5: 10184
         A6:              B6:           C6:         D6:           E6:
   
   

2. Select cells A1:E4.

3. On the Insert menu, click Chart.

4. Under Chart Type, click Column. Under Chart Sub-Type, click the second type in the first row. Click Finish.

5. Select cells B5:E5 on the worksheet. On the Edit menu, click Copy. Click the Chart. On the Edit menu, click Paste.

   This step adds a fifth series of data to the chart.

6. Double-click the new data series. Click the Patterns tab. Click None under Border and Area.

7. Click the Data Labels tab. Under Data Labels, click to select Show Value, and then Click OK.

   This step adds column totals from cells B5:E5 on the worksheet to the top of the columns on the chart.

8. Click to select the legend. Press the RIGHT ARROW key until the label for the new data series is selected. Then, press DELETE.

   This step removes this entry from the legend.

Example for Microsoft Excel 5.0 and 7.0

To add the totals to a stacked column chart in Microsoft Excel 5.0 and 7.0, follow these steps:

1. Enter the following data in a worksheet:

          A1: Widget 1     B1: 4897      C1: 2990    D1: 3889      E1: 2098
          A2: Widget 2     B2: 1231      C2: 1232    D2: 3123      E2: 2543
          A3: Widget 3     B3: 2019      C3: 4323    D3: 1232      E3: 3122
          A4: Widget 4     B4: 3245      C4: 1234    D4: 4214      E4: 2421
          A5:              B5: 11392     C5: 9779    D5: 12458     E5: 10184
          A6:              B6:           C6:         D6:           E6:
   
   

2. Select cells A1:E4.

3. On the Insert menu, click Chart, and then click As New Sheet. Click Next.

4. Click Column, and then click Next.

5. Click the third chart type, and then click Finish.

6. Select cells B5:E5 on the worksheet. On the Edit menu, click Copy. Click the Chart. On the Edit menu, click Paste.

   This step adds a fifth series of data to the chart.

7. Double-click the new data series. On the Format menu, click Selected Data Series, and then click the Patterns tab. Click None under Border and Area.

8. Click the Axis tab. Click Secondary Axis under Plot Series On. Click OK.

9. On the Insert menu, click Data Labels. Under Data Labels, click to select Show Value. Click OK.

   This step adds column totals from cells B5:E5 on the worksheet to the top of the columns on the chart.

10. Double-click the secondary y axis (this is the axis on the right side of the chart). On the Patterns tab, set all of the option buttons to None. Click OK.

    This step eliminates the secondary axis.

11. Click the legend. On the Edit menu, click Clear All.

For more information about creating charts in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

   creating and formatting charts

   chart