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?

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>

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

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?
>

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?
>>

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?
> >>
>
>