5. Creating Charts That Show Relationships

Comparing Two Variables on a Chart

The chart types discussed in this chapter are unusual. Scatter, radar, bubble, and surface charts are probably the least understood Excel charts.

Both scatter and bubble charts show the interplay between two or three different variables. They require a bit of care in setting up the data. A scatter chart can help you to figure out whether there is a correlation between two variables. A bubble chart has the unique ability to provide data about a third dimension. The first section in this chapter covers the mechanics of creating scatter charts.

When you are thinking about creating scatter charts to show a relationship, you might need to consider alternative charts such as a paired bar chart, paired chart, or frequency distribution. The second section in this chapter compares the message that you convey with the various chart types. That section describes charts created by charting celebrities such as Gene Zelazny, Kathy Villella, and Alfred E. Neumann.

Radar charts are rarely used, but they are great for providing performance reviews. For example, a radar chart can show how a person scored on 5 to 10 key indicators as spokes emanating from a central point.

Surface charts actually attempt to show a 3-D surface floating above an x,y grid. It is difficult to find a dataset that looks cool with a surface chart, and after you have found an appropriate dataset, it is often difficult to make out the valleys that might occur within the dataset.

The first part of this chapter discusses scatter charts in detail. Although it is easy enough to create a simple scatter chart, many annoying complications can occur when you want to add new data series to the chart or when you want to try to label the chart.

Using XY Scatter Charts to Plot Pairs of Data Points

Figure 5.1 shows a table of average January temperatures for selected U.S. cities. There is no trend or pattern to this data.

Figure 5.1 When this data is plotted on a column chart, there is no pattern to the data.

What are the likely causes of variability in January temperature? You might theorize that the most likely cause would be distance from the equator. In Figure 5.2, a new Column B shows the latitude of each city. This dataset is perfect for an XY scatter chart. The latitudes in Column B represent an independent variable. The temperatures in Column C represent a dependent variable. A scatter chart plots one data marker for every pair of latitude and temperature data. In this case, the latitude will be plotted along the x-axis, and the temperature will be plotted along the y-axis.

Figure 5.2 You can plot latitude and temperature on a scatter chart to understand the relationship between the values.

The chart in Figure 5.2 shows a relationship between latitude and temperature: as latitude increases, temperature decreases. You can therefore say that latitude and temperature are inversely related.

Adding a Trendline to a Scatter Chart

To add a tight grid and a trendline to a scatter chart, select Layout 3 from the Chart Layouts gallery on the Design tab. The trendline represents the best-fit line, given the data points. If the trendline is angled diagonally from bottom left to top right, it indicates that the x and y variables appear to be directly related. If the trendline is angled from top left to bottom right, it indicates that the x and y variables appear to be inversely related. In Figure 5.3, the trendline confirms that there is an inverse relationship between temperature and latitude.

Figure 5.3 Add a trendline to confirm the inverse relationship.

Excel uses a best fit to draw the trendline. Even when there is absolutely no correlation at all between the columns of data, Excel forces a line to fit. In Figure 5.4, the x and y data is random. A horizontal trendline represents the average of all the dots, but it does not indicate any correlation.

In Figure 5.4, Excel has added the equation for the trendline and the R-squared value. An R-squared value close to one indicates a near-perfect correlation. An R-squared value of zero confirms that there is not a correlation between the x and y values. To add this equation to your chart, follow these steps:

1. Select the chart.
2. On the Layout tab, select Trendline, More Trendline Options.
3. In the Format Trendline dialog that appears, select Display Equation on Chart and Display R-squared Value on Chart. Both settings are near the bottom of the dialog, as shown in Figure 5.5. Alternatively, choosing Layout 9 from the Charts Layout gallery on the Design tab will turn on the equation and R-squared.

Figure 5.4 Although a scatter chart of random data might produce a trendline, there is no correlation.

Figure 5.5 Add a trendline formula to the chart by using this dialog.

Adding Labels to a Scatter Chart

