how to plot line on secondary axis on stacked bar chart

[Gregg Gagliardi]

I have tried in vain to find a way to get Excel 2000 to
plot a secondary axis on a stacked bar chart. The closest
I can get is (1) using the custom clustured bar chart with
secondary axis (undesired compromise, since I want stacked
bars) or (2) adding the secondary axis to the standard
stacked bar chart, which only results in (a) adding a
third segment to the stacked bars along with the new
secondary axis ( useless for my purposes) or (b)adding
lines for each data series within the latter chart
described in (a) ( even more useless).

There must be a way to create the kind of chart that I
want. If not, it is time to find another program that
permits more flexible charting options. Any help that can
be provided is appreciated, including software packages
that are alternatives to Excel 2000.

Thanks

Gregg

[Andy Pope]

Gregg,

Here's one way of displaying the values of the Y axis on both sides of
your chart. I think this is what you are trying to do.

example data in cells A1:C4, not A1 is empty

X Y
A 1 2
B 2 3
C 3 1
D 1 2

Highlight the range A1:C4 and use the chart wizard to make a stacked
column chart. This should stack Y on X for categorys A,B and C.

In cell A7 enter this formula, which should return the value largest
stacked item. This will then keep the two value axes in sync.

=MAX(SUM(B2:C2),SUM(B3:C3),SUM(B4:C4))

You can now use this value to create a dummy series. Simply drag the
cell onto your chart, add as new series. This should add a new item to
stack A. So click the new column and change it to the secondary axis.
You should now have a single bar obscurring the stack for A but more
importantly a value axis on the right of your chart.

To finish off format the column to have no fill or border.
Delete the extra legend entry.

Post back if not what you meant.

Gregg Gagliardi wrote:

--

Cheers
Andy

http://www.andypope.info

[Jon Peltier]

Gregg -

You can take control over the way series are formatted. Rather than
using one of the built-in custom combo charts provided by Excel, plot
all the data using the chart type that will be most prevalent in your
chart. Then one by one, select the series which will be different. If
you want a series on the secondary axes, double click on it to bring up
the Format Series dialog, and pick Secondary on the Axes tab. If you
want the series to have a different type, right click on it, choose
Chart Type from the pop up menu, and choose the type you want. When you
are done with this, you can right click the chart, choose Chart Options
from the pop up menu, and decide exactly which combination of axes you
want on the Axes tab.

When in doubt, work on a copy of the chart. The right mouse click is
your friend, because it shows a list of options available for the
selected object. Double clicking brings up another set of options. The
F4 function key is nice because it lets you select another object and
repeat the previous action (changing to secondary axes is one thing that
can be repeated for another series, so is changing the chart type). And
don't forget Undo (Ctrl-Z)!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Gregg Gagliardi wrote:
> Andy
>
> Thanks for the help. What I am trying to do is a bit
> different than what you describe below. The stacked bars
> in my chart represent a variable with 8 levels; each bar
> divides a total at each level into two constituent parts.
> Because the totals vary a lot from one bar to another, I
> want to show not only the mix of one critical part in each
> bar to the remaining part in absolute numerical terms
> (which the stacked bar does well) but also the changing
> percentage that the critical part represents relative to
> the total, regardless of the absolute size of the total.
> This would mean plotting that percentage (a third data
> series) for each stacked bar as a point on a line graph.
> The secondary axis for this line would run from 0% to
> 100%. Note: I can do this with one of the custom graph
> selections in the Excel graph menu, but the bars are
> clustered not stacked. My thought is that if Excel can
> plot the latter graph with secondary axis using clustered
> bars, there ought to be a way to also do it for stacked
> bars.
>
> Thanks again for your prompt reply.
>
> Gregg

>>.
>>
>

[Charles Friedo]

Hello,

Simple and Easy way to create charts in excel is to use an external data visualization add-on.

I would recommend you to use ChartExpo™ for Excel.
There is no requirement of coding skills.

Installation Source:

ChartExpo™ for Excel: https://chartexpo.com/utmAction/MTErY29tbXVuaXR5K3hsK1NCK0dHKw==

ChartExpo™ for Google Sheets: https://chartexpo.com/utmAction/MTErY29tbXVuaXR5K2dzK1NCK0dHKw==

Thanks!