How do I add a total to a stacked bar graph in Excel

[Greg L]

I am using Excel for some graphs in Powerpoint and we are using stacked bar
graphs. Does anyone know how to add a total on a stcaked graph? I can add
individual values but I am looking for total per bar.

 

[bj]

the simplest way is to copy the data with the totals into the chart.
select the section of the bar equal to the totals and change the axis to
secondary
add the series values and in paterns select none for fill and line. in th
elegehgend select the line for the totals and clar all.

 

[John Mansfield]

Greg,

As an example, assume you have the following in cells A1:E6. Select the
range A16 (do not select the column for the total at this time). Go to the
Chart Wizard and create a stacked column chart.

Data 1 Data 2 Dummy Total
A 4 15 2 19
B 6 14 2 20
C 5 13 2 18
D 7 23 2 30
E 8 16 2 24

To add the data labels, you will need to use a data labeling tool like Rob
Bovey’s XY Chart Labeler or John Walkenbach’s Chart Tools. Or, you can use a
macro as described here (there are also links to the sites with the above
mentioned tools at this link too):

http://www.pdbook.com/index.php/excel/adding_independent_data_labels/

Begin by clicking on the top data series in the chart (the “Dummy†series).
The external data range consists of the range E2:E6. You will want to link
the external data label range to this range. If you were using John
Walkenbach’s Chart Tools, you would select the range D26. Then you would
go to the standard toolbar and select Chart -> J-Walk Chart Tools - > Data
Labels Tab. In the Data Label Range input, enter the range E2:E6. Finally,
change all of the numbers in the “dummy†series column from 2 to zero.

 

[Jon Peltier]

Greg -

Easiest of all. Plot the individual values and the totals in the chart.
Select the Totals series, and using Chart Type on the Chart menu, change
it to a Line style. Then double click the Totals series to format it. On
the Patterns tab, choose None for lines and for markers, so it's
invisible; on the Data Labels tab, choose Show Values. Press OK to close
the dialog and apply the formats. Double click on the data labels, and
on the Alignment tab choose the Above option.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[JWheeler]

Thanks Jon. I found this technique extremely helpful.

 

[-Pieter-]

Even now very helpful!!

Greg -

Easiest of all. Plot the individual values and the totals in the chart.
Select the Totals series, and using Chart Type on the Chart menu, change
it to a Line style. Then double click the Totals series to format it. On
the Patterns tab, choose None for lines and for markers, so it's
invisible; on the Data Labels tab, choose Show Values. Press OK to close
the dialog and apply the formats. Double click on the data labels, and
on the Alignment tab choose the Above option.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Karen]

Jon:

Thank you so much...........this is brilliant!!! It works like a charm.
Even though you posted this 2 years ago or more it has saved my sanity.
Thanks again I hope you have a wonderful HOLIDAY SEASON.

Karen Martin, Charleston, WV