When you create most other charts in this book, the first column contains labels. In Figure 5.2, there are city names in Column A, but they are not part of the dataset. This is annoying. When you click a data point and choose to display the label, you can see that the data point is at a 47-degree latitude and has an average temperature of 37.2. However, you have to scan through the original dataset to see that the data point in question is from Olympia, Washington (see Figure 5.6).

Figure 5.6 It would be good if you change the label from the x and y values to the label in Column A.

One solution is to write the following VBA macro code:

Sub AttachLabelsToPoints()
   Dim Counter As Integer, ChartName As String, xVals As String

   'Store the formula for the first series in "xVals".
   xVals = ActiveChart.SeriesCollection(1).Formula

   'Extract the range for the data from xVals.
   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
      Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
   Do While Left(xVals, 1) = ","
      xVals = Mid(xVals, 2)
   Loop

   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals).Cells.Count
     ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
         True
      ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
         Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
   Next Counter

End Sub

If you select the chart and then run this macro, Excel displays the label next to each data point, as shown in Figure 5.7. This is a useful addition to the chart.

Figure 5.7 Relevant labels for XY charts are an asset.

Joining the Points in a Scatter Chart with Lines

Of the five types of scatter chart types in the Change Chart Type dialog shown in Figure 5.8, four are dots joined using lines.

Figure 5.8 Most of the scatter charts are dots joined with lines.

Whereas the dot version of a scatter chart can be sequenced in any order, chaos will result if you try to join the dots with lines. In Figure 5.9, a familiar curve turns to spaghetti when the dots are joined with lines.

In Figure 5.10, using a line to join exactly the same dataset as in Figure 5.9 appears orderly.

The bottom chart in Figure 5.10 uses Scatter with Smooth Lines and Markers. The top chart in Figure 5.10 uses Scatter with Straight Lines and Markers. The difference is barely perceptible—the line in the bottom chart is just slightly smoother.

Figure 5.9 Chaos results when an unsorted XY chart is joined with lines.

Figure 5.10 If your data is sorted into ascending sequence by the x value, either a straight line or smooth line will work.

If you use Scatter with Straight Lines and Markers when fewer data points are available, the curve is not as well defined, as shown in the top chart in Figure 5.11. In contrast, you can use Scatter Chart with Curved Lines and Markers to have Excel extrapolate and fill in the curve, as shown in the bottom chart in Figure 5.11

Adding a Second Series to an XY Chart

It is somewhat unintuitive to plot a pair of XY series on a single chart: You create a chart for a single series and then use the Select Data icon to add the second series.

Follow these steps to create an XY chart with two series:

1. Start with an XY series in two columns. For example, on Figure 5.12, the XY series is in Columns B and C.

2. Add the label for this series in the cell above the y points column, such as the No College heading in C1.

Figure 5.11 It is important to use a smoothed line with sparse data.

Figure 5.12 Start with a chart with one XY series.

3. Create an XY chart from the data in B1:C24. The initial legend is No College. Leave this legend as it is.

4. Change the title from No College to a title that covers both series.

5. Enter a new series in Columns F:G. Enter the label for the second series in cell G1.

6. Click the chart to select the chart and bring back the Charting Tools tab.

7. From the Design tab, click the Select Data icon. Excel displays the Select Data Source dialog.

8. Click the Add button in the Legend Entries section of the dialog. Excel displays the Edit Series dialog.

9. Click in the Series Name box and select cell G1.

10. Click in the Series X Value box. Highlight cells F2:F15 in the worksheet.

11. Click in the Series Y Values box and backspace to remove the default value of 1. Click the Refers To box and highlight G2:G15. Excel adds sheet names and the dollar signs to make the references absolute, as shown in Figure 5.13.

Figure 5.13 The new series is specified in the Edit Series dialog.

12. Click OK to close the Edit Series dialog. Click OK to return to the worksheet.

The final chart is shown in Figure 5.14.

Figure 5.14 This chart compares two XY series.

Drawing with a Scatter Chart

An unusual use for XY scatter charts with straight lines is to make crude drawings on a chart.

