To show data trends or moving averages in a chart you created, you can add a trendline. You can also extend a trendline beyond your actual data to help predict future values. For example, the following linear trendline forecasts two quarters ahead and clearly shows an upward trend that looks promising for future sales.
You can add a trendline to a 2D chart that isn’t stacked, including area, bar, column, line, stock, scatter, and bubble.
You cannot add a trendline to a stacked, 3D, radar, pie, surface, or doughnut chart.
Add a trendline

On your chart, click the data series to which you want to add a trendline or moving average.
The trendline will start on the first data point of the data series you choose.

Click the Chart Elements button next to the upperright corner of the chart.

Check the Trendline box.

To choose a different type of trendline, click the arrow next to Trendline, and then click Exponential, Linear Forecast, or Two Period Moving Average. For additional trendlines, click More Options.

If you choose More Options, click the option you want in the Format Trendline pane under Trendline Options.

If you select Polynomial, enter the highest power for the independent variable in the Order box.

If you select Moving Average, enter the number of periods to use to calculate the moving average in the Period box.

To specify the point where the trendline crosses the vertical (value) axis, select Set Intercept and enter the value of the point on the vertical axis.

Tip: A trendline is most accurate when its Rsquared value (a number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data) is at or near 1. When you add a trendline to your data, Excel automatically calculates its Rsquared value. You can display this value on your chart by checking the Display Rsquared value on chart box (Format Trendline pane, Trendline Options).
Tips

If you change a chart or data series so that it can no longer support the associated trendline — for example, by changing the chart type to a 3D chart or by changing the view of a PivotChart report or associated PivotTable report — the trendline no longer appears on the chart.

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 bestfit linear or exponential lines.
Add a trendline

On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, click the data series to which you want to add a trendline or moving average, or do the following to select the data series from a list of chart elements:

Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.


Note: If you select a chart that has more than one data series without selecting a data series, Excel displays the Add Trendline dialog box. In the list box, click the data series that you want, and then click OK.

On the Layout tab, in the Analysis group, click Trendline.

Do one of the following:

Click a predefined trendline option that you want to use.
Note: This applies a trendline without enabling you to select specific options.

Click More Trendline Options, and then in the Trendline Options category, under Trend/Regression Type, click the type of trendline that you want to use.

Use this type 
To create 
Linear 
A linear trendline by using the following equation to calculate the least squares fit for a line:
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:
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:
where b and are constants. 
Power 
A power trendline by using the following equation to calculate the least squares fit through points:
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 calculate the least squares fit through points:
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: Note: The number of points in a moving average trendline equals the total number of points in the series less the number that you specify for the period. 
Rsquared value 
A trendline that displays an Rsquared value on a chart by using the following equation:
This trendline option is available on the Options tab of the Add Trendline or Format Trendline dialog box. Note: The Rsquared value that you can display with a trendline is not an adjusted Rsquared value. For logarithmic, power, and exponential trendlines, Excel uses a transformed regression model. 

If you select Polynomial, type the highest power for the independent variable in the Order box.

If you select Moving Average, type the number of periods that you want to use to calculate the moving average in the Period box.

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 have to sort the x values before you add a moving average.

If you add a trendline to a line, column, area, or bar chart, the trendline is calculated based on the assumption that the x values are 1, 2, 3, 4, 5, 6, etc.. This assumption is made whether the xvalues are numeric or text. To base a trendline on numeric x values, you should use an xy (scatter) chart.

Excel automatically assigns a name to the trendline, but you can change it. In the Format Trendline dialog box, in the Trendline Options category, under Trendline Name, click Custom, and then type a name in the Custom box.
Tips

You can also create a moving average, which smoothes out fluctuations in data and shows the pattern or trend more clearly.

If you change a chart or data series so that it can no longer support the associated trendline — for example, by changing the chart type to a 3D chart or by changing the view of a PivotChart report or associated PivotTable report — the trendline no longer appears on the chart.

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 bestfit linear or exponential lines.
What else would you like to do?
Change the format of a trendline

On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, click the trendline that you want to change, or do the following to select it from a list of chart elements.

Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.


On the Layout tab, in the Analysis group, click Trendline, and then click More Trendline Options.

To change the color, style, or shadow options of the trendline, click the Line Color, Line Style, or Shadow category, and then select the options that you want.
Specify the number of periods to include in a forecast

On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, click the trendline that you want to change, or do the following to select it from a list of chart elements.

Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.


On the Layout tab, in the Analysis group, click Trendline, and then click More Trendline Options.

To specify the number of periods that you want to include in a forecast, under Forecast, click a number in the Forward periods or Backward periods box.
Specify the point where the trendline crosses the vertical (value) axis

