Combination chart: primary data labels obscure line series on secondary axis

[Uhl]

I have a combination chart; a stacked column chart on the Primary Axis
and a line chart on the Secondary Axis. I use the stacked column chart
to display "bands" which I then overlay with the second data series. So
far so good, the line segments and markers draw "on top of" the bands.

But when I turn on data labels for the stacked column chart (to
identify the bands), they display in *front* of the line series?! The
column itself, remains behind, but the text of the data label is
drawing on top of the series.

Is there a way to force the labels *behind* the series on the secondary
axis?

 

[Jon Peltier]

That's how Excel lays out the chart elements, with labels on top of all the
series. Surely there's no possible situation in which you'd want the lines
to obscure the labels, is there? Oh.

You could try moving the labels off to the side, or put textboxes outside
the plot area, or remove the labels and hope the legend is an appropriate
way to label these regions.

- Jon

 

[Uhl]

Actually, I'm using the stacked column chart to add "bands" to the
graph. I've formatted the labels so they are very light and use a large
font-size. So even with the series on top, they're still readable. Kind
of like they're watermarks.

I may have to live with it. I was just hoping there was a workaround.

Thanks for the reply,

 

[Ibeam2000]

If you need to do this repeatedly, have Visio, and can do some VBA, you
might consider the followng:

Copy the chart into the clipboard, open Visio, new document, paste.

Ungroup the object.

Write a VBA program to iterate through the objects (there are basically
lines, text, and filled areas) and bring anything in the colour of your
line to the front. Or bring lines of colour x to the front and leave
text of colour x alone. Whatever you have to do to differentiate
between the two.

Save as metafile, visio drawing, whatever works. Most of my Excel
graphs wind up as lightweight metafiles linked to Word documents.

I have had a fair bit of success doing this kind of postprocessing in
Visio to achieve publication quality results. I have moved labels
around, done colour sorting (the line on top is most important but
needs to be drawn last), and so on. Most of this automates very
nicely.

At least, with Visio, you can gain some understanding of how Excel
draws. Then do something about it.