To create a drawing, it helps to trace your drawing onto quadrille graph paper first. You need to choose a point where the left edge and bottom edge cross, which, in this case, will be the (0,0) point. For every point in the drawing, you enter the x and y location. The x location is the number of gridlines from the left edge of the chart to that point. The y location is the distance from the bottom of the chart to the point.

If you need to draw a curve, you can choose as many points as possible around the perimeter of the curve. The more points you can plot, the smoother the curve.

Figure 5.15 shows an x and y dataset.

Figure 5.15 Transfer points from a drawing into columns for x and y.

Next, select the data from A2:B16. From the Insert tab, you select Insert, Scatter, Scatter with Straight Lines, which is the thumbnail with straight lines and no markers. Excel creates the chart shown in Figure 5.16.

Figure 5.16 Excel connects the x and y points to create a simple drawing.

→ See Figure 7.43 in Chapter 7, “Advanced Chart Techniques,” for an over-the-top example of this technique.

Using Charts to Show Relationships

You can use several types of charts to illustrate the relationship between two variables, including the following:

• Scatter charts are particularly well suited when comparing two variables that you think are related. Although you can use scatter plots with large datasets, they are limited in that it is difficult to label the points without using some VBA or an add-in. If you have a small dataset, you can use a paired bar chart (turn back to Figure 4.4 for one example or forward to Figure 5.21 for another example) to see the same information. This chart also adds a label so you can see which records are not in line with the others.

• In some cases, a pair of matching charts allows the reader to compare two variables to see whether there is a relationship.

• In the real world, it is rare to find a value that is influenced by only one variable. Usually, a whole host of factors contributes to a result. A bubble chart is a special type of XY scatter chart that attempts to show the interplay between three variables. It is best to use bubble charts for small datasets.

• Radar charts are rarely used charts that allow you to see the relationship between four to six variables.

• In some cases, you might have a population of results and be trying to figure out whether any patterns exist in one variable. In these cases, use of the FREQUENCY function enables you to make sense of the data by grouping members into similar categories and then comparing the categories.

Testing Correlation Using a Scatter Chart

The dataset for a scatter chart is composed of two variables for every row in the dataset. The values in the first column are plotted along the horizontal axis. The values in the second column are plotted along the vertical axis. The heading for the second column becomes the name of that series.

In Figure 5.17, the data in B2:C15 represents a price survey of regional car dealerships for a particular model of vehicle. Each pair of numbers in a row represents the data for a single car. Mileage is in the first column, and price is in the second column. In theory, you would expect that as the miles go up on a car, the price would come down.

If you select the data from B2:C15 and insert a scatter chart, Excel provides the chart shown in the top of Figure 5.17. The chart needs some labels to tell the complete story. Follow these steps to format the chart to look like the bottom chart in Figure 5.17:

Figure 5.17 The default scatter chart at the top requires some formatting before it can be useful.

1. From the Layout tab, select Legend, None to remove the legend.
2. From the Layout tab, select Axis Titles, Primary Horizontal Axis Title, Title Below Axis to add the words Axis Title below the horizontal axis.
3. Click Axis Title and type the new axis title Mileage (in thousands).
4. From the Layout tab, select Axis Titles, Primary Vertical Axis Title, Rotated Title to add the words Axis Title to the left of the vertical axis.
5. Click the Axis Title and type the new axis title of Price (in $000).
6. Double-click the prices along the y-axis to display the Format Axis dialog. In the middle of the dialog, select Thousands for the Display Units and clear the Show Display Units On Chart check box.
7. While still in the Format Axis dialog, click Number in the left navigation bar. Select Currency with 0 decimal places.
8. Click the title and type a more descriptive title such as Price as a Function of Mileage.

In Figure 5.17, it appears that there is a fairly strong relationship between mileage and price. Because the dots slant from top left to bottom right, the variables have an inverse relationship.

You can ask Excel to do a least-squares regression and fit a trendline to the points plotted on the chart. One of the trendline options is to display the regression equation and the R-squared value on the chart.

