You can add a trendline to any of the following data series types:

You cannot add a trendline to any type of 3–D chart, stacked chart, radar chart, pie chart, or doughnut chart. If you add a trendline and then change the chart type or data series to an unsupported type, the trendline is deleted.

The type of data used in a chart determines whether the chart is appropriate for a trendline. Generally, charts that are suited for a trendline fall into two categories:

Trendlines assume that the category axis contains equal–interval values. This will always be the case with scatter charts, bubble charts, and other chart types that use a time–based category axis. For example, a line chart that displays months or weeks along its category axis is a candidate for a trendline — as long as no gaps exist in the data.

Charts that use an arbitrary category axis are not appropriate for a trendline, although Excel won't object if you add one. If the chart uses an arbitrary category axis, the trendline interprets the categories as values beginning with 1 and incrementing by 1.

Figure 5-2 shows a column chart with a trendline. Because the category axis contains nonnumeric text, Excel assigned the value of 1 to Adam, 2 to Ernie, 3 to Jenny, and so on. Consequently, the trendline is completely meaningless.

Figure 5-2. This chart is not appropriate for a trendline.

Figure 5-3 shows an example of a chart that is appropriate for a trendline. In this case, the chart compares individual performance on two tests using a scatter chart. Because both axes are numeric, the trendline is valid. It indicates a positive linear relationship: Students who did well on Test 1 also tended to do well on Test 2.

Figure 5-3. The trendline on this scatter chart depicts a positive linear relationship between two variables.

A trendline is always associated with a particular data series. To add a trendline to a chart series, select the series and then choose Chart Tools

These options represent those that Microsoft considers to be the most common types of trendlines. For additional trendline options, you need to use the Format Trendline dialog box.

Note

An apparent bug in the initial release of Excel 2007 causes the Chart Tools

Figure 5-4 shows the Trendline Options tab of the Format Trendline dialog box. Use this dialog box to change an existing trendline to a different type, specify options for a trendline, or apply formatting to the trendline. Not all the options are available for all trendline types.

Figure 5-4. The Format Trendline dialog box offers additional trendline options that aren't available by using the Ribbon.

The trendline options are briefly described in the list that follows and are discussed in more detail later in the chapter:

Figure 5-5 shows a chart with the monthly income data presented earlier in this chapter, along with an additional series for the corresponding monthly expenses (expressed as negative values). A trendline was added to each series. The trendlines indicate that income has been increasing. Expenses have also been increasing, but at a much slower pace than income.

Figure 5-5. The two series in this chart each have a linear trendline.

When Excel inserts a trendline, it may appear to be a new data series, but it's not. Rather, a trendline is a new chart element with a name, such as Series 1 Trendline 1. And, of course, a trendline does not have a corresponding SERIES formula.

Select a trendline and press Ctrl+1 to display the Format Trendline dialog box, which enables you to change its formatting or its options (discussed in the previous section).

If you choose either of the options that display a trendline equation or the R–squared value, the trendline will be accompanied by a text item that displays the requested information. Figure 5-6 shows a trendline label in the upper–left corner of a scatter chart.

Figure 5-6. When you add a trendline, you have an option to include the equation and R–squared value.

When the data changes, the trendline label is updated automatically to display the recalculated equation. To move the trendline label, just drag it. To change the formatting, select the label and press Ctrl+1 to display the Format Trendline Label dialog box.

A common type of formatting change is to increase the number of decimal places displayed in the equation. Use the Number tab of the Format Trendline Label dialog box to adjust the number formatting. For example, you can choose Number in the Category list and then specify five decimal places.

Note

In previous versions of Excel, you change the number of decimal places in a trendline equation by using the Increase Decimal or Decrease Decimal buttons. Those buttons (located in the Home

You can also use Ribbon commands and the Mini toolbar to change the text formatting of a trendline label.

Thus far, the discussion has focused on making predictions for data that falls within the existing numerical range (interpolation). In addition, you can make estimates for data that falls outside the existing range of data. This is known as forecasting or extrapolation.

Calculating the slope, y–intercept, and R–squared values

As described in this chapter, you can use the FORECAST function to calculate the points on a linear trendline and forecast other values. Alternatively, you can calculate the slope and y–intercept for the best–fit line, and then use these values to calculate the data points.

Assume that the x values are in A2:A11 and the y values are in B2:B11. To calculate the slope, you can use the SLOPE function, as follows:

=SLOPE(B2:B11, A2:A11)

Use the following formula to calculate the y–intercept:

=INTERCEPT(B2:B11, A2:A11)

When you know the values for the slope and the y–intercept, you can calculate the predicted y value for each x using a formula in this form:

y = mx + b

The accuracy of forecasted values depends on how well the linear trendline fits your actual data. The value of R–squared represents the degree of fit. R–squared values closer to 1 indicate a better fit and will yield more accurate predictions. Statistically speaking, you can interpret R–squared as the proportion of the variance in y that is attributable to the variance in x.

To calculate R–squared, you can use the RSQ function, as in this formula:

=RSQ(B2:B11, A2:A11)

Or, calculate the correlation coefficient and square it:

=CORREL(B2:B11, A2:A11)^2

Keep in mind that the value of R–squared calculated by the RSQ function or CORREL function is valid only for a linear trendline.

When your chart contains a trendline, you can instruct Excel to extend the trendline to forecast additional values of (x). You do this on the Trendline Options tab of the Format Trendline dialog box. Just specify the number of periods to forecast (either forward or backward in time).

This limitation on backward forecasting also applies to a date–scale axis, which is apparently a bug. In prior versions of Excel, the backward forecasting on a time scale axis was not limited in this way.

Excel does not accept a negative forecast value. To draw a trendline for a subset of the series data, you need to plot another series that omits the points you want to exclude.

Figure 5-8 shows a line chart with monthly sales data for 21 months, along with a trendline that forecasts results for 3 subsequent months. The forecasted data is derived by simply extending the linear trendline to cover three additional periods.

Figure 5-8. Using a trendline to forecast sales for three additional periods of time.

The preceding example leads, of course, to the question What are the actual forecasted sales values? As described previously, you can use the slope and y–intercept values to calculate the predicted y value for a given value of x. It's a fairly simple exercise to create formulas to perform these calculations.

One approach is to copy the slope and y–intercept values displayed in the trendline's equation, and use these values to calculate the predicted y values. (This is the method used earlier in this chapter to predict a person's weight, based on his or her height.) For increased accuracy, you can calculate the slope and y–intercept and use these values to calculate the predicted values. (See the nearby sidebar, "Calculating the slope, y–intercept, and Rsquared values.") The simplest approach (for linear trendlines only) is to use Excel's FORECAST function.

Figure 5-9 shows the data from the sales forecast chart. Column A contains the month names (for reference only), and column B contains consecutive month numbers. The actual sales figures are in column C. Column D contains formulas that return the predicted y values displayed in the trendline.

The formula in cell D2 is as follows:

=FORECAST(B2,$C$2:$C$25,$B$2:$B$25)

This formula was copied to the 24 cells below. As you can see, values for the final 3 months are forecast, based on the trend for the first 21 months.

Figure 5-9. Column D uses the FORECAST function to calculate points on a trendline.

A logarithmic trendline might be appropriate for data that follows a logarithmic curve: The values increase or decrease quickly and then level out. A logarithmic trendline appears as a straight line on a chart with a linear y–axis scale and a logarithmic x–axis scale. The equation for a logarithmic trendline is as follows

y = [c * LN(x)] – b

Figure 5-10 shows a chart with a logarithmic trendline added. The formula in cell E2, which follows, calculates c:

=INDEX(LINEST(B2:B11, LN(A2:A11)),1, 1)

The formula to calculate b, in cell F2, is as follows:

=INDEX(LINEST(B2:B11, LN(A2:A11)),1, 2)

Column C shows the predicted y values for each value of x, using the calculated values for b and c. For example, the formula in cell C2 is as follows:

=($E$2*LN(A2))+$F$2

Figure 5-10. A chart displaying a logarithmic trendline.

A power trendline describes data that increases (or accelerates) at a specific rate. A power trendline appears as a straight line on a chart with a logarithmic y–axis and a logarithmic x–axis scale. This trendline is limited to positive values. The equation for a power trendline looks like this:

y = c * x^b

Figure 5-11 shows a chart with a power trendline added. Cell E2 contains the following formula, which calculates b:

=INDEX(LINEST(LN(B2:B11),LN(A2:A11)),1, 1)

Figure 5-11. A chart displaying a power trendline.

The value for c is calculated in F2, using this formula:

=EXP(INDEX(LINEST(LN(B2:B11),LN(A2:A11)),1, 2))

Column C shows the predicted y values for each value of x, using the calculated values for b and c. For example, the formula in cell C2 is as follows:

=$F$2*(A2^$E$2)

An exponential trendline is used for data that rises or falls at an increasing rate. An exponential trendline appears as a straight line on a chart with a logarithmic y–axis scale and a linear x–axis scale. As with the power trendline, the exponential trendline does not work with data that contains zero or negative values. The equation for an exponential trendline looks like this:

y = c * EXP(b * x)

Figure 5-12 shows a chart with an exponential trendline added. The value for c is calculated in cell G2, which contains this formula:

=EXP(INDEX(LINEST(LN(B2:B11),A2:A11),1, 2))

Cell F2 contains this formula, which calculates the value for b:

=INDEX(LINEST(LN(B2:B11),A2:A11),1, 1)

Column C shows the predicted y values for each value of x, using the calculated values for b and c. For example, the formula in cell C2 is as follows:

=$G$2*EXP($F$2*A2)

Column D contains a multicell array formula that produces the same results. The array formula in range D2:D11 is:

=GROWTH(B2:B11, A2:A11)

Figure 5-12. A chart displaying an exponential trendline.

A polynomial trendline defines a curved line and describes data that fluctuates in an orderly pattern. When you request a polynomial trendline, you also need to specify the order of the polynomial (ranging from 2 through 6). The equation for a polynomial trendline depends on the order of the polynomial.

SECOND–ORDER TRENDLINE

A second–order trendline (also known as a quadratic trendline) describes data that resembles a U or an inverted U. Following is the equation for a second–order polynomial trendline:

y = (c2 * x^2) + (c1 * x^1) + b

Notice that two c coefficients appear (one for each order).

Figure 5-13 shows a chart with a second–order polynomial trendline added. Formulas entered in E2:G2 calculate the values for each of the c coefficients and the b constant. The formulas are as follows:

E2: =INDEX(LINEST(B2:B11, A2:A11^{1, 2}),1, 1)
F2: =INDEX(LINEST(B2:B11, A2:A11^{1, 2}),1, 2)
G2: =INDEX(LINEST(B2:B11, A2:A11^{1, 2}),1, 3)

Column C shows the predicted y values for each value of x, using the calculated values for b and the two c coefficients. For example, the formula in cell C2 is as follows:

=($E$2*A2^2)+($F$2*A2^1)+$G$2

HIGHER–ORDER POLYNOMIAL TRENDLINES

A polynomial trendline can use between two and six coefficients. Higher–order trendlines can often describe data sets that have complex or multiple curves. Figure 5-14 shows a chart with a third–order polynomial trendline. The equation for this trendline is similar to the second–order polynomial trendline equation, but with an additional coefficient:

y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b

Formulas in E2:H2 calculate the values for each of the c coefficients and the b constant. The formulas are as follows:

E2: =INDEX(LINEST(B2:B11, A2:A11^{1, 2,3}),1, 1)
F2: =INDEX(LINEST(B2:B11, A2:A11^{1, 2,3}),1, 2)
G2: =INDEX(LINEST(B2:B11, A2:A11^{1, 2,3}),1, 3)
H2: =INDEX(LINEST(B2:B11, A2:A11^{1, 2,3}),1, 4)

Figure 5-13. A chart displaying a second–order polynomial trendline.

=INDEX(LINEST(B2:B11, A2:A11^{1, 2,3, 4,5}),1, 1)

Figure 5-15 shows a sine curve (for x values of 1 through 8.25), along with a 6th–order polynomial trendline. The trendline, with an R–squared value of 1, is such a good fit that it corresponds exactly to the sine curve.

Figure 5-14. A chart displaying a third–order polynomial trendline.

Figure 5-15. A 6th–order polynomial trendline for a sine curve corresponds with the data so precisely that the trendline and the data overlap.

To add a moving average line, select the chart series and choose Chart Tools

If you like, choose the Custom option and enter a name for the moving average line. This is the text that will be displayed in the chart's legend.

A moving average is useful for smoothing out noisy data; it may also help to uncover trends that may otherwise be difficult to spot. Figure 5-16 shows a line chart with 50 data points, along with a moving average line with a period of 7. (It displays the average of every seven data points.) As you can see, the moving average line is much smoother and clearly depicts the general upward trend in the data.

Figure 5-16. This chart displays a moving average.

moving average.xlsx.

Notice that the moving average line does not begin with the first data point. In this case, the line begins at the seventh data point because the period is 7. The beginning of the line is the average of the first seven data points. The second point is the average of data points 2 through 8, the third point is the average of data points 3 through 9, and so on. Generally, using a larger period results in a smoother line — but the line gets shorter as the period increases.

You can, of course, create formulas to calculate a moving average for a data series, and then plot the moving average as a separate chart series. For example, assume that your data is in the range A1:A50. To create a moving average with a period of 7, enter this formula into cell B7:

=AVERAGE(A1:A7)

Then, copy the formula down the column, ending with cell B50. Add B1:B50 as a new data series, and the result will be identical to adding a moving average line via the Add Trendline dialog box.

This technique offers two advantages: You can add a moving average line to chart types that don't support trendlines, and you have more control over the appearance of the moving average line.

Figure 5-17, for example, shows a 3–D line chart. (This type of chart does not support trendlines.) The chart displays an additional series with a calculated moving average.

Figure 5-17. This 3–D line chart uses a data series in place of a moving average line.

Error bars are available for chart series of the following 2–D chart types:

Because scatter charts and bubble charts each have two value axes, you can display error bars for the x values, the y values, or both.

To add error bars, select the data series in the chart and choose Chart Tools

For maximum control, choose More Error Bar Options, which displays the Vertical Error Bars tab of the Format Error Bars dialog box. (See Figure 5-19.)

Figure 5-19. The Vertical Error Bars tab of the Format Error Bars dialog box.

The Format Error Bars dialog box lets you select the direction of your error bars. Vertical error bars display above each data point (Plus), below each data point (Minus), or both above and below each data point (Both). Horizontal error bars (available for scatter charts and bubble charts) offer the same options, but in the horizontal direction.

You can also choose the End Style: display the bars with a Cap or No Cap.

The Error Amount options determine the length of the error bars. These options are as follows:

Figure 5-20 shows a chart that uses error bars to indicate sampling error in a poll. Note that, in this example, the error bars use the Fixed Value option, with a value of .035 to represent a sampling error of 3.5 %. In other words, the percentage of those in favor of the bond issue in October is 40%, plus or minus 3.5% (a range of 36.5% to 43.5%). Notice that the error bars do not use the Percentage option, which displays as a percentage of each data point.

Figure 5-21 shows a scatter chart that displays vertical error bars using the Standard Deviation option. Unlike other error bar options, error bars that use the Standard Deviation option are not displayed relative to each data value. Rather, these error bars use the entire data set. In this example, the 100 data points have a mean of 31.64 and a standard deviation of 3.08. Therefore, the error bars display the mean, plus or minus 3.08. Using error bars in this chart makes it clear that the majority of the data points fall within one standard deviation of the mean.

