Dual Value Axes advice needed please.

[Spence]

Hi all, need some help with what should be a simple chart.

Using Excel 2003, I have a worksheet with a rolling year of data,
months on row 1, series titles in A2:A4, values in B2:M4 and Annual
Total in Column N (data in N2:N4).

As the Annual Total is 12 times the average I want the Total to be
assigned to a second Value Axis on the right from the default Value
Axis on the left.

I have no problem setting a complete Series to a second value Axis, but
I want to set a category to the second axis. How can I do that?

Thanks.

 

[Jon Peltier]

To add the secondary category axis, go to Chart menu > Chart Options > Axes
tab, and check the appropriate choice.

- Jon

 

[Spence]

To add the secondary category axis, go to Chart menu > Chart Options > Axes
tab, and check the appropriate choice.

That was my thought initially, but I only have a Primary Axis here. Nothing I
change will add a secondary Axis.

 

[Jon Peltier]

You said you had no problem with getting a secondary value axis. If you
don't have any secondary axes, only primary, you need at least two series in
the chart, and you have to format one so it appears on the secondary axis.
Then follow the steps I showed to get a secondary category axis.

- Jon

 

[Spence]

You said you had no problem with getting a secondary value axis. If you
don't have any secondary axes, only primary, you need at least two series in
the chart, and you have to format one so it appears on the secondary axis.
Then follow the steps I showed to get a secondary category axis.

OK, perhaps I didn't explain too well. I don't need a secondary Category Axis,
I need a secondary Value Axis, and assign the last Category to this value axis.
All I seem to be able to do is assign a series to the secondary value axis, not
a category.

I have categories of Jan to Dec plus Total. I need to assign Jan to Dec to the
Primary value axis, and Total to the Secondary value axis, regardless of
series. If I select a complete series, right click, select Format Series, I can
assign it to the secondary Value axis. If I select an individual point in the
series (as I can't select all 3 points in the category), there is no Axis
option.

I have seen a printed chart with the months as a line chart and the total as a
column chart, both with their own value axes, so I presume it can be done.

 

[Spence]

Well I've finally got it working, but it seems to be a bit of a kludge.

Instead of putting all the data including the totals on 3 rows (my 3
series), I have the monthly data on the first 3 rows, and the total
data on the next 3 rows. Now I chart all 6 rows, and set the 3 total
row series to use the secondary axis. Finally change the colours of
these second 3 series to match those of the first 3 series and it works
as required.

Thanks for your suggestions Jon.