Format trendlines and Moving Average

Excel 365
It is simple as ABC to add one or multiple trendlines in Excel. A bewildering task is to choose the trendline type that best corresponds to the data series and, if needed, format data to create correct trends, forecasts, etc. Excel adds trendlines with the default formatting and values, such as the default value Period = 2 for the Moving Average.

Like Excel objects, you can fine-tune the trendline settings on the Format Trendline pane:

Format Trendline in Excel 365

To open the Format Trendline pane, right-click on the existing trend line and choose Format Trendline... in the popup menu (or double-click the trend line):

Format Trendline in popup menu Excel 365

Customize a trend line fill and effects

On the Format Trendline pane, on the Trendline Options tab:

  • In the Fill & Line group, specify the color, transparency, width, etc. for the trendline:
    Filling options for Trendline in Excel 365

    See more about filling options in Microsoft applications.

  • In the Effects group, specify some effects for the trend line such as Shadow, Glow, and Soft Edges in appropriate sections:
    Effects for Trendline in Excel 365

    Excel applies the chosen formatting to the trendline:

    Formatted Trendline in Excel 365

Change the trend line type

On the Format Trendline pane, on the Trendline Options tab, in the Trendline Options group, under Trendline Options, you can change the trendline type or trendline options, such as the Order for the Polynomial trend line or the Periods for the Moving Average trend:

Format Trendline in Excel 365

See more about trendline types.


To customize a trend line legend

On the Format Trendline pane, on the Trendline Options tab, under Trendline Name:

  • Select the Custom option,
  • Specify a Trendline name for the chart legend:
Specify Trendline name in Excel 365

This custom trendline name will be shown in the chart instead of the automatically generated one (see examples above).


Extend a trendline

On the Format Trendline pane, on the Trendline Options tab, under Forecast, change or specify the number of periods that you want to forecast:

Forecast Trendline in Excel 365
  • Type or select a value in the Forward field to project your data into the future (this option works for all trendline types):
    Forecast forward example in Excel 365
  • Type or select a value in the Backward field to project your data into the past (this option only works for XY scatter chart, Excel ignores the entered data in this field for any other chart types):
    Forecast backward example in Excel 365

Apply intercept

The Intercept is a value on the value axis where the trendline crosses the Y axis.

Note: Excel offers the INTERCEPT () function that, for given data, calculates a value on the Y axis - usually the vertical (Value) axis, where the trend line should pass. See more about the INTERCEPT () function:

Intercept example in Excel 365

To set the intercept, on the Format Trendline pane, on the Trendline Options tab, under Trendline Name, type or select the appropriate value in the Set Intercept field:

Intercept Trendline in Excel 365

Note: You can do this only when you use an exponential, linear, or polynomial trendline.

For example, the chart with two linear trendlines:

  • The first one with calculated intercept,
  • The second one with the intercept = 0:
Intercept example in Excel 365

Display the trendline equation on the chart

The trendline equation is a mathematical formula that describes a trendline. Excel uses the least squares method to find the best fit for a line through data points - see more about the calculation of trend values.

To add an equation to the chart, on the Format Trendline pane, on the Trendline Options tab, at the bottom of the Trendline Options group, select the Display Equation on chart checkbox:

Display Equation of Trendline in Excel 365

Note: You can display trendline equations for all trendline types except for the Moving Average:

Equation of Trendline example in Excel 365

See how to format the chart equation below.


Display the R-squared value on a chart

The R-squared value (in Excel, R2) is used to analyze how well the trendline corresponds to the data series. The R-squared value is a number from 0 to 1. The closer R2 is to 1, the closer the trendline is to the data points. An exactly matching model has R2 equal to 1.

To add the R-squared value to the chart, on the Format Trendline pane, on the Trendline Options tab, at the bottom of the Trendline Options group, select the Display R-squared value on chart checkbox:

Display R-squared value on chart in Excel 365

Note: You can display an R-squared value for all the trendline types except for the Moving Average:

R-squared value example in Excel 365

Format chart equation and R-squared value

Excel adds the equation and R-squared value with parameters by default. You can easily customize them:

  • To change the position of the equation and R-squared value on the chart, click on the corresponding text and drag the textbox using the mouse.
  • Format the textbox of the equation and R-squared value as a regular text box:
    • Change the text color, size, add effects, etc.
    • Change the filling options, add effects, border color, weight, type, etc.
  • To display more or less decimal places on the equation or R-squared value when displaying on a chart, do the following:
    • Double-click the text box to open the Format Trendline Label pane.
    • On the Format Trendline Label pane, on the Label Options tab, in the Label Options group, in the Number section:
      • From the Category list, select Number,
      • In the Decimal places field, type the number of decimal places you want to see on the coefficients in the equation and R-squared value:
        Format Trendline Label in Excel 365
      • Press Enter to update the text box.

    For example, with 8 decimal points:

    Format Trendline Label example in Excel 365

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more