Display Trendlines in charts

You can display or hide trendlines in the bar, line, area, bubble, or scatter charts.

Trendlines, also known as lines of best fit or regression lines, graphically illustrate trends in data series and are commonly used when charting predictions. A trendline is typically a line or curve that connects or passes through two or more points in the series, showing a trend.

You can specify the following types of trendlines:

  • Linear

    Use a linear trendline when your data increases or decreases along a straight line at a constant rate. For example, if your chart displays a steady increase in revenue by product line over time, you could use a linear trendline.

  • Exponential

    Use an exponential trendline when your data values increase or decrease exponentially, or at an increasingly higher or lower rate. For example, if your chart displays an exponential increase in revenue by product line over time, you could use an exponential trendline.

  • Polynomial

    Use a polynomial trendline when your data values both increase and decrease. For example, if your chart displays both increases and decreases in revenue by product line over time, you could use a polynomial trendline.

  • Logarithm or Natural Logarithm

    Use a logarithmic trendline when your data values increase or decrease rapidly and then level out. For example, if your chart displays a rapid decrease in revenue by product line over time and then a plateau, you could use a logarithmic trendline.

  • Power

    Use a power trendline when your data values increase or decrease in a curve at a steady rate. For example, if your chart displays a steady increase in revenue by product line over time and your data points will fit a curved line, you could use a power trendline.

  • Moving Average

    Use a moving average trendline when your data values fluctuate and you want to smooth out the exceptions to see trends. For example, if your chart displays wild fluctuations in revenue by product line over time, but you know that some data points are exceptions, you could use a moving average trendline.

If you are not sure which trendline type to use, try each type to see which one best fits most of your data points. For example, a linear trendline will not fit most points on a scatter chart with widely spread data points.

Trendlines do not support color transparency.

Procedure

  1. Select the chart object.
  2. In the Properties pane, under Chart Annotations, double-click the Trendlines property.
  3. Click the New icon new icon and click a trendline type.
  4. Define the trendline by specifying the following options. The options that are available depend on the type of trendline you chose.
    • To set the order, or degree, of a polynomial trendline, in the Order box, type a value between 2 and 6.
    • To set the number of periods to go back in a moving average trendline, in the Periods box, type a value.
    • If you have more than one series on your chart, in the Based on list, click the data you want to use for the trendline.
    • Click either One trendline for all series items or A trendline for each series item.
    • To customize the style of the trendlines, click Line Styles and customize the line color, weight, and style.
    • To customize the trendline labels in the legend, click Label and choose None, Automatic, or Custom.
    • To display the trendline equation, click Show equation, and then click Show in legend or Show on chart, and then click Position to define the equation's position on the chart.
    • To display the R-squared value of the trendline, click Show R-squared value, and then click Show in legend or click Show on chart and then click Position to define the position of the value on the chart.