Figure 5.18 shows three different scatter charts. The top chart has a perfect correlation and an R-squared value of 1. The next chart has just a small bit of variation. Individual points appear close to the trendline but are sometimes a bit above or below it. This provides an R-squared value of 0.995. The bottom chart looks like a shotgun blast. There does not appear to be any correlation between those variables, and Excel reports an R-squared value of 0.003. If the dataset is truly random, the trendline is often a straight horizontal line, drawn through the value that marks the average of all of the points.

Figure 5.18 R-squared is a measure of how well a trendline fits the points in a chart.

There are a few different ways to add a trendline and the R-squared value to a chart. Follow these steps to see one way:

1. Select the chart.
2. From the Layout tab, select Trendline, More Trendline Options.
3. Select Linear as the trend/regression type.
4. In the bottom of the Format Trendline dialog, choose either Display Equation on Chart or Display R-squared Value on Chart.
5. If you want only the R-squared value on the chart, select the Line Color style and move the Transparency slider to 100 percent.

In Figure 5.19, the R-squared value has been added to both charts. Price and mileage have an R-squared value of 0.849. Price and age have an R-squared value of 0.944. While both sets have correlations, the age of the car seems to have more of an impact on price than does mileage.

Figure 5.19 Based on the R-squared values, age is a better predictor of price than is mileage.

Using Paired Bars to Show Relationships

A scatter chart lacks a straightforward way to label the points with identifying names. If you need to compare two variables and have around a dozen points, a paired bar chart can be more effective than a scatter chart. For example, Figure 5.20 shows a preference for ice cream, based on a survey of two groups: kids and adults.

The chart is sorted by the kids’ preference. Any bars toward the bottom of the chart that have a sizable presence on the right are flavors that grow in popularity as the respondents mature.

Figure 5.20 A paired bar chart allows you to compare values for two populations.

To create this chart in Excel, you need to use a little trickery in several steps. Since this is a stacked bar chart, the values for the left bar have to be in the Excel table as negative to force them to stretch leftward from the axis.

Follow these steps to create the chart in Figure 5.20:

1. Sort your original data by the first series so that it is in descending order.

2. Copy the dataset to the right of the original dataset. In the copy of the dataset, enter the formula -B39 in Cell F39 to create a negative value for the first series (see Figure 5.21).

Figure 5.21 Use a formula to make the first series negative.

3. Select the data for your chart. Press Ctrl+1 to display the Format Cells dialog box. On the Number tab, select the Custom category and type the custom number code 0;0. Click OK. The negative numbers in Column F are displayed without the minus sign.

4. From the Insert tab, select Bar, Stacked Bar. Excel creates the chart shown in Figure 5.22.

5. From the Layout tab, select Legend, Top to move the legend to the top. Moving the legend changes the legend to a landscape orientation, with the first series on the left and the second series on the right. This arrangement matches the data in the chart.

Figure 5.22 Excel displays the start of a paired bar chart.

6. Resize the chart vertically to allow enough room for each category name to appear along the axis.

7. Select Vertical (Value) Axis from the Current Selection drop-down on the Layout tab. Then select the Categories in Reverse Order check box, which is the fourth selection in the Axis Options panel.

8. (Optional) The initial chart is not quite symmetrical, running to 200 on the kids’ side and to only 150 on the adults’ side. To correct this, specify a fixed minimum of –200 and a fixed maximum of 200.

9. (Optional) Initially the category labels overwrite the left bars. You have to decide how annoyed you are by this. It might be best to keep the labels there. If you want to move them to the left, select Low from the Axis Labels drop-down.

To see a demo of creating this paired bar chart, search for “MrExcel Charts 5” at YouTube.

This example creates a chart that plots a frequency distribution for two populations. While it allows you to compare the preferences of the populations, the case study that follows provides another example of a paired bar chart that lets you examine the correlation between two variables, just as you would do in a scatter chart.

The following section describes an example of a paired bar chart.

Adding a Third Dimension with a Bubble Chart

A bubble chart attempts to add a third piece of information to each point in an XY scatter chart. In a bubble chart, the size of the marker varies, based on the third data point for each marker.

