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


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


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.


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


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


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 Charts for Excel


  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.


  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.


  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
    that seems to work for me

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


  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,


  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?

  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.

  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

  36. There was a question about the spacing of the grids in the contour plots. They are not quite as they seem and in fact I believe the original programmers fell for the ‘fencepost problem’ when working out how to arrange the grid.
    I was trying to reverse engineer this and made an interesting discovery. It goes like this:

    Excel wants to maximise the contour plot into the window available (in fact into the PlotArea).

    So it takes the available plotarea height and width removing the space required for axis labels etc and sees which one is the constraining one in terms of the grid of points to be plotted. Lets say it is the height.
    So if there were 8 rows of data it divides the available height by 8 to get the height of each cell – but they SHOULD HAVE divided by seven not eight.This will be the width of the horizontal cells. But then they realised that the new size vertically does not quite fill the space so they multiply the height by 8/7 to fill it up. They did a similar rescaling of the width.

    The long and short of it is this. If you have a grid of M rows by N Columns of data that should make a plot of M-1 by N-1 ‘squares’. But the cells will be of size:
    CellHeight=TotalHeight / (M-1) CellWidth = TotalHeight / M * (N/(N-1))

    If the fence post problem had been known the formula for the width should have been
    CellWidth = TotalHeight / (M-1) ie the same as the height
    Cells would then have been square.
    As it is a cell in a plot will have a height to width ratio of M/(M-1) * (N-1)/N :1
    If M=N no problems, but as M and N become more different the ‘squares’ approach 2:1 or 1:2 in size
    So M=2 and N=6 gives a shape of 5:3 N=9 gives 16:9 N=18 gives 17:9 etc.

  37. Bob –

    I haven’t worked with contour plots in a long time. My first impulse was to say you’re crazy, and the grids are always square. But in my old age I’m more inclined to listen to what people say, and see if it has any merit.

    So I set up as series of contour charts using a data range with four rows of data (capital letters along each contour chart’s vertical axis) and four to fourteen columns of data (lower case letters along each contour chart’s horizontal axis). The data is irrelevant, but the charts are shown below. The first chart has square grids, but in each successive chart the grid cells are progressively taller than they are wide.

    I’ve summarized in the following table. The third column shows (R-1)/(C-1), the ideal height-to-width ratio of the plotting region of the chart, and the fourth shows R/C, which is uncorrected by consideration of the fencepost problem. The seventh column shows H/W, the actual height-to-width ratio of the chart. You can see that this ratio is within a couple percent of R/C instead of (R-1)/(C-1). The last column, “Distort” shows the ratio between the incorrect and correct aspect ratios. This distortion reaches 24% in the last chart.

    I last worked with contour charts extensively in Excel 97. My computer that had Excel 2003 on it (with the same ancient chart engine as Excel 97) gave up the ghost earlier this week, so I can’t easily check this analysis. I have to think those charts had square grids, or I would remember from my earlier work.

  38. I couldn’t stand it, so I fired up an old laptop and tried out the contour charts in Excel 2003. The results:

    I just did the extreme examples. You can see, though, that in Excel 2003, the aspect ratio of the cells matched the ideal 1:1 within a couple percent. The grid is obviously square.

  39. Wow some work done over night.
    Unfortunately I read the 2003 version reply first – interesting. I am using 2011 so it seems the engine must have changed on both platforms about that time. I just checked with an Excel Mac 2004 and indeed I get square grids on that version.

    The reason for the exercise was to try and create an overlay of an XY plot on a contour plot. This cannot be done ‘naturally’, but by creating two plots (A contour and an XY) of the same chartarea dimensions (well actually similar not same) and setting the XY backgrounds to transparent one can achieve this manually. But could one automate? Pretty messy I decided. Even more so by this M/N vs (M-1)/(N-1) issue

    Thanks for the effort Jon – have always admired your work.
    Bob Jordan

  40. Thank you for the detail about how to “flatten” the colors. I had tried the “material” but unfortunately was frustrated and didn’t systematically try them all. I was starting to think it was an optical illusion. I painstakingly mapped colors from a thermal plot I liked, using RGB and transparency, then with further pain adjusted the color of every band (I had 7). It was not right. You should have seen me. I was holding my hands up to my screen to mask out little squares to see if the color looked right in isolation. As I noted, I thought it was in my head. Thanks again.

Speak Your Mind


Peltier Tech Charts for Excel 3.0