Excel Developer Tip

Excel page

Tip archives

Removing Lines From a Surface Chart

By default, Excel's surface charts display black lines between each color gradient. It's possible to remove those lines, but Excel makes you jump through quite a few hoops to do so.

Each line must be removed separately! Here's how to do it:

1. Click the chart legend to select it
2. Click the first legend key (that is the small colored square)
3. Right-click and choose Format Legend Key
4. In the Format Legend Key dialog box, click the Patterns tab
5. Select None for the Border option, and click OK

Repeat those steps for every series in your chart.

As with most tedious operations in Excel, you can automate this with a VBA macro:

Sub RemoveSurfaceChartLines()
    Dim LE As LegendEntry
    If ActiveChart Is Nothing Then Exit Sub
    If ActiveChart.ChartType <> xlSurface Then Exit Sub
    If MsgBox("Remove lines from surface chart?", vbYesNo) = vbYes Then
        Application.ScreenUpdating = False
        ActiveChart.HasLegend = True
        For Each LE In ActiveChart.Legend.LegendEntries
            LE.LegendKey.Border.LineStyle = xlNone
        Next LE
        ActiveChart.ChartGroups(1).Has3DShading = True
    End If
End Sub

To use this macro, copy it and paste it to a VBA module. Then, select a surface chart and press Alt+F8 to display the Macro dialog box. Execute the RemoveSurfaceChartLines macro, and your chart will be transformed, as shown below. Note that the final statement in this procedure turns on the chart's 3D shading option (which gives the chart a much nicer appearance.

SURFACE CHART TIP:
The number of colored gradients in a surface chart is determined by the Major Unit setting for the vertical axis. Double-click the vertical axis to display the Format Axis dialog box. Select the Scale tab and change the value for the Major Unit. The larger the number, the fewer the colors.