Figure 5-20. This chart uses error bars to indicate sampling error in a poll.

Figure 5-22 shows a scatter chart that uses both vertical and horizontal error bars. Both sets of error bars display the corresponding value plus or minus 12.5 %. The two sets of error bars are independent of each other and can use different options.

Figure 5-21. This chart uses error bars that indicate the standard deviation.

Figure 5-22. This scatter chart uses both vertical and horizontal error bars.

The Custom option for error bars enables you to create error bars that aren't otherwise available. In most cases, you'll need first to create formulas that calculate the error bar values and then to specify those formula cells as the range(s) for the error bars.

The chart in Figure 5-23 shows a line chart that plots monthly sales. It uses error bars to depict the relative sales volume for the previous year. In this case, an error bar that appears above a data point indicates that the previous year's sales were higher in that month. When it appears below the data point, the prior year's sales were lower for that month. This chart represents an alternative to displaying an additional data series. In this case, adding another series would make the chart cluttered and perhaps less legible.

Figure 5-23. This chart uses error bars to depict corresponding monthly sales from the previous year.

Column D contains a simple formula that calculates the difference between the data in columns B and C. Following are the steps I used to create these error bars:

Because using error bars in this manner is not a standard technique, I added a text box to the chart to explain how to interpret the error bars.

Figure 5-24 shows another example of a chart that uses custom error bars. This column chart plots the average daily call volume for each of six weeks. The error bars depict the daily minimum and maximum for each week. For example, in Week–1, the average call volume was 103.71 calls per day. The maximum for the week was 173 and the minimum was 32.

The daily data appears in range B3:G9. Additional calculations were made for the data that appears in the chart. Row 13 contains the calculated average, which is the data used for the columns in the chart. Formulas in rows 14 and 15 calculate the maximums and minimums. Formulas in rows 16 and 17 calculate the data that's used for the error bars. The formulas in row 16 subtract the average from the maximum to yield the values used in the Positive Error Value range for the error bars. The formulas in row 17 subtract the minimum from the average to get the values used in the Negative Error Value range for the error bars.

Figure 5-24. The columns depict the weekly average; the custom error bars show the minimum and maximum for the week.

If you add a trendline to a chart, you can use error bars to indicate the deviations between the actual and the predicted values. Figure 5-25 shows an example.

