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:
- Click the chart legend to select it
- Click the first legend key (that is the small colored square)
- Right-click and choose Format Legend Key
- In the Format Legend Key dialog box, click the Patterns tab
- 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.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks