Contour and Surface Charts in Excel 2007

Some time ago I wrote about Surface and Contour Charts in Microsoft Excel in Dian Chapman’s now defunct TechTrax Ezine. In that article I outlined the data requirements for surface and contour charts, and described some of the formatting idiosyncrasies of these charts. That article was valid for Excel versions 97 through 2003, but like so many other aspects of charting, Excel 2007 changed all that.

For these examples, let’s use this simple dataset. Each value in the grid is the product of the corresponding row and column headers.

Data for Contour and Surface Chart Examples

Excel 2003 Contour and Surface Charts

Select the data and insert a contour chart, and you will get something like this. I have removed the horizontal and vertical axis features (actually, the “category” and “series” axes), and selected a simple color scheme for the data bands. To format the bands, you format the legend key for that band, the small square in front of the label.

Excel 2003 Contour Chart with Default Scale

Scale

That scale is a bit coarse, and symmetrical features like the saddle point and the shapes of the colored bands in the corners are not symmetrically rendered. You can’t do much about the asymmetry; I used to insert rows and columns in the source data, and interpolate values, to double the resolution. This didn’t cure the strange rendering, but at least improved it a bit.

To change the scale of the bands, format the legend. The Format Legend dialog has a Scale tab just like in a typical Format Axis dialog. You can’t see the “value” axis, but you can select it in the Chart toolbar’s Chart Element dropdown, and format its scale directly.

Excel 2003 Chart Element Selection Dropdown

Here is the contour chart with a finer scale and more shades of blue and red.

Excel 2003 Contour Chart with Fixed Scale

Below is the corresponding surface chart. The value axis is now evident, and the saddle-like shape of the surface is perhaps easier to envision than in the contour plot. The downside is that part of the surface, the far dark blue corner, drops far enough that it is obscured by the saddle point in the middle of the chart.

Excel 2003 Surface Chart with Fixed Scale

Orientation

You can adjust the orientation of the surface chart using the 3-D View dialog, available by right-clicking on the chart.

Excel 2003 3D View Dialog

In Excel 2003, you can also click on a corner of the box enclosing the chart…

Excel 2003 Surface Chart with Corners Selected

… and drag the corner to reorient the chart. While dragging, just the outline of the box is visible, showing how the orientation is changing.

Changing Excel 2003 Surface Chart Orientation with the Mouse

Excel 2007 Contour and Surface Charts

To make a contour chart in Excel 2007, like 2003, you select the data, and insert the chart.

Scale

You can’t format the legend to adjust the scale of the colored bands, but you can select the value axis using the dropdown on the Chart Tools > Layout or Format tab.

Excel 2007 Chart Element Selection Dropdown

To change the colors of each band, select the legend entry (the legend keys in 2007 cannot be selected) and open the formatting dialog. The tabs for fill and border color refer to the formats of the legend key and the corresponding band.

Here is the contour plot with the same scale as the 2003 example above.

Excel 2007 Contour Chart with Fixed Scale

And the corresponding surface chart. Asymmetric rendering of the saddle point and other features has not changed since Excel 2003.

Excel 2007 Surface Chart with Fixed Scale

Orientation

The Format Chart Area dialog in Excel 2007 includes a 3-D Rotation pane to allow adjustment of the surface chart’s orientation. Like many 2007 dialogs, it is much larger than the corresponding 2003 dialog without much difference in actual function.

You can also select the corners in the Excel 2007 surface chart, but you cannot drag them to adjust the orientation of the chart.

Excel 2007 3D View Dialog

Formatting

Like much other formatting in Excel 2007, especially in Excel 2007 charts, the shading of the contour and surface charts is rather over-the-top. The charts have excessive color gradients, apparently to represent the reflections and the brighter and darker illumination caused by a light source located at some oblique angle.

The gradients are not too severe in a surface chart, but in a contour chart, they interfere with interpretation of the values in the bands. You’d think since the contour chart is “flat” it would not be affected by an offset light source. But the contour plot is really just a top view of the surface chart, so all shading appears in both charts.

The variations in color would not be half bad if there were a way to tone down the differences. I searched for a while, though, and could find no way to reduce the variation within a color band. Nor could I find any kind of texture or other feature that improved the appearance of the charts.

