force graph to axis of choice

The nature of the Category(X) axis for a scatter chart and for a column
chart are very different. It is hard to make logical sense out of
placing the two chart types on the same axis. However, if you want to
do so, start with a XY Scatter chart, add the column chart data and
play with that setup. It yields some logically sensible results and
some bizarre results as one experiments with primary-only, secondary
y-, secondary x-, and moving the primary/secondary axes up/down, etc.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , bxtrap01
@comcast.net says...
i have a bar chart and when then added an xy scatter chart
i want the scatter chart to use the primary axis but it seems to insist on
using the 2ndary axis
i never saw a way to force the scatter to use the primary axis when creating
the scatter plots
i suppose i can change the scale of the 2ndary axis to match the primary but
then can't use
the 2ndary axis if i need it for different values
when i open the format data series for any of the xy scatter plots, the
place to choose the axis is greyed out

Generally, when you put the XY Scatter series on the primary axis that
already houses a column series, the left edge of the chart (to the left
of the first category) is at X=0.5, the categories are at X=1, 2, 3,
etc., and the right edge of the chart is at N+0.5 for N categories (N
points in the column chart). The Y values can be made to coincide
perfectly onto the primary axis.

The OP said "bar" chart, which is the name many newcomers to Excel have
for column charts. If he really meant "bar" charts, then that's a
different story. The axes will be hard to make coincide: the bar chart's
X axis is atop the XY chart's Y axis, and vice versa. You cannot coerce
both series onto the primary axes, but you can uncheck the XY series' X
axis, and it will use the Y values of the bar chart; both of these axes
are value axes. The XY series' Y axis cannot be made to correspond to
the bar series' X axis, because one is a category axis, the other a
value axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tushar Mehta wrote:

The nature of the Category(X) axis for a scatter chart and for a column
chart are very different. It is hard to make logical sense out of
placing the two chart types on the same axis. However, if you want to
do so, start with a XY Scatter chart, add the column chart data and
play with that setup. It yields some logically sensible results and
some bizarre results as one experiments with primary-only, secondary
y-, secondary x-, and moving the primary/secondary axes up/down, etc.

thanx for the replies
it is a bar chart not a column chart but now the problem has changed
that was the 2nd stage of creating a combo chart with bar, xy scatter, and
transparent column, all 3 in the same plot area
i thought i needed the 2ndary axis 'saved' for the transparent column but
evidently not
so i got the bars and xy scatter working ok, but whenever i try to add a
data series for
the transparent column, it blows out the data series or something for the xy
scatter, bcause it goes away and i can' t seem to get it back
see, job1data.com/xcelChart1.htm for a better explanation

"Jon Peltier" wrote in message
...
Generally, when you put the XY Scatter series on the primary axis that
already houses a column series, the left edge of the chart (to the left
of the first category) is at X=0.5, the categories are at X=1, 2, 3,
etc., and the right edge of the chart is at N+0.5 for N categories (N
points in the column chart). The Y values can be made to coincide
perfectly onto the primary axis.

The OP said "bar" chart, which is the name many newcomers to Excel have
for column charts. If he really meant "bar" charts, then that's a
different story. The axes will be hard to make coincide: the bar chart's
X axis is atop the XY chart's Y axis, and vice versa. You cannot coerce
both series onto the primary axes, but you can uncheck the XY series' X
axis, and it will use the Y values of the bar chart; both of these axes
are value axes. The XY series' Y axis cannot be made to correspond to
the bar series' X axis, because one is a category axis, the other a
value axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tushar Mehta wrote:

The nature of the Category(X) axis for a scatter chart and for a column
chart are very different. It is hard to make logical sense out of
placing the two chart types on the same axis. However, if you want to
do so, start with a XY Scatter chart, add the column chart data and
play with that setup. It yields some logically sensible results and
some bizarre results as one experiments with primary-only, secondary
y-, secondary x-, and moving the primary/secondary axes up/down, etc.

There are a few ways to try this. First I tried floating bars on the
primary axis, then a floating bar on the secondary axis to get the
transparent overlay. So far, so good. Then I tried adding a new series
for the vertical line, but when I tried to change that series to an XY
Scatter, I got the message that Excel can't make certain combinations of
charts. I guess Bar (primary) + Bar (secondary) + XY is out; Line won't
work in place of the XY, and Bar + Column works the same as Bar(pri) +
Bar(sec).

I tried a different approach for the column, using a variation of
Stephen Bullen's Variable Width Column Chart (http://bmsltd.co.uk). But
as noted above, I couldn't add an XY series to provide the vertical line.

Never one to give up easily, he he, I tried again, with the floating
bars in place. I added my XY series for the vertical line, and then I
added the variable width column. A little tweaking of the chart at that
point, and I got what I wanted.

The workbook's a mess, though, not ready for public consumption. I'll
have to clean it up before I let anyone see it. Maybe I'll be able to
post it in a day or two, or this description gets you where you need to be.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

bbxrider wrote:
thanx for the replies
it is a bar chart not a column chart but now the problem has changed
that was the 2nd stage of creating a combo chart with bar, xy scatter, and
transparent column, all 3 in the same plot area
i thought i needed the 2ndary axis 'saved' for the transparent column but
evidently not
so i got the bars and xy scatter working ok, but whenever i try to add a
data series for
the transparent column, it blows out the data series or something for the xy
scatter, bcause it goes away and i can' t seem to get it back
see, job1data.com/xcelChart1.htm for a better explanation

"Jon Peltier" wrote in message
...

Generally, when you put the XY Scatter series on the primary axis that
already houses a column series, the left edge of the chart (to the left
of the first category) is at X=0.5, the categories are at X=1, 2, 3,
etc., and the right edge of the chart is at N+0.5 for N categories (N
points in the column chart). The Y values can be made to coincide
perfectly onto the primary axis.

The OP said "bar" chart, which is the name many newcomers to Excel have
for column charts. If he really meant "bar" charts, then that's a
different story. The axes will be hard to make coincide: the bar chart's
X axis is atop the XY chart's Y axis, and vice versa. You cannot coerce
both series onto the primary axes, but you can uncheck the XY series' X
axis, and it will use the Y values of the bar chart; both of these axes
are value axes. The XY series' Y axis cannot be made to correspond to
the bar series' X axis, because one is a category axis, the other a
value axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Tushar Mehta wrote:


The nature of the Category(X) axis for a scatter chart and for a column
chart are very different. It is hard to make logical sense out of
placing the two chart types on the same axis. However, if you want to
do so, start with a XY Scatter chart, add the column chart data and
play with that setup. It yields some logically sensible results and
some bizarre results as one experiments with primary-only, secondary
y-, secondary x-, and moving the primary/secondary axes up/down, etc.