You can add 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.) 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, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart. A trendline is always associated with a data series, but a trendline does not represent the data of that data series. Instead, a trendline is used to depict trends in your existing data or forecasts of future data.
Note You cannot add a trendline to data series in a stacked, 3D, radar, pie, surface, or doughnut chart.
What do you want to do?
Learn about forecasting and showing trends in charts
Trendlines are used to graphically display trends in data and to help analyze problems of prediction. Such analysis is also named 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 two quarters ahead to clearly show a trend toward increasing revenue.
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 bestfit linear or exponential lines.
Choosing the right trendline type for your data
When you want to add a trendline to a chart in Microsoft Office Excel, you can choose any one of these six different trend or regression types: linear trendlines, logarithmic trendlines, polynomial trendlines, power trendlines, exponential trendlines, or moving average trendlines. The type of data that you have determines the type of trendline that you should use.
A trendline is most accurate when its Rsquared value (Rsquared 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 Rsquared 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 Rsquared value. If you want to, you can display this value on your chart.
Linear trendlines
A linear trendline is a bestfit 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 13year period. Notice that the Rsquared value is 0.979, which is a good fit of the line to the data.
Logarithmic trendlines
A logarithmic trendline is a bestfit 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 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 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 hills or valleys.
The following example shows an Order 2 polynomial trendline (one hill) to illustrate the relationship between driving speed and fuel consumption. Notice that the Rsquared value is 0.979, which is a good fit of the line to the data.
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 1second 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 Rsquared value is 0.986, which is an almost perfect fit of the line to the data.
Exponential trendlines
An exponential trendline is a curved line that is used when data values rise or fall at constantly increasing 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 Rsquared value is 0.990, which means that the line fits the data almost perfectly.
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. 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..
In the following example, a moving average trendline shows a pattern in number of homes sold over a 26week period.
Top of Page
Add a trendline
 On an unstacked, 2D, area, bar, column, line, stock, xy (scatter), or bubble chart, 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, 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.
 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.
Notes
 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.
Top of Page
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.
Top of Page
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.
Top of Page
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.
Top of Page
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).
Top of Page
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 (Rsquared 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 Rsquared value is at or near 1. Also known as the coefficient of determination.), 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.
Top of Page
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.
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.
Top of Page