It is possible to remove the light and dark regions in the 2007 charts, rendering them with flat colors as in their 2003 counterparts. You have to dig pretty deep to find the trick, so I am pleased to share it with you here.

You need to format the band of color. To do this, click once on the legend, then click once on the label of the band you want to format. Then right click on this label and choose Format Band from the pop-up menu.

In the Format Band dialog, click on 3-D Format, then click on Material, and under Special Effect, select the Flat option.

Format Band - 3D Format - Material Dialog

This removes all of the textures within the band. Repeat for every band in the contour plot…

Excel 2007 Contour Chart with Fixed Shading

… or surface chart.

Excel 2007 Surface Chart with Fixed Shading

While the default texture with all of the shading and gradients was excessive, it provided a bit of detail missing in the flat-colored charts. In 2003, this detail was provided by borders on the bands. Add a border using the Format Bands dialog.

Excel 2007 Contour Chart with Borders

The border seems to help the surface chart more than the contour plot.

Excel 2007 Surface Chart with Borders

Peltier Tech Chart Utility

Comments

  1. Hi Jon,

    Previously I have used the VBA approach to remove the lighting effect, which is,

    activechart.ChartGroups(1).has3dshading = 0

  2. Andy -

    Thanks for that piece of code. It’s the kind of thing that belongs on a button somewhere.

  3. It bothers me that the depth axis can’t be made a Date Axis… Of course, now that I’m trying it, even the horizontal Category axis doesn’t seem to be behaving correctly (XL2007). I switch to Date and it switches back to ‘Automatically select based on data’. I guess I need to always interpolate to evenly spaced coordinates?

  4. Many people would like a true 3D XYZ surface chart, but the surface charts in Excel are not so powerful. Despite what the documentation may say, both axes along the floor behave as category axes, and only the Value axis (perpendicular to the floor) is a continuous value axis. This is clear in Excel 2003, but 2007′s format axis dialog pretends to allow a date-scale axis.

  5. THANK YOU (sorry to shout, been trying to figure this out for years).

  6. Anna Kamphaus says:

    How to edit the legend scale:

    Change surface chart to 3-D view, then edit the vertical axis
    Changing the min and max values will change the legend values, also you can edit the interval.
    Then change back to 2-D

    :-)

  7. hi
    i am trying using vba to mark the “right angle axes” option in 3-D formating of a chart – can any one help please?

  8. Amit:

        With ActiveChart
            .RightAngleAxes = True
        End With
  9. Hello,

    thank you for the help, I have been struggling with contour maps for ages.

    BUT….. my drop down menu does not have the field ‘vertical (value) axis’… its just not there!!

    I would appreciate any help on this.

    Thanks,
    Libby

  10. Libby – What are the names of the three axes in the drop down?

  11. Hello,

    I solved it!! I thought I had the VBA tool pack installed, but I didn’t!! So I have done that now, and it does show up.

    Thank you!

  12. Trying to update some code that worked in XL 2003. Specifically, would like to have ActiveChart.Legend.LegendEntries(i).Interior.ColorIndex = 13 still work (deprecated in XL 2007, and crashes after a while). Any suggestions? Thanks.

    Ben

  13. Ben -

    The documentation was rather slim (it’s post-2003 Office, after all), but I came up with this:

    ActiveChart.Legend.LegendEntries(i).LegendKey.Interior.ColorIndex = 13

  14. Jon,
    It appears that there is a ~32,000 colorized pixel limit for the contour surface chart because the plot height produced automatically varies as I supply 5 columns of values with 15, 30, 60 rows that are interpolated to color the values in the box with my 75, 150, 300 values.
    Am I understanding some subtle aspect? Or having an ID-10-T issue? john

  15. Jon,
    I am trying to get rid of 3D lighting effects in contour plot, so your tip is very valuable. Unfortunately, I cannot find Format Band dialogue where you recommend to set the option. How do i get there?

  16. Yourii -

    Yeah, I left out that part.

    Click once on the legend, then click once on the label of the band you want to format. Then right click on this label and choose Format Band from the pop-up menu.

    I just tried writing a little macro to do all of the bands in the chart, but the command that the macro recorder recorded produced errors when it was executed. Wonderful.

  17. Hi Jon,
    Thanks for clarification. Yes, indeed that works but it is pretty cumbersome to adjust every single band in the plot.

    Here is a VBA code that I found in
    http://www.pcreview.co.uk/forums/3d-shading-t3069203.html
    that seems to work for me

    ActiveSheet.ChartObjects(“Chart 6″).Activate
    ActiveChart.ChartGroups(1).Has3DShading = False

    Yourii

  18. Thanks Yourii, I knew there was a way. It’s frustrating when the exact same syntax from a recorded macro fails.

  19. I am trying to change the values on a contour plot using Excel 2007. I am graphing a problem in electromagnetism, and I want to instruct Excel to allow me to change the scaling it displays.

    Right now, it displays the values from 0 to 150 in increments of 50; I would like Excel to display the values from 0 to 150 in increments of 15.

    Can you instruct me on how to make this change?

    Thank you,

    David

  20. David -

    The vertical axis is still there, but you can’t see it or click on it.

    Select the chart, then click on the Chart Tools – Layout ribbon tab. Select the Vertical Axis in the current selection dropdown at the top left of this ribbon. Press Ctrl+1 to format the axis.

  21. Is it possible to build a surface graph mapping 2 or more different surfaces on the same chart? If so, it would be helpful to know how to set the suface properties, like color and transparency, so you could see “above” and “below” intersecting surfaces, as well as being able to set the format of the intersecting curve of the surfaces.

  22. Dan – You only get one surface in a surface chart.

  23. Hi Jon,
    Have a contour plot set up, and would like to show irregular increments. i.e. instead of 5, 10, 15, 20, 25% etc would like to be able to show 5, 10, 20, 40% only. Is there a way to do this?
    Thanks!
    Mark

  24. Mark -

    Maybe the best you can do is use 5% increments, but then format 15 and 20 the same, 25 through 40 the same, etc. It’s not exactly what you want, but it’s all these axes can do.

  25. Good idea – thanks for that.
    Mark

  26. Great explanation. It all worked fine. BUT I have square crosshatching over part of all of one band and part of another. It is not gridlines, as I have changed them to red and they are not this cross hatching. It is not patternd fill as I have put red paterned fill in and it is not it.This cross hatching corresponds to the axis tick marks, but adding and removing tick marks only changes bits across the axis. Clicking on a hatch line selects the chart area.

    What a pain.

  27. Found it. I had a solid border colour line set for one band.

  28. Hi, great blog. I would like to know if it is possible to show irregular increments for the HORIZONTAL & DEPTH axes grid lines (floor axes) for a contour chart? (someone already asked for the vertical axis).

    I can see excel plots equally spaced intervals no matter what the numbers of the labels are, which for me it’s not an accurately visual representation of what I want to plot (labels are 0.25, 0.5, 0.9, 1.3, 2.1, …). Do I need to insert rows and columns and interpolate the data? I hope there’s another way.

    Thanks for your help

  29. Roberto -
    Those are category axes, which means you’re stuck with equal spaced intervals.

  30. It’s sad to hear that, but many thanks for your response Jon.

  31. Hey, thanks for this advice, i spent a number of hours to find out how to flatten that sh*tty contour. Much appreciated !!

  32. It is not clear where To change the scale of the bands, format the legend. The Format Legend dialog has a Scale tab just like in a typical Format Axis dialog. You can’t see the “value” axis, but you can select it in the Chart toolbar’s Chart Element dropdown, and format its scale directly.

    The example that you give cannot be found by me
    In every village, when asking for directions it is straight on and very near. That you have to go around the church, cross the canal in a boat and walk for three miles is no longer clear to the villagers. I am afraid that many of you suffer from the same syndrom

  33. You need to read more closely. In Excel 2003 and earlier, to change the scale, format the legend, as stated in the article. In Excel 2007 and later, it’s not so easy. You need to select the vertical axis (which you can’t see) using the Chart Element dropdown on the Chart Tools > Layout or Format tab, and format this axis.

  34. I need to adjust the vertical (value) axis to refer to cell(dynamic)…i.e max to A1,min to A2 and major axis/minor axis unit to A3(lets say)…how can it be done…thanks

  35. Salah Al Wahieshi says:

    Dear Jon
    Thanks a lot it was very usefull

Subscribe without commenting

Trackbacks

  1. [...] 参考文献:感谢Jon peltier,http://peltiertech.com/WordPress/contour-and-surface-charts-in-excel-2007/ [...]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites