If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with stacked column chart
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! |
#2
|
|||
|
|||
Problem with stacked column chart
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "cherman" wrote in message ... 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! |
#3
|
|||
|
|||
Problem with stacked column chart
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 On 25/02/2010 01:44, cherman wrote: 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! |
#4
|
|||
|
|||
Problem with stacked column chart
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! "AdamV" wrote: 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 On 25/02/2010 01:44, cherman wrote: 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! . |
#5
|
|||
|
|||
Problem with stacked column chart
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! "Bernard Liengme" wrote: 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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "cherman" wrote in message ... 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! . |
#6
|
|||
|
|||
Problem with stacked column chart
I wonder if this would be more straightforward:
Label Totals on Stacked Column Charts http://peltiertech.com/WordPress/lab...tacked-charts/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 2/25/2010 11:55 AM, cherman wrote: 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! "AdamV" wrote: 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 On 25/02/2010 01:44, cherman wrote: 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! . |
Thread Tools | |
Display Modes | |
|
|