You are currently offline, waiting for your internet to reconnect

How to Add Totals to a Stacked Column Chart

This article was previously published under Q151521
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SUMMARY
This article contains information about adding total values to a stackedcolumn chart in Microsoft Excel.
MORE INFORMATION
In Microsoft Excel, it is possible to add data labels that automaticallydisplay the values of an individual series in a stacked chart. It is notpossible to automatically add the total value of all series. However, youcan work around this behavior.

The following examples add the total values to a sample stacked columnchart. Use the example that is appropriate for the version of MicrosoftExcel that you are using.

Example for Microsoft Excel 97

To add the totals to a stacked column chart in Microsoft Excel 97, followthese 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 and7.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.
REFERENCES
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

For more information about charting in Microsoft Excel version 5.0, clickthe Search button in Help and type:

chart
5.00a 5.00c sum XL
Properties

Article ID: 151521 - Last Review: 08/17/2005 18:17:13 - Revision: 1.3

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • kbhowto kbinfo KB151521
Feedback