column totals on stacked column chart

[mark]

Hi.

Is there a good way to get the column totals on a stacked
column chart to appear above the columns?

All I have gotten so far is the individual pieces of the
column to show their values inside of the column, but the
user wants the column total, above the column.

Thanks.
Mark

 

[Andy Pope]

Hi,

Assuming your chart is a 2d stacked column you could use dummy data
series plotted on the secondary axis to display the total.

Create yourself a helper column on your worksheet that sums the values
of each column. Add this to the chart. At first it will be an extra
portion on each stack. Select the series and via the format dialog
change the axis to secondary. Also get it to display data label values.
Set the area pattern and border to none so the new column does not
obscure the existing data.

To remove the legend entry have a look at this explanation.
http://www.andypope.info/charts/deletelegendentry.htm

Cheers
Andy

 

[mark]

At first it will be an extra portion on each stack.

I had tried that, and have that here now.

Select the series and via the format dialog
change the axis to secondary.

I have not been able to find where to assign it to a
secondary axis. I am familiar with using a secondary
axis, but do not see the option to do that, anywhere. I
have selected the series and tried "Format Data Series".
This brings up a dialog box with 5 tabs, but none allow
the series to be assigned to a secondary axis.






Also get it to display data label values.

 

[mark]

ahhh...

to do it, I had to go through using a stacked bar, and
then tweak it.

but got it.

thanks.

 

[Andy Pope]

Mark, if you only see 5 tabs on the format dialog I would guess you had
a 3d stacked column chart.

 

[mark]

but at least for me, it ended up losing the secondary axis
when it went back to stacked bar.

the 'Axis' tab of the 'Format Data Series' dialog is only
available in 2-D. (unless you can tell me how to change
that).

When I set it back to 3-D, it loses the secondary access.

But I can go in and manually set the max on the axis to
get it to fit better.

 

[mark]

Mark, if you only see 5 tabs on the format dialog I would
guess you had a 3d stacked column chart.

yeah, that's it. I see now that you said 2-d in your
original suggestion.

this chart was 3-d.

one of the two solutions will be fine, I'm sure... either
making it 2-d, or the 3-d single axis method I found from
your suggestion.

thanks.

 

[Jon Peltier]

Instead of using the secondary axis, I like to make the total series into a line
chart. It fits on the primary axis, so you don't have to worry about synchronizing
the axes.

Of course, this won't work with a 3D chart type. Another good reason to avoid
gratuitous 3D chart effects.

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

 

[Andy Pope]

HI,

I decided to write up both methods.
http://www.andypope.info/charts/StackColTotal.htm

Cheers
Andy

 

[Jon Peltier]

It fits on the primary axis, so you don't have to

worry about synchronizing the axes.

Correction/Clarification: Once you put the Totals column series on the Secondary
axis, you can use Chart Options (Chart menu), Axes tab, and uncheck the Secondary
Value Axis tab. The series is still treated as a secondary axis series, even though
there's no secondary axis.

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