Stacked Bar Chart with Secondary Axis

[Guest]

Need to create a stacked bar chart and utilize the secondary axis for data
with a different value range.

Data Table:
Yr 1 YR 2 Pipeline
Small Deals 7 1 13
Med Deals 2 6 14
Large Deals 0 2 20

Primary axis should be Yr 1 & 2 and secondary axis should be pipeline.
Having created the stacked bar chart I change the pipeline series to the
secondary axis and end up with a simple bar chart. How can I create a
stacked bar chart with a secondary axis with a different scale?

Thanks.

 

[Andy Pope]

Hi,

Try staggering you data and then create you stacked chart and move
Pipeline to the secondary axis.

Yr 1 YR 2 Pipeline
Small 7 1
Deals 13
Med 2 6
Deals 14
Large 0 2
Deals 20

Cheers
Andy

 

[Guest]

do you want the three items to still in in one stack?
or do you want the pipeline to be besides the yr1 and yr2 data.
I am not sure what you want to do.

 

[Guest]

Andy,

Thanks. However, the pipeline data still looses it's stacked bar format.
Any hints on how to convince excel to leave all data series as stacked?

 

[Guest]

BJ,

Yes. The series "Year 1" deals should be stacked then next to that would be
the "Year 2" series stacked, followed by the "Pipeline" series stacked.

 

[Guest]

try moving the data for the pipeline series down far enough that it does not
overlap the other data.make a stacked column chart
select each of the data sets shown in the pipeline set and change to
secondary axis. (you may have to play with the scale to be able to select
each of them) It will probably strart to show as a side by side chart and you
will have to select one and set it as chat type stacked columns again. By
playing with the fill and line formating you can easily indicate which side
should be used for value axis a,d which legend character you want.
I would select the unwanted legend charaters in the legend box and clear all
so that they dont show

 

[Andy Pope]

You can select the pipeline series and change the chart back to stacked.
What I can't quite understand is what you want it is stacked with???

Cheers
Andy

 

[Jon Peltier]

To clarify, arrange your data like this:

Yr 1 YR 2 Pipeline
Small Deals 7 1
Med Deals 2 6
Large Deals 0 2
Small Deals 13
Med Deals 14
Large Deals 20

Plot this data by row. BTW, the Yr 1, Yr 2, Pipeline are category labels, not series
names. There are six series, two each with the duplicate names Small Deals, Med
Deals, and Large Deals. Double click the Small Deals series that has a value for
Pipeline, and on the Axis tab, select Secondary. Select the Med Deals series which
has a Pipeline value, and press the F4 key to repeat, then do the same for the
corresponding Large Deals series. Format the series with matching names to look the
same, and delete the duplicate legend entries: select the legend, then a legend
label within the legend, and press Delete.

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