The best time to use a bubble chart is when you have a sparse dataset. The size of the bubbles makes it difficult to read the chart when you have too many data points on the chart.

Figure 5.29 shows a bubble chart. The size of each bubble is based on the selling price for a particular model of car. The location of the bubble along the horizontal axis indicates the age of the car. The location of the bubble along the vertical axis shows the mileage of the car.

In theory, cars that are older or have higher mileage should have lower prices. However, you can find some bubbles where the seller of an older car is asking for more money.

Here are some tips for creating bubble charts:

• You should always leave the heading off the top-left cell in the data range. This rule applies any time you have numbers as the first column of your dataset. It is particularly important with bubble charts.

• The initial size of the bubbles is always too large since it is initially scaled to 100 percent. If you see too much overlap in the bubbles, you can right-click a bubble and select Format Data Series. In Figure 5.29, the bubbles are scaled down to 30 percent to prevent excessive overlapping.

• There is an option in the Format Series dialog to have the size data translated into the area of the bubble or the width of the bubble. You should always choose the area of the bubble.

Figure 5.29 This bubble chart shows the relationship between age, mileage, and price.

In both charts shown in Figure 5.30, each bubble is two times larger than the previous bubble. The top chart includes the option that the size of data in the third column affects the area of the bubble. This is the default setting, and it is the correct setting. If you instead decide to tie the data in the table to the width of the bubble, you violate the pictograph rule. When you are using pictures for markers, you should increase the marker in only one dimension, not in both dimensions.

Figure 5.30 The width setting in the bottom chart creates the false impression that the bubble is four times larger than the previous bubble.

To create the chart in Figure 5.29, follow these steps:

1. Set up your data with age in Column A, mileage in Column B, and price in Column C. It is okay to have headings above the data in Columns B and C.
2. Select cells A1:C15.
3. From the Insert tab, select Other Charts, Bubble, Bubble with a 3-D Effect.
4. Right-click one bubble and select Format Series. Change the Scale Bubble Size value from 100 to 30.
5. Right-click the numbers along the vertical axis and select Format Axis. Change Minimum to Fixed, 0.
6. From the Layout tab, select Legend, None.
7. From the Layout tab, select Chart Title, Above Chart. Click the title and type a new title.
8. From the Layout tab, select Axis Titles, Primary Horizontal Axis Title, Title Below Axis. Click the axis title and type the new title Age (Years).
9. From the Layout tab, select Axis Titles, Primary Vertical Axis Title, Rotated Title.

When you need to show the relationship between three variables and you have only a few points to compare, a bubble chart will create an effective presentation of the data.

Using a Frequency Distribution to Categorize Thousands of Points

Suppose your dataset had results of 30,000 trials. Figuring out how to present this data can be difficult. The chart in Figure 5.31 is a first attempt. The data is sorted by the trial number in Column A. A line chart is based on Columns A and B. You cannot tell much of anything from this chart. You can tell that the range is from 20 to 100, but that is about it.

Figure 5.31 This cannot be the best way to plot this data.

If you sort the data by the result field in Column B, the chart changes into a smooth line, as shown in Figure 5.32. Again, you can tell that the range is from 20 to 100 and that for 60 percent of the chart, the data ranges from 40 to 60.

To create a more useful chart with this data, you need to use a somewhat obscure function. You use the FREQUENCY function to group the data into bins. This function is one of the few functions that returns several different answers all at once. These functions, called array functions, require special care and handling.

Figure 5.32 Some might be able to draw conclusions from this, but this chart is not obvious.

Creating Bins

To make sense of the data in Column B in Figure 5.32, you need to group the results into equal-sized bins. You need to type limits for each bin in a range, going down a column of the worksheet.

If your first bin is the number 0, the FREQUENCY function shows all the trial results less than 0 next to that bin. If the next bin is the number 15, the FREQUENCY function shows all the trial results from the last bin (0) to the current value (15).

To create bins that contain ranges of 15 units each, follow these steps:

