Anyway to plot a stacked bar and bar chart on Primary and Secondary axis?

[Father Guido]

Hi,

I am using XL2003 and am wondering if there is a way to add a
secondary axis to a stacked bar chart. I need to display $$$ is the
primary axis and percent in the secondary axis.

I tried adding a dummy/invisible bar series to plot on the secondary
axis, but excel added the dummy series into the stacked bars, and not
beside them. I thought of just drawing in the secondary axis, but if
the boss likes this I'd prefer something that's automated so I don't
have to keep drawing in the secondary axis data and title, whic I've
done in the past. However that becomes a PITA when excel adjusts the
Primary axis data and then the secondary axis has to be modified to
line-up with the new data and their new gridline positions. I think I
can add a line series to the stacked bars and possibly use the
secondary axis that way, then make the series invisible. But is there
a better/easier way to do this for now and in the future?

Thanks!

Norm

 

[Jon Peltier]

When you add the series, it is piled onto the top of the stack. Double click
the new series, and on the Axis tab, choose Secondary. Now it will appear in
front of the stacked series. Now you can hide the new series.

If you just need another scale, it might be better to make a fake axis with
an XY series and its data labels:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

You can put the relationship between $ and % in a set of worksheet formulas,
so they adjust automatically. Much easier than fiddling with the scale of a
secondary axis.

- Jon

 

[Father Guido]

Hey, thanks Jon. Excellent advice I will follow. Thanks again!!!

Norm

On Wed, 9 Jan 2008 21:19:51 -0500, "Jon Peltier"

~When you add the series, it is piled onto the top of the stack.
Double click
~the new series, and on the Axis tab, choose Secondary. Now it will
appear in
~front of the stacked series. Now you can hide the new series.
~
~If you just need another scale, it might be better to make a fake
axis with
~an XY series and its data labels:
~
~http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
~
~You can put the relationship between $ and % in a set of worksheet
formulas,
~so they adjust automatically. Much easier than fiddling with the
scale of a
~secondary axis.
~
~- Jon
~-------
~Jon Peltier, Microsoft Excel MVP
~Tutorials and Custom Solutions
~Peltier Technical Services, Inc. - http://PeltierTech.com
~_______
~
~
~~> Hi,
~>
~> I am using XL2003 and am wondering if there is a way to add a
~> secondary axis to a stacked bar chart. I need to display $$$ is the
~> primary axis and percent in the secondary axis.
~>
~> I tried adding a dummy/invisible bar series to plot on the
secondary
~> axis, but excel added the dummy series into the stacked bars, and
not
~> beside them. I thought of just drawing in the secondary axis, but
if
~> the boss likes this I'd prefer something that's automated so I
don't
~> have to keep drawing in the secondary axis data and title, whic
I've
~> done in the past. However that becomes a PITA when excel adjusts
the
~> Primary axis data and then the secondary axis has to be modified to
~> line-up with the new data and their new gridline positions. I think
I
~> can add a line series to the stacked bars and possibly use the
~> secondary axis that way, then make the series invisible. But is
there
~> a better/easier way to do this for now and in the future?
~>
~> Thanks!
~>
~> Norm
~