Section 17.2. Improving Your Charts | Excel 2010: The Missing Manual

17.2. Improving Your Charts

So far, you've learned the key techniques you need to make sure your charts tell the right story. However, Excel lets you do plenty more, including adding trendlines, data tables, and error bars, and tweaking 3-D perspective and shapes . In the following sections, you'll learn even more about how to make the perfect chart.

17.2.1. Adding a Trendline

One of the main reasons that people create charts is to reveal patterns that are hidden in the data. For example, a gift card company might look at a historical record of sales to make an educated guess about the upcoming holiday season . Or, a researcher might look at a set of scientific data to find out if potatoes really can cure the common cold. In both these examples, what's most important is spotting the trends that lurk inside most data collections.

One of the easiest ways to spot a trend is to add a trendline to your chart. A trendline is similar to an ordinary line in a line chart that connects all the data points in a series. The difference is that a trendline assumes the data isn't distributed in a perfectly uniform pattern. Instead of exactly connecting every point in a series, a trendline shows a line that best represents all the data on the graph, which means that minor exceptions, experimental error, and ordinary variances don't distract Excel from finding the overall pattern. Figure 17-11 shows an example.

Figure 17-11. This example shows the comparison between age and reaction time that was introduced in the last chapter. The difference here is that the trendline has been extended beyond the actual data to predict results for people who are younger than 40.

The other important point about trendlines is that they can predict values you don't have. For example, the gift card company can use a trendline to get a good estimate of future sales, while a scientific experimenter can make educated guesses about data that wasn't recorded.

A trendline is most commonly used in an XY scatter chart. It also makes sense in a column chart, and it can also work in line, bar, and area charts in specialized circumstances.

To add a trendline, follow these steps:

Select your chart.

If you're using an XY scatter chart (the most common choice), make sure you are using a subtype that doesn't draw lines. Otherwise, the trendline and the chart line may cause confusion. To change the subtype of a chart, right-click the chart and choose Chart Type.
Choose Chart Add Trendline.

The Add Trendline window appears.
Select the Type tab. Now, choose the type of relationship that you expect to find in your data by clicking the appropriate picture.

A Linear trend varies regularly, and it is the simplest possible relationship. For example, if you're driving at a constant speed, the distance traveled increases linearly as the time increases .

Power and Exponential trends start off slowly and quickly ramp up. Logarithmic is the inverseit starts off rapidly and then levels out. These types of trends are more complex, but they are more common than linear trends in natural phenomena. Two example exponential relationships are the change in a population size with successive generations, and the distance traveled in a car if you continuously accelerate.

A Polynomial trend attempts to fit the data by creating an equation with a combination of different terms. This trend is the best choice if there are multiple factors involved, and your data doesn't fit a smooth line or curve.
If your chart has more than one series, choose the series you want to use to create the trendline from the list at the bottom of the tab. Then click OK.

After you've added the trendline, you can right-click it and choose Format Trendline to change the line color , thickness , and style.

A standard trendline fits the data you have. However, you can also extend a trendline forward or backward to fill in values you don't know. This process of estimating data that you don't have (based on data that you do have) is called extrapolation . A closely related concept is interpolation , which estimates unknown data values between known existing values. For example, if a gift card company has information about sales from 2001 to 2003, you'd use extrapolation to predict sales in 2004. To make an educated guess at what sales were like in March 2002 (a month in which your firm lost its sales data), you'd use interpolation. All trendlines necessarily use interpolation since there's always, in effect, "missing" data points between the data points you're providing.

To extrapolate values in a trend, use the Options tab from the Add Trendline dialog box (if you're creating the trendline) or the Format Trendline dialog box (if you want to extend the line after you've created it). Look for the Forecast box, and specify the number of units that you want to add going forward or backward. Figure 17-11 shows an example.

Note: Don't always trust trendlines. It's quite possible that a relationship holds true only over a limited set of values. For example, if you use a rising sales trendline as the basis for guessing future results, Excel's guesses won't, of course, take into account unexpected developments, like limited inventory or rising production costs. Similarly, if you extend the age versus reaction time comparison in Figure 17-11 too far, you'll wind up with ages and values that don't make sense (for example, a reaction time of 0 seconds, or a reaction time for a 300-year-old).

17.2.2. Adding Error Bars to Scientific Data

In a typical scientific experiment, there are two important sets of information: the actual results, and an estimate that indicates how reliable these results are. This "reliability" number is called the uncertainty . The uncertainty doesn't compensate for human error, faulty equipment, or invalid assumptions. Instead, it accounts for the limited accuracy of measurements. For example, think of the typical bathroom scale, which can give you your weight only to the nearest pound . That means there is an uncertainty of 0.5 pounds because any given measurement could be off by that amount. If a scientific experimenter weighs in at 150 pounds, he would record that measurement as 150 ±0.5 (150 pounds plus or minus 0.5 pounds ). Any other calculations based on weight need to take this potential inaccuracy into account.

