Secondary Axis

[dsteven]

Office Standard 2007. To add a secondary vertical axis Excel Help says at
instruction 1.2 On the Format tab, in the Current Selection group, click the
arrow next to the Chart Elements box, and then click the data series that you
want to plot along a secondary vertical axis.

The Current Selection group doesn't have a Chart Elements to select from,
but the illustration does show Chart Area with the arrow next to it. It would
be nice and very helpful if the terminaolgy were consistent.

Insturction 2. goes on to direct more or less accurately. When I click on
Format Selection it brings up a dialogue box titled Format Axis. Again, it
would be nice and very helpful if the terminaolgy were consistent.

Instruction 3. directs => Click Series Options if it's not selected, and
then under Plot Series On, click Secondary Axis and then click Close.

Here I could not find the function Series Options, there is an Axis Options,
which I would presume is what might be referred too, however, there is no
function available titled Plot Series On or Secondary Axis.

This is where the problem lies. If I can't get Series Options to bring up
the functions plot Series On or Secondary Axis, I can't get the secondary
axis on the chart.

Sorry this is so lengthy but I think and I'm hoping explaining the problem
in detail might attract an accurate response from MicroSoft support people.
Any one else who has a suggestion, I'd be glad to hear from you.

 

[Jon Peltier]

Office Standard 2007. To add a secondary vertical axis Excel Help says at
instruction 1.2 On the Format tab, in the Current Selection group, click
the
arrow next to the Chart Elements box, and then click the data series that
you
want to plot along a secondary vertical axis.

The Current Selection group doesn't have a Chart Elements to select from,
but the illustration does show Chart Area with the arrow next to it. It
would
be nice and very helpful if the terminaolgy were consistent.

That dropdown box is the Chart Elements box. It contains a listing of all
chart elements which you can pick from.

Insturction 2. goes on to direct more or less accurately. When I click on
Format Selection it brings up a dialogue box titled Format Axis. Again, it
would be nice and very helpful if the terminaolgy were consistent.

Format Axis allows formatting of the selected axis. It does not allow
addition of a secondary axis. You need to select a series and go to the
Format Series dialog.

Instruction 3. directs => Click Series Options if it's not selected, and
then under Plot Series On, click Secondary Axis and then click Close.

Here I could not find the function Series Options, there is an Axis
Options,
which I would presume is what might be referred too, however, there is no
function available titled Plot Series On or Secondary Axis.

Did you select a series first? The series has a Plot Series On block where
you can choose which axis to plot thee series on. Note that if you only have
one series in the chart, you can only use the primary axis.

This is where the problem lies. If I can't get Series Options to bring up
the functions plot Series On or Secondary Axis, I can't get the secondary
axis on the chart.

Sorry this is so lengthy but I think and I'm hoping explaining the problem
in detail might attract an accurate response from MicroSoft support
people.
Any one else who has a suggestion, I'd be glad to hear from you.

You're not likely to see any Microsoft support people in this forum, nor any
official Microsoft response at all. People such as myself who answer your
questions are volunteers who just like helping people.

- Jon

 

[dsteven]

Answer to my own question. When you explore long enough maybe you can find
ananswer. This didn't come from Micro Soft.

When adding a secondary axis data series it matters that the column of data
containing the secondary axis data series be adjacent to the column
containing the primary data series. When this is the case, clicking on the
chart will outline the coulmn data series and the outline can be clicked on
and dragged to include the column containin the secondary data series. At
this point, the data points will be representd on the chart and the format
window will provide the opportunity to select secondary data series.

I just can't find the words to thank Micro Soft for being so clear in their
help screens. Its a great product with strong capibilty but the help screens
are inferiior in their content. Work on it Micro Soft.

 

[dsteven]

Jon, Thanks and I appreciate you taking the time to offer some direction. I
read through your text, I did all the things you brought up they didn't
resolve the issue. What I found was the column containing the secondary data
series needs to be adjacent to the column cotaining the primary data series
so that both columns can be selected simultaneously. But no where does MS
talk about that. Again thanks for taking the time to respond.

 

[Christopher Scott [MSFT]]

I'm curious about the problem/solution you found. I tried one column/series
of data in one part of the sheet (A1:A5) and another column/series of data in
a non-adjacent part of the sheet (F12:F17) and was able to move the second
series to a secondary axis.

Could you elaborate? The description of your solution sounds like you are
trying to add more data to the chart by dragging the blue outlines on the
cells when the chart is selected.

 

[dsteven]

I had a scater chart with the following data series A1:A20 (Date) the next
data series was in E1:E20 (Value $). A1:A20 is the horizontal axis and E1:E20
is the vertical axis on the left side of the chart. When I attempted to add a
secondary axis, that is, a secondary vertical axis on the right side of the
chart, for data series Q1:Q20, the formatting tools were not available. So I
inserted a column at column F and moved column Q data to column F. By doing
this, I could click on the existing chart, reveal the data series associated
with the chart, and then click on teh blue outline and drag it to the right
to include the data series in column F.

My efforts to include the secondary data series while in column Q were
unsuccesful. Even fiddling with the control key and trying various ways to
see if that would help. Hope this hlps.

 

[Jon Peltier]

The columns have no such requirement, unless you are referring to creating
the chart with them both. And in fact, they need not be adjacent, as long as
you select them as discontiguous ranges (select one, hold CTRL while
selecting the other). You can add another series to an existing chart in
several ways. The Select Data dialog is one way (click the Add button
associated with the list of series); another way is to copy the data, select
the chart, and use paste special.

If you didn't have two series in the chart, that is the problem.

- Jon

 

[Jon Peltier]

Did you add the data in column Q as a second series in the chart? If there
is only one series in the chart, as I stated in my first reply to your post,
you cannot have a secondary axis. The position of the data in the worksheet
has NOTHING to do with it.

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

 

[jen gold]

HI Jon:

Coincidentally, I am trying to solve a similar problem. I am in Windows2003.
The help instructions in the program are really direct.

1. Click the data series you want to plot along a secondary value axis.
2. On the Format menu, click Selected Data Series, and then click the Axis
tab.
3. Click Secondary axis.

Yet, I am not finding this format menu with a data series option. Also what
I am trying to do is to have a stacked bar as the primary and a line graph on
the secondary. I am assuming this is possible, though it's not visible in the
chart options.
I appreciate your advice.
Many thanks,
Jen

 

[Jon Peltier]

Perhaps what led you and dsteven astray is the wording "data series" in the
instructions. "Data series" implies data, i.e., in the worksheet. Replace
that phrase with "chart series", meaning a series that is already present in
the chart, add the data series to the chart (if it isn't already plotted),
and repeat the instructions.

For your specific chart, make a stacked chart with all of the data, then
select the charted series that you want as a line on the other axis. Right
click on the series and choose Chart Type, and select the line style that
you want. Then double click on this new line, and on the Axis tab, choose
Secondary.

- Jon