Trendlines and Moving Average in Excel

Excel 2016
A trend line (or trendline, also known as a line of best fit) is a straight or curved line on a chart that shows a general pattern or the general direction of the data. The trend line displays the approximated values obtained using some mathematical function. The choice of function for constructing a trend line is usually determined by the nature of the data.

Trends can be described by various equations - linear, logarithmic, exponential, and so on. The more accurately the equation describes the data, the less distinct is the trend line. The trend line can also be used to forecast future trends and make forecasts:

Chart with a Trend Line in Excel 365

Excel makes adding a trend line to a chart quite simple.

Notes:

  1. In Excel, trendlines can be added for 2-dimensional charts only: a 2-D area, bar, line, column, stock, scatter, or bubble chart. You cannot add a trendline for 3-D or stacked charts, pie, radar, and similar.
  2. When creating a trend line, you should remember that Excel treats any data along the axes as numbers:
    • Excel generates a mathematically correct trend line using data values only for the XY scatter plot because the Y-axis and the X-axis are real numeric values for this type of chart. Excel treats the Category axis values for all other chart types as the list {1, 2, 3, ... n}, where n is the number of text elements on the axis.
    • If one of the chart's axes is based on text data, Excel uses the list {1, 2, 3, ... n} to calculate the trend and its formula, where n is the number of text elements on the axis.

      Attention! Excel takes the first value as x = 1, not x = 0 as usual. So, don't expect a proper intersection with Y-axis.

      For example, for a line chart, the vertical (Value) axis crosses the horizontal (Category) axis at a non-zero position - see the example above.

    • If one of the chart axes is a time scale with dates, Excel uses numbers that correspond to dates to calculate the trend and its equation (e.g., "January 1, 2020" is 43831, "December 1, 2020" is 44166). Thus, trends degenerate because the value differences of the secondary axis are extremely small compared to the magnitude of the primary time axis values:
      Wrong trendline equations in Excel 365

      To solve this issue, you can change the data values to text values (see how to change the axis labels format below).

  3. If you change the chart type to one that does not support trendlines, the trendline will not be displayed.

Add a trend line

To add a trendline, select the data series to which you want to add a trendline, and do one of the following:

  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element dropdown list:

    Add Chart Element button in Excel 365

    From the Add Chart Element dropdown list, choose the Trendline list, where select the desirable trend function if available (see more about different options below) or click More Trendline Options... to open the Format Trendline pane:

    Add Trendline in Excel 365
  • Right-click on the selected data series and select Add Trendline... in the popup menu:
    Add Trendline in the popup menu Excel 365
  • Click the Chart Elements button:
    Chart Elements button in Excel 365

    In the Chart elements list, choose the Trendline list, then select the desired trend function (see more about different trend functions below) or click More Options... to open the Format Trendline pane:

    Chart Elements - Trendline in Excel 365

Notes:

  1. If there are several data series on the chart, click the Chart Elements list without selecting any data series, choose the Trendline list, then select the option you prefer (anyone, including More Options...).

    Excel will show a list of the data series from the chart. Select the one you need and click OK:

    Several data series Trendline in Excel 365
  2. You can add several trend lines to the same data series. After adding the first trendline, just add another one. To select the needed trendline if you can't find them on the chart, see how to select an invisible element in the chart.

    Excel displays several trendlines for the same data series; see examples below.


Trendline types

The type of trend line that you choose depends on your data. You can see all possible trend lines that Excel can add to the chart on the Format Trendline pane, on the Trendline Options tab, in the Trendline Options group:

Format Trendline in Excel 365
  • Exponential trendlines can only be added for data with positive values. They are best suited for data that increases or decreases rapidly (the rate of data change is continuously increasing):
    Exponential Trendline in Excel 365

    The exponential trend lines are common if the vertical axis has a log scale.

    See more about the exponential trendline equation and formulas, calculating the trendline values, and creating a forecast.

  • Linear trends are most common and can be added for data with positive and negative values. They are used in the simplest cases when the data increases or decreases at a constant rate:
    Linear Trendline in Excel 365

    Linear trendlines are used to estimate a linear relationship in the data.

    See more about the linear trendline equation and formulas, how to calculate the trendline values, and build a forecast.

  • Logarithmic trendlines can be used for data with positive and negative values. They are best used for data increases or decreases very quickly at the beginning but then slow down and level off over time:
    Logarithmic Trendline in Excel 365

    The logarithmic trend lines are common if the horizontal axis has a log scale.

    Note: To create a logarithmic trendline for the displayed data, you should remember that Excel "understands" dates as numbers. If you use an axis as the timeline with dates, Excel treats them as numbers. So, you won't see an expected logarithmic trend because the logarithm function degenerates into a line parallel to the X-axis for large X-values (for example, "January 1, 2020" = 43831, "December 1, 2020" = 44166):

    Wrong Logarithmic Trendline in Excel 365

    See below for how to solve that issue.

    See more about the logarithmic trendline equation and formulas, calculating the trendline values, and creating a forecast.

  • Polynomial trend lines can be added for data with both positive and negative values. Use them to describe data that alternately increase and decrease, fluctuate - go up and down, and you need to evaluate the ups and downs of a large set of data.

    Additionally, for polynomial trendlines, you can specify the degree of the polynomial in the Order field. This value determines the maximum number of extrema (maxima and minima) of the curve:

    Polynomial 2 Trendline in Excel 365
    • A polynomial trendline of the second degree (Quadratic polynomial trend line with Order 2, used by default) can describe only one maximum or minimum (one hill or valley).
    • A polynomial trendline of the third degree (Cubic polynomial trend line with Order 3) has one or two extrema.
    • A polynomial trendline of the fourth degree (polynomial trend line with Order 4) can have no more than three extrema:
    Polynomial 4 Trendline in Excel 365

    Excel allows to choose from 2nd to 6th degree in the Order field:

    Polynomial 6 Trendline in Excel 365

    See more about the polynomial trendline equation and formulas, calculating the trendline values, and creating a forecast.

  • Power trendlines can only be added for data with positive values. They are best suited for data that shows a steady increase or decrease in the rate of growth or decline.

    In other words, you can try this type of trend line if you see that the trend is:

    • non-linear due to uneven data changes,
    • not polynomial due to the absence of fluctuations and extrema,
    • non-exponential, which implies extreme behavior at the end of a trend,
    • not logarithmic, which implies extreme behavior at the beginning of a trend:
    Power Trendline in Excel 365

    Note: The difference between a linear trend line and a power trend line is noticeable only when the first or first few values are close to zero or the difference between the values increases very significantly. If you analyze data far from zero or with a small growth rate, the difference between a linear and a power trend will not be significant.

    The power trend lines are common if both the vertical and horizontal axes are in log scales.

    See more about the power trendline equation and formulas, calculating the trendline values, and creating a forecast.

  • A Moving Average trendline (also known as rolling average, running average, or moving mean) can only be added for the data with positive values. It is suitable for data with large fluctuations - it smooths out data that has a lot of variation (that is, "noisy" data):
    Moving Average in Excel 365

    The Moving Average is a series of straight lines based on the average of the previous points. In the Period field, specify how many points are used for calculating the average:

    Moving Average 2 in Excel 365

    Notes:

    1. Excel offers to choose the Period value from two to the number of data points minus one. For this example, from 2 to 11.
    2. The number of points in a Moving Average trendline equals the total number of points in the data series plus one and minus the number specified in the Period field.

Delete a trendline

To delete a trendline, select it (see how to select invisible chart elements for more details), then do one of the following:

  • Right-click the selection and choose Delete in the popup menu:
    Remove Trendline in Excel 365
  • Press Delete.

Change the axis labels format

If Excel incorrectly treats dates on the axis as numbers, change the axis label format from date to text.

Excel treats text values as a numbered sequence of elements (1, 2, 3, etc.). To change the axis labels, do the following:

   1.   Right-click on the axis for which you need to change the format of the labels and choose Format Axis... in the popup menu:

Format Axis in popup menu Excel 365

   2.   On the Format Axis pane, on the Axis Options tab, in the Axis Options group, under Axis Type, select the Text axis option:

Format Axis as Text axis in Excel 365

See also this tip in French: Comment ajouter une courbe de tendance.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.