Because every type of measurement has a different range of accuracy, there's always a certain degree of imprecision that you need to watch out for before you make a dramatic conclusion. In a scientific chart, you can indicate the uncertainty using error bars. For example, if you plot 150 ±0.5 on a chart, you should end up with a point at 150 and an error bar that stretches from the point up to 150.5 and down to 149.5.

To add scientific error bars to a chart, follow these steps:

Right-click the appropriate data series on the chart and choose Format Data Series.

The Format Data Series dialog box appears (Figure 17-12).
Select the Y Error Bars tab.

On an XY scatter chart, you can also select X Error bars to add horizontal error bars.
Choose the type of error bars you want.

Your choices are plus, minus, none, or both, which is the most common.

Set the size of the error bars by choosing one of the "Error amount" settings.

Two simple and useful choices are "Fixed value" (which lets you specify the same measurement of uncertainty for every value), and Percentage (which lets you specify a percentage uncertainty). You can also supply a different fixed uncertainty for each value. In this case, add a new column with this information, choose the Custom option, and specify the range of cells with the uncertainty information (you need one uncertainty value for each data point). Statistics fans can also use two more advanced options, like Standard deviation and Standard error. For definitions of these two weighty concepts, consult your favorite statistics textbook .

Figure 17-12. You can set error bars to use a fixed value (as in this example), a fixed percentage of the value, or you can specify a range of cells in the worksheet that contains the uncertainty value for each measurement.

Click OK to add error bars to your graph.

Figure 17-13 shows a graph with error bars added.

17.2.3. Adding a Data Table

Trying to pack as much information as possible into a chartwithout cluttering it upis a real art form. Some charting aficionados use labels, titles, and formatting to highlight key chart details, and then use the data on the worksheet itself to offer a more detailed analysis. However, Excel also provides a meeting point between chart and worksheet that works with column charts, line charts, and area charts. It's called the data table .

Excel's data table feature places your worksheet data under your chart, but lined up by category. The best way to understand how this works is to look at a simple example, like the one shown in Figure 17-14.

Figure 17-13. This graph attempts to demonstrate a link between age and reaction time, with the error bars indicating that the reaction time couldn't be measured precisely. The errors bars tell us that even though the trendline doesn't fit the data exactly, it lies within the range of measurement error. In this example, you can't distinguish all of the error bars because sometimes the points and error bars overlap (for example, consider the two data points around age 40). On the other hand, it's easy to see the single data point at age 70 and the error bars above and below it.

Figure 17-14. This data table removes the need for a legend or data point labels. However, keep in mind that data tables don't work well with large amounts of data.

To add a data table, right-click your chart and select Chart Options. In the Chart Options dialog box, click the Data Table tab, and then turn on the "Show data table" checkbox.

17.2.4. Formatting 3-D Charts

As you learned in Chapter 16, many charts provide subtypes that are drawn in three dimensions. Some 3-D chart types are no different than their plainer 2-D relatives. In these charts, a 3-D effect simply gives the chart a more interesting appearance. But in true 3-D charts, data gets layered from the front of the chart to the back, with each series appearing behind the other. (For example, in the column chart, there are seven subtypes . The first three are ordinary 2-D charts, the second three use a 3-D effect, and the last one is the only true 3-D chart. For more details about what subtype each chart supports, see the Section 16.4 at the end of Chapter 16.)

In true 3-D charts, you might need to take special care to make sure that data in the background doesn't become obstructed. There are a few tricks that can help, such as reordering the series and simplifying the chart so it isn't cluttered with extraneous data. You might also want to rotate or tilt the chart so that you have a different vantage point on the data.

To rotate or tilt a chart, follow these steps:

Right-click the chart and select 3-D View.

The 3-D View dialog box appears, as shown in Figure 17-15.