1. Type 0, 15, 30, 45, 60, 75, 90, 105, in cells E2:E9. Remember that this range contains eight cells.

2. Because the FREQUENCY function returns one more value than the number of bins that you have, select the empty cells F2:F10, as shown in Figure 5.33. The additional bin is for any results larger than your last bin value.

3. Type =FREQUENCY(.

4. When Excel asks for the data array, enter the trial results from B2:B3001. Type a comma.

5. When Excel asks for the Bins array, enter the values from E2:E9.

6. Type the closing parenthesis, but do not press Enter (see Figure 5.34).

Figure 5.33 It might feel strange, but you are entering one formula in this range.

Figure 5.34 It might feel strange, but you are entering one formula in this range.

7. Hold down Ctrl+Shift while pressing Enter. Excel returns all nine answers at once. You now have one formula entered in nine cells.

Take a look at the results of the formula shown in Figure 5.35.

Figure 5.35 These results make sense only if you understand how the bins work.

You have to “know the code” in order to figure out what is going on here. There is a 0 next to the 0, and there is a 10 next to the 15. This means that none of the results was less than 0 and 10 of the results were between 0 and 15. At the other end of the range, there is a 34 next to the 105, and there is a 0 below that. This means that there are 34 results between 90 and 105. The final 0 means that there were no results above 105. When you set up the bin range, you should always bracket the expected results with one bin above and below your expected results. Having a 0 end up as the first and last result means that you have accurately captured all your results.

Creating the Frequency Distribution Chart

The results in Column F in Figure 5.35 are somewhat difficult to decode, but you can improve the appearance of the labels along the axis of the chart.

In Figure 5.36, the formulas in H3:H9 concatenate the bins so that they make more sense. The formula =E2&”-”&E3 concatenates the bin in the previous row, a dash, and the bin in the current row. The formulas in Column I copy the results from the current row of the array formula in F.

Figure 5.36 Use formulas to build a table that makes more sense than the previous results.

To create the Frequency Distribution chart, follow these steps:

1. Select cells H3:I9.
2. From the Insert tab, select Column, 2-D, Clustered Column.
3. From the Layout tab, select Legend, None.
4. Normally, there is a fair-sized gap between the columns. If you prefer the columns touch each other, or even that there be less of a gap, you can change the gap width by right-clicking a column, choosing Format Data Series, and then dragging the Gap Width slider to zero percent.
5. Resize the chart so that it is narrower.

Figure 5.37 Frequency charts typically eliminate the gap between columns. This is strictly your preference.

Using Radar Charts to Create Performance Reviews

Radar charts are designed for showing a person’s or a company’s rating along several performance areas. Here are some typical usages:

• Employee performance review—A manager might rate an employee using a one- to five-point rating scale in areas such as efficiency, accuracy, timeliness, and so on. While this data can be presented in a table, a radar chart provides an interesting alternative presentation.

• Customer satisfaction results—A marketing manager could use a radar chart to summarize the results of a customer satisfaction survey. In this case, one line can be used to show customer satisfaction along rating areas such as speed, accuracy, and value.

If you want to summarize customer satisfaction results for two companies, you can present the results as two charts, as shown in Figure 5.38. Alternatively, you can present the results as two series on a single chart, as shown in Figure 5.39.

You can choose to fill in the chart area or to show the series as a line. You should leave the chart unfilled when you put two series on one chart.

You can use a radar chart to compare some kind of results from this year versus the same kind of results from last year. For example, on Figure 5.40, the line for last year is dark black, and the line for this year is a dashed line in a lighter color.

To create the performance review chart shown in Figure 5.40, follow these steps:

1. Using the information in Figure 5.40, enter the categories in Column A, starting in A2. The category in A2 appears at 12:00 on the chart. The remaining categories are arranged in clockwise order.

2. In cells B1 and C1, enter the headings for last year and this year.

3. Enter the 1–5 ratings in B2:C6.

Figure 5.38 With only one series, you can choose to fill in the series.

Figure 5.39 When you have two series, you should not fill in the lines so the reader can see the overlap.

Figure 5.40 Radar charts can compare several values from last year to this year.

4. Select Cell A1:C6. From the Insert tab, select Other Charts, Radar, Radar with Markers.

5. Click the line from this year. From the Format tab, select Shape Outline, Dashes, and then select the fourth choice, called Dash.

All the examples so far show measures that are of similar scale. If you need to show a series that is of a different order of magnitude, you can plot the series on a secondary axis. To do this, select the axis, select Format Axis, and then choose Secondary Axis.

In the celebrity chart described in the following section, the designer used a radar chart for something that is not quite typical. In this case, the designer solves the order-of-magnitude problem by using a logarithmic scale.

Using Surface Charts to Show Contrast

Not many datasets can be plotted as a surface chart, which look like topographic maps. Because you use a surface chart to represent a 3-D surface on a 2-D piece of paper, it is particularly important that your surface be generally sloping toward the front of the chart. Otherwise, you will never see the details hidden by the hill at the front of the chart.

Before you start to build a dataset, you should look at Figure 5.44. The data for the chart is in C3:L12. Each data point requires two headings. Headings for the front axis are in C2:L2. Headings for the side axis are in B3:B12.

Figure 5.44 Study this table and chart to understand how surface charts work.

Data at the top of the table appears at the front edge of the chart. Data in the last row of the table appears at the back of the chart. The Front and Back labels in Column A are there to help you keep track of this.

Data in the left column of the table appears in the left side of the chart. The four arrows point out where each corner of the data table ends up in the chart.

To create this chart, you select Cells B2:L12. From the Insert tab, select Other Charts, Surface, 3-D Surface.

Many datasets are not designed to have the smallest numbers at the front of the chart. For example, in Figure 5.45, the top-left chart has the highest numbers along the front wall of the chart.

Figure 5.45 All four charts represent the same data. Each chart provides a different view angle.

When the front wall of the surface chart is higher than the other points, you can use the techniques that follow to create the other three charts.

Using the Depth Axis

One element unique to surface charts is the depth axis. This is the axis that falls along the right side of the chart in the default orientation.

In the top-right chart in Figure 5.45, the chart has been turned around by having the orientation of the depth axis changed. Follow these steps to create this effect:

1. From the Layout tab, select Current Selection, Depth (Series) Axis.
2. Click Format Selection.
3. Select Series in Reverse Order.

Because the depth axis is treated as a category axis, you do not have control over the minimum or maximum values along the axis. The Format dialog box is limited to settings for the interval between tick marks and labels and where the tick marks and labels appear.

Controlling a Surface Chart through 3-D Rotation

You can spin a surface chart by rotating it. To access the rotation settings, select 3-D Rotation from the Layout tab. This takes you to the 3-D Rotation category of the Format Chart Area dialog, which has the following settings:

• X Rotation—This setting ranges from 0 degrees to 359.9 degrees. It rotates the floor of the chart in a clockwise direction, when you are looking down at the chart from above.

• Y Rotation—This setting starts at +15 degrees. It is your viewing angle in relation to the baseline of the chart. With an angle of 15 degrees, you are looking slightly down at the chart. With an angle of 0 degrees, many of the 3-D effects disappear. As you increase from 10 to 80 degrees, you have slightly different views of the chart. At 90 degrees, the chart becomes flat, as you are looking directly down from above. You can also enter negative values from 0 to –90. As you move from –10 to –80, you look at the chart from underneath. This might allow you to see better detail. When you reach –90, the chart turns flat again, as you are looking directly up at the chart from below.

Perspective ranges from 0 to 120. A value of 0 creates the least distortion. As you increase to 120, the foreshortening increases, creating distortion similar to what you get with an ultra-wide-angle lens on a camera.

Next Steps

In Chapter 6, “Creating Stock Analysis Charts,” you will learn about the process of creating charts to show the performance of stocks and securities. While Excel offers four types of built-in stock charts, they appear dated in light of modern stark charts available on numerous websites. Chapter 6 shows you how to go beyond the four built-in charts to create modern-looking stock charts.