On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, click the trendline that you want to change, or do the following to select it from a list of chart elements.

Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.


On the Layout tab, in the Analysis group, click Trendline, and then click More Trendline Options.

Select the Set Intercept = check box, and then in the Set Intercept = box, type the value to specify the point on the vertical (value) axis where the trendline crosses the axis.
Note: You can do this only when you use an exponential, linear, or polynomial trendline.
Display the trendline equation on the chart

On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, click the trendline that you want to change, or do the following to select it from a list of chart elements.

Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.


On the Layout tab, in the Analysis group, click Trendline, and then click More Trendline Options.

To display the trendline equation on the chart, select the Display Equation on chart check box.
Note: You cannot display trendline equations for a moving average.
Tip: The trendline equation is rounded to make it more readable. However, you can change the number of digits for a selected trendline label in the Decimal places box on the Number tab of the Format Trendline Label dialog box. (Format tab, Current Selection group, Format Selection button).
Display the Rsquared value for a trendline

On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, click the trendline for which you want to display the Rsquared value, or do the following to select the trendline from a list of chart elements:

Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.


On the Layout tab, in the Analysis group, click Trendline, and then click More Trendline Options.

On the Trendline Options tab, select Display Rsquared value on chart.
Note: You cannot display an Rsquared value for a moving average.
Remove a trendline

On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, click the trendline that you want to remove, or do the following to select the trendline from a list of chart elements:

Click anywhere in the chart.
This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want.


Do one of the following:

On the Layout tab, in the Analysis group, click Trendline, and then click None.

Press DELETE.

Tip: You can also remove a trendline immediately after you add it to the chart by clicking Undo on the Quick Access Toolbar, or by pressing CTRL+Z.
Learn more about each trendline option
Linear trend line
Use this type of trendline to create a bestfit straight line for simple linear data sets. Your data is linear if the pattern in its data points looks like a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.
A linear trendline uses this equation to calculate the least squares fit for a line:
where m is the slope and b is the intercept.
The following linear trendline shows that refrigerator sales have consistently increased over an 8year period. Notice that the Rsquared value (a number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data) is 0.9792, which is a good fit of the line to the data.
Logarithmic trendline
Showing a bestfit curved line, this trendline is useful when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use negative and positive values.
A logarithmic trendline uses this equation to calculate the least squares fit through points:
where c and b are constants and ln is the natural logarithm function.
The following logarithmic trendline shows predicted population growth of animals in a fixedspace area, where population leveled out as space for the animals decreased. Note that the Rsquared value is 0.933, which is a relatively good fit of the line to the data.
Polynomial trendline
This trendline is useful when your data fluctuates. For example, when you analyze 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. Typically, an Order 2 polynomial trendline has only one hill or valley, an Order 3 has one or two hills or valleys, and an Order 4 has up to three hills or valleys.
A polynomial or curvilinear trendline uses this equation to calculate the least squares fit through points:
where b and are constants.
The following Order 2 polynomial trendline (one hill) shows the relationship between driving speed and fuel consumption. Notice that the Rsquared value is 0.979, which is close to 1 so the line’s a good fit to the data.
Power trendline
Showing a curved line, this trendline is useful for data sets that compare measurements that increase at a specific rate. For example, the acceleration of a race car at 1second intervals. You cannot create a power trendline if your data contains zero or negative values.
A power trendline uses this equation to calculate the least squares fit through points:
where c and b are constants.
Note: This option is not available when your data includes negative or zero values.
The following distance measurement chart shows distance in meters by seconds. The power trendline clearly demonstrates the increasing acceleration. Note that the Rsquared value is 0.986, which is an almost perfect fit of the line to the data.
Exponential trendline
Showing a curved line, this trendline is useful when data values rise or fall at constantly increasing rates. You cannot create an exponential trendline if your data contains zero or negative values.
An exponential trendline uses this equation to calculate the least squares fit through points:
where c and b are constants and e is the base of the natural logarithm.
The following exponential trendline is shows the decreasing amount of carbon 14 in an object as it ages. Note that the Rsquared value is 0.990, which means the line fits the data almost perfectly.
Moving Average trendline
This trendline evens 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. For example, if Period is set to 2, 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, etc.
A moving average trendline uses this equation:
The number of points in a moving average trendline equals the total number of points in the series, minus the number you specify for the period.
In a scatter chart, the trendline is based on the order of the x values in the chart. For a better result, sort the x values before you add a moving average.
The following moving average trendline shows a pattern in the number of homes sold over a 26week period.
See Also
Create a chart from start to finish