Problem with stacked column chart

[cherman]

I'm using Excel 2003. I have basically 2 sets of data, the 1st is a total
count of people and the other set is a breakdown of the total count into 5
subcategories. I have a stacked column chart with a weekly category axis that
has the 5 categories and the total all in one column per week. However, the
count is off because it's adding the total and the 5 subcategories. I only
want the series value to be the total.

For example, if my total is 500 then it is showing 1000 for the series value
because it's adding the total and the subcategories (subcategories always add
up to the total, so it's doubling).

I have the source data set up with the total and each subcategory in
separate columns.

Is there any way to get what I want? I would even consider having 2 columns
on the chart, one for the total and the other stacked with the 5 categories,
but I really would like it all in 1 column.

Thanks!

 

[Bernard Liengme]

What you describe is the way STACKED chart are design to work.
If A has value 20, B and value 60, then A stacked on B will end up as 80
from origin
The combined column is the sum of the parts.
best wishes

 

[AdamV]

I can't see why you would want a stack showing all five categories and
the total stacked on top of that, it makes no real sense.
However, you should be able to show the total and plot it on a secondary
axis, change the chart type to a non-stacked column, add data labels to
show the total values, hide the columns (no border, no fill), then
delete the second axis to force it to a single scale.

Hope this helps
Adam

 

[cherman]

Thanks for your feedback, and I agree with you on what a stacked column chart
is supposed to show. I had a talk with my boss and that is what he wanted to
see. Anyway, thanks for the tip!

 

[cherman]

Yes, I agree, but my boss wanted to see the total of all the segments as well
as the segments. The end result makes sense to me, but the stacked column
chart isn't designed that way, so I must figure out another way to provide
the total.

Thanks!

 

[Jon Peltier]

I wonder if this would be more straightforward:

Label Totals on Stacked Column Charts
http://peltiertech.com/WordPress/label-totals-on-stacked-charts/

- Jon