Problem
You want to create a 3D surface plot in Excel (e.g., to present results on a multidimensional optimization study or perhaps to present topographic data).
Solution
Use Excel's builtin Surface chart type.
Discussion
Excel's Surface chart allows you to create plots that can be viewed from various angles.
In Excel, surface plots are like a collection of line plots that get connected to form a surface. Thus, they suffer from some of the limitations of line plots. In particular, the values to be plotted (we'll call these the zdata) must be uniformly spaced along the x and yaxes. You can't have arbitrarily spaced data as you can in XY scatter plots.
Figure 421 shows some sample data in the proper form for a 3D surface plot. The sample data shown here represents results from a 1D finite element simulation of nonlinear, nonNewtonian fluid flowing through a porous material.
Figure 421. 3D surface plot data
The Node column represents the finite element node at which data was calculated. This column also represents values (or categories) that will be displayed on one of the chart axes. The row of text showing t=0, t=0.01002, and so on represents the other axis; more precisely, these are labels to be displayed on the other axis. Excel will display these labels (for both axes) uniformly spaced on the chart, so I purposely extracted data at regular time intervals so the chart would make sense.
The values in the cell range starting at B3137 and ending in the lowerright corner represent the zvalues to be plotted as functions of node and time. These values represent the velocity of the fluid flowing through the porous material in this example.
To create the plot, select the entire range of cell data, including the label column and row (i.e., the Node column and the row containing the elapsed time labels). In this example, the complete range is A3136:AD3147. After selecting the data, click the Chart Wizard button and go through the Chart Wizard as discussed in Recipe 4.1; however, this time select Surface for the chart type. The results are shown in Figure 422.
Figure 422. 3D Surface chart
Here you can see that one axis on the horizontal plane contains node number labels, while the other contains the elapsed time labels. The surface represents fluid flow velocity at each node over time. The surface clearly shows a lot of information in a concise fashion, facilitating interpretation of the results.
The colors shown on the surface represent values within small ranges; you can set these to control the number of different colors used on the plot. To do this, you need to format the legend's scale in much the same way as you would adjust the scale of an axis as discussed in Recipe 4.4. Specifically, you can adjust the major and minor units (see Figure 411) to control the number of colors used to represent small ranges of values in the plot.
To format the legend's scale, you'll first have to display it. Rightclick on the chart and select Chart Options from the menu to display the Chart Options dialog box. Click the Legend tab and then check the Show Legend control.
After closing the Chart Options dialog box, select the legend and press Ctrl1 to open the Format Legend dialog box. Click the Scale tab to reveal scale controls like those shown in Figure 411. Adjust the scale to suit your needs.
Figure 423 shows a new version of the chart from Figure 422, with a reduced number of colors.
Figure 423. 3D Surface chart with adjusted legend scale
If you set the major and minor units too small, you could end up with a large number of colors and items in the legend. This may make the legend too big to display on your chart. If you want to keep such a large number of colors, then you'll probably want to hide the legend.
You can format the legend and data series even further. After selecting the legend, click on any legend entry to select it (you'll see selection handles around it). Rightclick on it and select Format Legend Entry from the popup menu. You'll then be able to change the formatting of the legend entry font. With a legend entry selected, click on its legend keythe little colored square next to the legend entry text. Now rightclick on the key and select Format Legend Key from the popup menu. You'll then be able to change the formatting of the legend key. For example, you can change its color or fill pattern, which also affects the chart. This allows you to set specific colors for the values displayed in the chart. In the Format Legend Key dialog box, you'll also find an Options tab. Click it to reveal additional formatting options that affect the entire chart. For example, you can change the depth of the chart or set a 3D shading effect, giving the chart more of a 3D look.
3D Surface charts can also be useful for plotting 3D analytic functions to visualize their form. For example, the plot of the function
looks like that shown in Figure 424.
Figure 424. 3D Surface chart of sample analytic function
Notice that the x and yaxes (in the horizontal plane) show real numbers. These resemble axes in an XY scatter plot, but they are still just category labels here. The trick to pulling this sort of plot off is to make sure you perform your calculations at uniformly spaced x and yvalues.
Excel's Surface chart type also gives you the option of displaying wireframe representations of surfaces such as that shown in Figure 425.
Figure 425. Wireframe 3D Surface chart of sample analytic function
Sometimes, wireframe views are more effective in revealing the structure of such a surface without the distraction of all the colors.

Using Excel
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Charting
Statistical Analysis
Time Series Analysis
Mathematical Functions
Curve Fitting and Regression
Solving Equations
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations
Index