XL: How to Add Totals to a Stacked Column Chart

ID: Q151521

The information in this article applies to:

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.



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, click the Search button in Help and type:

chart

Additional query words: 5.00a 5.00c sum 


Keywords          : kbtool xlchart 
Version           : MACINTOSH:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS 
Issue type        : kbhowto kbinfo

Last Reviewed: July 30, 1999