Figure 17-15. The 3-D View dialog box lets you twist and turn your 3-D chart. As you make your changes, Excel updates the small wire-frame preview to give you a basic idea of how the chart is oriented. If things go horribly awry, you can always click the Default button to restore the chart to use Excel`s standard perspective settings.

Use the arrow buttons to tilt the chart in various ways, changing the rotation, perspective, and elevation.

The 3-D View dialog box gives you a few additional options that you can experiment with. You can use the Height box to set how tall to make the chart. The Height box specifies a percentage that's compared against the length of the bottom of the chart (the X-axis). In other words, a Height of 100% means the chart will be just as high as the base is wide. A height of 200% means the chart will be twice as high as it is wide. Keep in mind that Excel compares the height against the width of the base, not the width of the entire chart. If you rotate the chart, the perspective makes it appear as though the base isn't as wide as it really as. In this situation, Excel still makes the chart just as tall as if you hadn't rotated it.

If you turn on the "Right angle axes" checkbox, the perspective controls disappear and Excel straightens out the chart by making sure all the corners are square. However, it's hard to tell what this setting will do until you try itdepending on how you've rotated your chart, the act of "straightening" it can be a little unpredictable. Another option is the "Auto scaling" checkbox, which is available only if you've turned on the "Right angle axes" checkbox. If you turn on "Auto scaling" the Height box is grayed out, and Excel uses the largest height that will fit the chart within the box.
Click OK when you're finished.

GEM IN THE ROUGH
Saving Custom Chart Settings

Think you've created the perfect mix of chart formatting, scaling, and shapes? Once you have, you can save it for later use, so that you can apply it to data in a new spreadsheet. Here's what to do:

Select your formatted chart, right-click it, and select Chart Type.

The Chart Type dialog box appears.
Click the Custom Types tab, and choose the " User defined" option below the list of chart types.

An Add button appears on the tab.
Click the Add button, and fill in a name and description for your chart.

The picture here shows an example.
Click OK to add the chart.

Now, the chart appears in the list of user-defined charts. When you select it, the description shows up under the chart thumbnail. You can also use the Delete button to remove one of your user-defined charts.

There is one problem with creating custom chart types. Not only does a custom chart type store formatting information, but it also stores the chart title and axis labels. When you apply your chart type to another chart, Excel replaces the current titles with the saved titles, which can be a little annoying.

17.2.5. Changing the Order of Your Data Series

If your table has more than one series, Excel charts it in the order it appears (from left to right if your series are arranged in columns , or from top to bottom if they're arranged in rows). In a basic line chart, it doesn't matter which series Excel charts firstthe end result is still the same. But in some charts, it does make a difference. One example is a stacked chart, in which Excel plots each new series on top of the previous one. Another example is a 3-D chart, where Excel plots each data series behind the previous one.

Changing the data series is easy, but it's not really that intuitive. The first step is selecting one of the series on your chart. Then, right-click it and choose Format Data Series. In the Format Data Series dialog box, select the Series Order tab. In this dialog box, Excel lists all the data series in the order they appear. Select one in the list, and click the Move Up or Move Down button to change the order.

17.2.6. Changing the Shape of a 3-D Column

Excel provides several subtypes of column and bar charts that use a 3-D effect. Excel also provides the Cylinder, Cone, and Pyramid chart types, which are always three-dimensional (see the description of chart types on Section 16.4 for more information.) It doesn't really make much difference whether you use columns, pyramids , or conesthe overall effect is pretty much the same. However, you create a much more dramatic effect by putting more than one shape in a single chart. For example, if you want to compare two series, you could represent one with columns and the other with cones, as shown in Figure 17-16.

Figure 17-16. To make a dramatic point, why not combine more than one shape in a chart? In this example, cylinders represent cat numbers and pyramids represent the dog count. Creating this chart is refreshingly easyyou just need to change the standard choice in the Shape tab of the Format Data Series dialog box.

To try this out, follow these steps:

Create a column chart with two or more data series.

Make sure you use one of the chart subtypes that uses a 3-D effect.
Right-click one of the data series, and choose Format Data Series.

The Format Data Series dialog box launches.
Choose the Shape tab. Click one of the pictures to change your columns into cylinders, cones, pyramids, or something else. Then click OK.

Repeat steps 2 though 4 for any other data series that you want to change.

17.2.7. Changing the Way Excel Plots Blank Values

When Excel creates a chart, its standard operating procedure is to ignore all empty cells. The value of 0 doesn't count as an empty cell and neither does text (Excel plots any cells that contains text as a 0).

You can change Excel's behavior, butin a bizarre twist that confuses everyonethe setting isn't in the Chart Options dialog box where you'd expect it. Instead, it's in the general Excel settings.

To see the "Plot empty cells as" setting, select the chart you want to change, and then select Tools Options. Click the Chart tab, where you'll find the "Plot empty cells as" setting.

Note: Don't let Excel's quirky arrangement fool you. The "Plot empty cells as" setting is not a general Excel setting. Instead, it's stored on a chart-by-chart basis, like all other chart settings.

You have three choices for the cells:

Not plotted . Excel leaves a gap where the information should be. In a line chart, this breaks the line (making it segmented). This option is the standard choice.
Zero . Excel treats all blank cells as though they contain the number 0.
Interpolated . Excel treats all blank cells as missing information, and tries to guess what should go in between. For example, if a line chart goes from 10 to 20 with a blank cell in between, Excel interpolates the data point 15 and plots it.

Tip: You can also switch on or off the "Plot visible cells only" setting, to tell Excel how it should deal with cells that are hidden. This setting determines how Excel deals with data when you use filtering in a data list, or you explicitly hide rows or columns using the Format menu.