First, create the scatter chart and add a linear trendline. (Choose Chart Tools

=TREND(B2:B11, A2:A11)

Figure 5-25. This scatter chart uses vertical error bars to indicate the deviation between the actual y values and the predicted y values on the trendline.

Next, create formulas to calculate the difference between each predicted y value and each actual y value. In this example, cell D2 contains the following formula, which was copied down to cell D11:

=C2–B2

The final step is to add vertical error bars to the chart. Follow these steps:

Because it's a scatter chart, Excel also adds horizontal error bars. Just select the horizontal error bars and press Delete. You might need to use the Chart Elements control to select these error bars.

For data points in which the predicted y value is greater than the actual y value, the error bar extends upward from the data point. For data points in which the predicted y value is less than the actual y value, the error bar extends downward from the data point. The sum of these deviations will always be 0.

In some cases you may prefer to "roll your own" error bars by adding one or two additional series to your chart. Figure 5-26 shows two charts. The chart on the top uses standard error bars to display one standard deviation. The chart on the bottom uses two additional series to plot lines that represent plus and minus one standard deviation.

Figure 5-26. Using two additional series as a substitute for error bars.

The data for the additional ranges used in the second chart are in columns C and D. The formula in C2, which was copied to the cells below, is as follows:

=AVERAGE($B$2:$B$13)+STDEV($B$2:$B$13)

The formula in D2, also copied to the cells below, is as follows:

=AVERAGE($B$2:$B$13)–STDEV($B$2:$B$13)

The easiest way to add these two data series to the chart is to activate the chart and drag the range outline to include the new data in C1:D13. This action adds two new line series to the chart (with series names), and then you can format them as you like.

One advantage of these additional series is that you have more control over the formatting. Vertical error bars, for example, always appear as vertical lines, and the lines cannot be connected horizontally. In addition, using a series for error bars enables you to display a description in the legend. Most would agree that the bottom chart is less cluttered and more legible.

A series line is applicable for the 2–D variants of stacked bar charts and stacked column charts, as well as pie of pie charts and bar of pie charts. Figure 5-27 shows an example of a stacked column chart with the series line option enabled. As you can see, the series lines simply connect the top of each data point with the next data point in the series.

To turn series lines on or off, select the chart and choose Chart Tools

Tip

For a single–series bar chart or column chart, the Chart Tools

To change the appearance of a series line, select a series line and press Ctrl+1 to display the Format Series Lines dialog box. Formatting changes apply to all the series lines on the chart.

Figure 5-27. This stacked column chart uses series lines.

Line charts and area charts can display drop lines, as shown in Figure 5-28. When this option is in effect, a line drops from each data point to the category axis.

Figure 5-28. This line chart displays drop lines.

To turn drop lines on, select the chart and choose Chart Tools

Normally, drop lines apply to all series in the chart. However, if you have a combination chart that consists of a line and an area series, you can select one of the series before you choose the Drop Lines command, and only that series will display drop lines.

To apply drop lines to only one series in a multiseries chart, specify a secondary value axis for the series before you apply the drop lines. Figure 5-29 shows a line chart with two series. One of the series uses a secondary value axis, and only that series displays drop lines. For details regarding secondary axes, refer to Chapter 4.

To change the appearance of drop lines, select a drop line and press Ctrl+1 to display the Format Drop Lines dialog box.

Figure 5-29. One series uses a secondary value axis and displays drop lines.

High–low lines are frequently used in stock market charts. In fact, when you create a stock market chart, high–low lines are added automatically. However, this feature can be used in any line chart that has at least two series.

The high–low lines connect the maximum data point in the category with the minimum data point in the category. Figure 5-30 shows an example that uses two data series. The highlow lines depict the difference between the sales goal and the actual sales made.

To add high–low lines to a line chart, select the chart and choose Chart Tools

To change the appearance of high–low lines, select a high–low line and press Ctrl+1 to display the Format High–Low Lines dialog box.

Figure 5-30. High–low lines connect the highest and lowest points within a category.

As with high–low lines, up/down bars are commonly used in stock market charts. Up/down bars are available only with 2–D line charts that have at least two series.

In a stock market chart, up/down bars (sometimes referred to as candlesticks) connect the day's opening price with the closing price. If the closing price is higher than the opening price, the bar is a lighter color. Otherwise, the bar is a darker color. You can format up/down bars any way you like.

Figure 5-31 shows a line chart with up/down bars. The first series plots income and the second plots expenses. The up/down bars connect each corresponding data point and represent the net profit for the month. Note that in January, February, and April, expenses exceeded income, so the bars for those months are down bars and they display in a lighter different color.

HOW UP/DOWN BARS WORK

Up/down bars rely on the plot order of the series. The up/down bars always connect the first series with the last series. If a line chart has six data series, the up/down bars will connect the first and the sixth. The only way to control which series get connected is to change the series plot order.

Figure 5-31. Tc bars to depict net profit by month.

To add up/down bars to a line chart, select the chart and choose Chart Tools

To remove up/down bars, choose Chart Tools

To change the appearance of up/down bars, select an up/down bar and press Ctrl+1 to display either the Format Up Bars or Format Down Bars dialog box. Note that the dialog box varies, depending on which type of bar is selected. In other words, you can apply different formatting to each of the two types of bars.

Another example of up/down bars is shown in Figure 5-32. This is a line chart with two series that display the normal high and low temperatures, by month. The lines are connected by up/down bars.

Figure 5-32. Using up/down bars to create floating columns.

Notice that the two series lines are invisible. This effect was done using the Line Color tab of the Format Data Series dialog box. I set the line color to No Line for both series. The result is a "floating column" chart that is not obscured by lines.