Add a trendline to a chart

A trendline (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.) or moving average (moving average: A sequence of averages computed from parts of a data series. In a chart, a moving average smooths the fluctuations in data, thus showing the pattern or trend more clearly.) can be added to any data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in an unstacked, 2-D, area, bar, column, line, stock, xy (scatter), or bubble chart.

 Note   A trendline cannot be added to data series in a stacked, 3-D, radar, pie, surface, or doughnut chart.

What do you want to do?

Learn about forecasting and showing trends in charts

Add a trendline

Change trendline settings

Display the R-squared value for a trendline

Remove a trendline

Learn about forecasting and showing trends in charts

Trendlines are used to graphically display trends in data and to analyze problems of prediction. Such analysis is also called regression analysis (regression analysis: A form of statistical analysis used for forecasting. Regression analysis estimates the relationship between variables so that a given variable can be predicted from one or more other variables.). By using regression analysis, you can extend a trendline in a chart beyond the actual data to predict future values. For example, the following chart uses a simple linear trendline that is forecasting four quarters ahead to clearly show a trend toward rising revenue.

Chart with linear trendline

Tips

  • You can also create a moving average, which smoothes out fluctuations in data and shows the pattern or trend more clearly.
  • For line data without a chart, you can use AutoFill or one of the statistical functions such as GROWTH() or TREND() to create data for best-fit linear or exponential lines.

Choosing the right trendline type for your data

When you want to add a trendline to a chart in Microsoft Excel, you can choose any of the six different trend/regression types. The type of data that you have determines the type of trendline that you should use.

A trendline is most reliable when its R-squared value (R-squared value: A number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data. A trendline is most reliable when its R-squared value is at or near 1. Also known as the coefficient of determination.) is at or near 1. When you fit a trendline to your data, Excel automatically calculates its R-squared value. If you want to, you can display this value on your chart.

Linear trendlines

A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.

In the following example, a linear trendline illustrates that refrigerator sales have consistently risen over a 13-year period. Notice that the R-squared value is 0.9036, which is a good fit of the line to the data.

Chart with linear trendline

Logarithmic trendlines

A logarithmic trendline is a best-fit curved line that is used when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use both negative and positive values.

The following example uses a logarithmic trendline to illustrate predicted population growth of animals in a fixed-space area, where population leveled out as space for the animals decreased. Note that the R-squared value is 0.9407, which is a relatively good fit of the line to the data.

Chart with logarithmic trendline

Polynomial trendlines

A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analyzing gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trendline generally has only one hill or valley. Order 3 generally has one or two hills or valleys. Order 4 generally has up to three.

The following example shows an Order 2 polynomial trendline (one hill) to illustrate the relationship between speed and gasoline consumption. Notice that the R-squared value is 0.9474, which is a good fit of the line to the data.

Chart with polynomial trendline

Power trendlines

A power trendline is a curved line that is used with data sets that compare measurements that increase at a specific rate — for example, the acceleration of a race car at 1-second intervals. You cannot create a power trendline if your data contains zero or negative values.

In the following example, acceleration data is shown by plotting distance in meters by seconds. The power trendline clearly demonstrates the increasing acceleration. Note that the R-squared value is 0.9923, which is a nearly perfect fit of the line to the data.

Chart with power trendline

Exponential trendlines

An exponential trendline is a curved line that is used when data values rise or fall at increasingly higher rates. You cannot create an exponential trendline if your data contains zero or negative values.

In the following example, an exponential trendline is used to illustrate the decreasing amount of carbon 14 in an object as it ages. Note that the R-squared value is 1, which means that the line fits the data perfectly.

Chart with exponential trendline

Moving average trendlines

A moving average trendline smoothes out fluctuations in data to show a pattern or trend more clearly. A moving average uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the line. If Period is set to 2, for example, then the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on.

In the following example, a moving average trendline shows a pattern in number of homes sold over a 26-week period.

Chart with moving average line

Top of Page Top of Page

Add a trendline

  1. On a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) or in an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) to which you want to add a trendline or moving average.
  2. On the Chart menu, click Add Trendline.
  3. On the Type tab, click the type of regression trendline or moving average that you want.

ShowLearn more about the available trendline types

Use this type To create
Linear

A trendline by using the following linear equation to calculate the least squares fit for a line:

Equation

where m is the slope and b is the intercept.

Logarithmic

A logarithmic trendline by using the following equation to calculate the least squares fit through points:

Equation

where c and b are constants, and ln is the natural logarithm function.

Polynomial

A polynomial or curvilinear trendline by using the following equation to calculate the least squares fit through points:

Equation

where b and Variable are constants.

Power

A power trendline by using the following equation to calculate the least squares fit through points:

Equation

where c and b are constants.

 Note   This option is not available when your data includes negative or zero values.

Exponential

An exponential trendline by using the following equation to calculates the least squares fit through points:

Equation

where c and b are constants, and e is the base of the natural logarithm.

 Note   This option is not available when your data includes negative or zero values.

Moving average

A moving average trendline by using the following equation:Equation

 Note   The number of points in a moving average (moving average: A sequence of averages computed from parts of a data series. In a chart, a moving average smooths the fluctuations in data, thus showing the pattern or trend more clearly.) trendline equals the total number of points in the series less the number that you specify for the period.

R-squared value

A trendline that displays an R-squared value (R-squared value: A number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data. A trendline is most reliable when its R-squared value is at or near 1. Also known as the coefficient of determination.) on a chart by using the following equation:

Equation

This trendline option is available on the Options tab of the Add Trendline or Format Trendline dialog box.

 Note   The R-squared value that you can display with a trendline is not an adjusted R-squared value. For logarithmic, power, and exponential trendlines, Excel uses a transformed regression model.

  1. If you select Polynomial, type the highest power for the independent variable in the Order box.
  2. If you select Moving Average, type the number of periods to be used to calculate the moving average in the Period box.

 Notes 

  • The Based on series box lists all of the data series in the chart that support trendlines. To add a trendline to another series, click the name in the box, and then select the options that you want.
  • If you add a moving average to an xy (scatter) chart, the moving average is based on the order of the x values plotted in the chart. To get the result that you want, you might need to sort the x values before adding a moving average.

Top of Page Top of Page

Change trendline settings

  1. On a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) or in an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), click the trendline that you want to change.
  2. On the Format menu, click Selected Trendline.
  3. On the Options tab, select the options that you want.
  4. To change the name of the trendline or moving average, type a name in the Custom box.

Top of Page Top of Page

Display the R-squared value for a trendline

  1. On a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) or in an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), click the trendline for which you want to display the R-squared value (R-squared value: A number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data. A trendline is most reliable when its R-squared value is at or near 1. Also known as the coefficient of determination.).
  2. On the Format menu, click Selected Trendline.
  3. On the Options tab, select Display R-squared value on chart.

 Note   You cannot display an R-squared value for a moving average.

Top of Page Top of Page

Remove a trendline

Or you can right-click the trendline, and then click Clear.

 Tip   You can also remove a trendline immediately after you add it to the chart by clicking Undo on the Edit menu, or by pressing CTRL+Z.

Top of Page Top of Page

 
 
Applies to:
Excel 2003