Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Stacked column and column combo chart
I have the following data set: 2001 2002 Sales Product A 45 55 Sales Product B 102 123 Sales Product C 66 78 Total Gross Margin 60 77 I want the sales to be in stacked columns and the gross margin a separate column next to them, as it represents total gross margin for all three products; 2001 and 2002 are the X category labels. I can easily show the gross margin series as a line or area but when I attempt to show it as a column, the entire chart is converted to clustered columns. Is there a work-around? |
#2
|
|||
|
|||
KG,
Seems to me Stephen Bullen has an example of that at http://www.bmsltd.ie/Excel/Default.htm. It's in one of the "Charting examples" downloads. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "KG" wrote in message ... I have the following data set: 2001 2002 Sales Product A 45 55 Sales Product B 102 123 Sales Product C 66 78 Total Gross Margin 60 77 I want the sales to be in stacked columns and the gross margin a separate column next to them, as it represents total gross margin for all three products; 2001 and 2002 are the X category labels. I can easily show the gross margin series as a line or area but when I attempt to show it as a column, the entire chart is converted to clustered columns. Is there a work-around? |
#3
|
|||
|
|||
If you stagger your data, you can create side-by-side stacked columns.
Bernard Liengme has an example and instructions on his site: http://www.stfx.ca/people/bliengme/E...ps/Columns.htm and Jon Peltier has links to other sites with information: http://peltiertech.com/Excel/ChartsH...sterStack.html KG wrote: I have the following data set: 2001 2002 Sales Product A 45 55 Sales Product B 102 123 Sales Product C 66 78 Total Gross Margin 60 77 I want the sales to be in stacked columns and the gross margin a separate column next to them, as it represents total gross margin for all three products; 2001 and 2002 are the X category labels. I can easily show the gross margin series as a line or area but when I attempt to show it as a column, the entire chart is converted to clustered columns. Is there a work-around? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Thanks for the links; by and large, they are using overlaying of the charts
as the fundamental technique. If there is no other way to create my chart, I will resort to overlaying! Debra, I did try to create the chart by staggering the data but I was unable to come up with a category labeling scheme that the reader could understand. You see, I need (a) the stack components to be identified as Product A, B. and C, (b) the unstacked column next to it labeled 'Total Gross Margin" and (c) the years 2001 and 2002 to be the X category labels, applying to both the stacked and unstacked column next to it. If you honestly feel that overlaying is the only option, please let me know and I'll stop looking for an alternative. Thanks for your help Debra and Earl. "KG" wrote: I have the following data set: 2001 2002 Sales Product A 45 55 Sales Product B 102 123 Sales Product C 66 78 Total Gross Margin 60 77 I want the sales to be in stacked columns and the gross margin a separate column next to them, as it represents total gross margin for all three products; 2001 and 2002 are the X category labels. I can easily show the gross margin series as a line or area but when I attempt to show it as a column, the entire chart is converted to clustered columns. Is there a work-around? |
#5
|
|||
|
|||
The techniques do not rely on overlaying separate charts, because they plot all the
data in one chart. But a gross margin is a different kind of data than what you probably are plotting for the different products. So why not use a different style? Plot all four series in a stacked column chart, then select the gross margin series, go to the Chart menu, choose Chart Type, and change this series to a line chart. If you need to, you can plot this series on a secondary axis: double click on it, and on the Axis tab select the Secondary Axis option. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ KG wrote: Thanks for the links; by and large, they are using overlaying of the charts as the fundamental technique. If there is no other way to create my chart, I will resort to overlaying! Debra, I did try to create the chart by staggering the data but I was unable to come up with a category labeling scheme that the reader could understand. You see, I need (a) the stack components to be identified as Product A, B. and C, (b) the unstacked column next to it labeled 'Total Gross Margin" and (c) the years 2001 and 2002 to be the X category labels, applying to both the stacked and unstacked column next to it. If you honestly feel that overlaying is the only option, please let me know and I'll stop looking for an alternative. Thanks for your help Debra and Earl. "KG" wrote: I have the following data set: 2001 2002 Sales Product A 45 55 Sales Product B 102 123 Sales Product C 66 78 Total Gross Margin 60 77 I want the sales to be in stacked columns and the gross margin a separate column next to them, as it represents total gross margin for all three products; 2001 and 2002 are the X category labels. I can easily show the gross margin series as a line or area but when I attempt to show it as a column, the entire chart is converted to clustered columns. Is there a work-around? |
#6
|
|||
|
|||
A combination stacked column+line chart is my current design. I just thought
that it would look neat to have the gross margin as a column next to the stacked column showing sales with its component parts. If I still feel strongly abou it, I'll apply the techniques that were pointed to me. Thanks again! "Jon Peltier" wrote: The techniques do not rely on overlaying separate charts, because they plot all the data in one chart. But a gross margin is a different kind of data than what you probably are plotting for the different products. So why not use a different style? Plot all four series in a stacked column chart, then select the gross margin series, go to the Chart menu, choose Chart Type, and change this series to a line chart. If you need to, you can plot this series on a secondary axis: double click on it, and on the Axis tab select the Secondary Axis option. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ KG wrote: Thanks for the links; by and large, they are using overlaying of the charts as the fundamental technique. If there is no other way to create my chart, I will resort to overlaying! Debra, I did try to create the chart by staggering the data but I was unable to come up with a category labeling scheme that the reader could understand. You see, I need (a) the stack components to be identified as Product A, B. and C, (b) the unstacked column next to it labeled 'Total Gross Margin" and (c) the years 2001 and 2002 to be the X category labels, applying to both the stacked and unstacked column next to it. If you honestly feel that overlaying is the only option, please let me know and I'll stop looking for an alternative. Thanks for your help Debra and Earl. "KG" wrote: I have the following data set: 2001 2002 Sales Product A 45 55 Sales Product B 102 123 Sales Product C 66 78 Total Gross Margin 60 77 I want the sales to be in stacked columns and the gross margin a separate column next to them, as it represents total gross margin for all three products; 2001 and 2002 are the X category labels. I can easily show the gross margin series as a line or area but when I attempt to show it as a column, the entire chart is converted to clustered columns. Is there a work-around? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To create a stacked column chart and group the stacked bars togeth | Charts and Charting in Excel | |||
How do I combine a line and a percentage stacked column chart? | Charts and Charting in Excel | |||
Clustered column chart with stacked coumns | Charts and Charting in Excel | |||
Making Special "Stacked Column" Chart | Charts and Charting in Excel | |||
Copying the contents of a column into a chart | Excel Worksheet Functions |