How do I change the order of the line versus the marker for a scat

[GJS001]

I have a scatter plot with thousands of points (markers only, no line), and
am plotting a second series as a line with only a few hundred points over the
same range of x-values (line only, no markers). I want the line to appear in
front, but Excel 2007 keeps putting the marker symbols in front, which
completely obscures the line.

I have tried changing the series order, but it makes no difference. In the
Chart Tools - Format - Arrange toolbar, there are "Bring to Front" and "Send
to back" buttons, but they are greyed out.

Suggestions?

 

[Jon Peltier]

I presume you mean you're plotting the second series as an XY series with
lines and no markers. If you plot it on the secondary axis, the lines will
appear in front of the markers on the primary axis. If you plot the second
series as a line series rather than XY, its line will be behind any markers
of an XY series.

- Jon

 

[GJS001]

OK - I was afraid that was the answer. Why don't the "Bring to Front" and
"Send to back" commands work on the markers and lines? That would be a
useful feature.

The secondary axis approach worked perfectly for a single set of markers and
a single line. Does everything plotted on the secondary axis appear in front
of objects plotted on the primary axis? It appears so. The order of
plotting must be (back to front):
1. primary axis lines
2. primary axis markers
3. secondary axis lines
4. secondary axis markers

I was able to set the secondary axis to the same scale as the primary axis,
set the ticks to "none", and set the labels to "none", and it appeared as
though the line was plotted against the primary axis along with the markers.
This was a workaround to not being able to change the order of line plots vs.
marker plots.

Are there good third party graphing packages that integrate with Excel to
provide greater flexibility than Excel Charts for custom tailoring the
graphs? I like the data management/manipulation in Excel, but can't do
everything I would like to with the graphs.

 

[Jon Peltier]

OK - I was afraid that was the answer. Why don't the "Bring to Front" and
"Send to back" commands work on the markers and lines? That would be a
useful feature.

Bring To Front and related commands work on individual shapes. The chart is
comprised of a large number of elements, which are constructed in a certain
order, and probably messing with that would affect Excel's ability to go
back and make changes to a chart. If you don't care about this
interactivity, you could copy the chart as a picture (hold Shift, click on
the Edit menu, choose Copy Picture, then choose On Screen and Picture
options), paste it, ungroup it, and play with all of the elements.

The secondary axis approach worked perfectly for a single set of markers
and
a single line. Does everything plotted on the secondary axis appear in
front
of objects plotted on the primary axis? It appears so. The order of
plotting must be (back to front):
1. primary axis lines
2. primary axis markers
3. secondary axis lines
4. secondary axis markers

For a given chart type, yes. Further:

Top to bottom:
All XY series (secondary over primary)
All Line series
Bar and Column series
Area series

I was able to set the secondary axis to the same scale as the primary
axis,
set the ticks to "none", and set the labels to "none", and it appeared as
though the line was plotted against the primary axis along with the
markers.
This was a workaround to not being able to change the order of line plots
vs.
marker plots.

After you assign one or more series to the secondary axis, you can go to
Chart menu > Options > Axes tab, and uncheck the secondary axes. This plots
the secondary series on the primary axes, but keeps the secondary and
primary series separated. This is easier than having to change one set of
axes to match up with the other.

Are there good third party graphing packages that integrate with Excel to
provide greater flexibility than Excel Charts for custom tailoring the
graphs? I like the data management/manipulation in Excel, but can't do
everything I would like to with the graphs.

There are no major packages that interact with Excel that I have any
experience with, though I'm sure some exist. Also, some third party
applications are able to work with Excel data files. There are a ton of
workarounds and small utilities on the internet, including a good number on
my web site. After you learn how the different chart elements interact, you
can figure out ways to make Excel charts show things you wouldn't imagine.

- Jon

 

[GJS001]

:

After you assign one or more series to the secondary axis, you can go to
Chart menu > Options > Axes tab, and uncheck the secondary axes. This plots
the secondary series on the primary axes, but keeps the secondary and
primary series separated. This is easier than having to change one set of
axes to match up with the other.

Very cool! Thanks for the tip!