A tough question? - about surface plots

[ShaneDevenshire]

Hi Folks (or maybe Jon),

In 2007 I have a surface plot, first subtype. I wish to change the color of
the surface and the lines on the surface. I realize I can make an overall
change by changing the Chart Style or even worse by first changing the
spreadsheet Theme. But I want to access the line and surface colors
directly. How do I do this in 2007?

 

[Debra Dalgleish]

One way is to select the legend, then select an item in the legend.
Under the PivotChart Tools tab, click Format
Select a Shape Fill and Shape Outline

 

[ShaneDevenshire]

Hi Debra,

I don't think we are on the same page - I not in a pivot chart & I'm using
2007 not 2003.

 

[Jon Peltier]

She got the 2007 part right, but called out a different tab. I guess one of
the Chart contextual tabs has it, probably Chart Tools > Format. I always
select the legend key, the little colored square, and press Ctrl+1 to format
it. I used to double click on the legend key, but Excel 2007 has taught me
not to waste time double clicking, because it no longer (after 15 years of
Excel) brings up a formatting dialog.

I just discovered that Excel 2007 no longer lets you select just the legend
key, but in a surface chart, selecting the legend entry (the text label part
of the legend) is sufficient to allow formatting of the band. However, in
other chart types, you can no longer use the legend to format the series.
That's a loss in a busy chart where the points are difficult to select.

- Jon

 

[ShaneDevenshire]

Hi Jon & Debra,

Well actually I'm working on this bug. I see that in fact 2007 works
identically to 2003, although it names the components differently and you
don't select the legend key. However, this problem arose when I took a
surface plot from 2003 and moved it into 2007.

The surface plot value axis runs from 0 to 1. In 2003 I set the chart up
with Minimumn value 0, Maximum value 1 and Major Units 1. Thus if you apply
colors to the chart the format the entire surface. I formatted had formatted
the chart in 2003 using the Legend Key. In 2003 the Legend displayed 0-1 as
the single series. When this chart is opened in 2007 the legend displays
1-1. Selecting this single entry, now the entire entry, and choosing the
Shape File or Shape Outline commands under Chart Tools, Format produces no
results.

I see that the bug here is that when the chart was brought into 2007, Excel
changed the legend key to read 1-1. So if you select this legend key and
format it nothing happens.

Real problem is that, for some unknown reason Excel has changed the legend
key from 0-1 to 1-1, so this procedure is formatting the value 1. The ugly
solution -

1. select the value axis and format it with major units set to 0.2.
2. resize the legend to display all entries
3. format each entry with the color and line style you want.

Second Problem - when I do the above procedure I have only 4 legend entries:
0-.2
..2-.4
..4-.6
..8-1

Only 4 entries, the .6-.8 entry is missing? Just as in 2003 each of these
can be changed, however the missing entry can't.

I will send a copy of the 2003 file to you and you can open it in 2007 and
test. How do I send a copy of the file?

 

[Jon Peltier]

Shane -

Now I know why you were having problems. When I did a dummy chart, I set it
up with a bunch of bands, and they worked much like 2003. When your chart
appeared, there was only the one legend entry, and its formatting didn't
match that seen in the chart.

I have replicated the behavior you observed, with some further details.
After changing the major unit of 0.2 resulted in a missing legend entry, I
changed the major unit to 0.5, and only one of the two expected legend
entries appeared. I found that if you removed the legend and added it back,
you would get all the entries you were entitled to.

I then created a new Excel2007 surface chart, and changed the scale to 0 to
1 with major unit of 1, and I got two legend entries, 0-1 and 1-1. Hiding
and showing the legend in this chart did not remove the extraneous entry.

In the process of trying these things, Excel crashed a number of times. I
suspect there is something about the Excel 2003 surface chart which causes
problems with Excel 2007.

I wonder if there's any way to turn off the nag screen that reminds you not
drawing the series border lines will let the chart redraw more quickly. I
think Excel 2003 draws the chart with borders faster than Excel 2007 redraws
the chart without borders.

- Jon

 

[ShaneDevenshire]

Thanks for taking the time to verify that there seems to be a problem here.
It was my intent to flush out the problem and then submit it to Microsoft.
I've found, as I am sure you have, that there are a number of "issues", read
that as bugs, in 2007's charting area.

 

[Debra Dalgleish]

If you select the chart and remove the legend, then redisplay the
legend, the 0-1 series appears, and you can format it. You could then
delete the other legend item.

 

[ShaneDevenshire]

Hi Debra & Jon,

All in all I think you have confirmed that this is a bug, yes there is a
workaround, infact a number of them, but I don't think that was Microsoft's
intended behavior, so thanks for the time and input.

Interesting that it should plot and work correctly in 2007 but not when
imported into 2007.

Thanks,
Shane Devenshire