Trendlines

<< Click to Display Table of Contents >>

Trendlines

Note: Trendlines are available only for certain chart types (including two-dimensional column, bar, and line charts). Furthermore, they should not be stacked.

If desired, you can have a trendline shown for each data series of the chart. For more information, see Showing/hiding the trendline.

Trendlines can be used to graphically display the trend of a data series (i.e., to what extent the data series rises/falls on average). Trendlines can also be extended beyond the given data points, so that they display a forecast for future values.

A statistical technique called regression analysis is used to calculate trendlines.

Changing properties: To change the properties of a trendline, select it, right-click it and choose the command Trendline: Properties from the context menu.

Tip: Alternatively, you can choose this command via the Edit properties icon chart_edit_element_icon on the ribbon tab Chart | group Chart elements. The other alternative is to simply double-click on the trendline.

You can make the following settings:

Format tab

Line section

Here, you can change the appearance of the trendline. Auto uses a thick black standard line. None removes the line. The option Custom lets you customize the line style.

Trendline tab

Type section

Here, you can select the type of trendline to be displayed. In addition to linear trendlines (i.e. simple best-fit lines), trendlines such as exponential or polynomial trendlines can also be displayed.
The available types of trendlines are based on the following formulas:

Linear:

y = ax + b

Polynomial:

y = b + c1x + c2x2 + c3x3 + ... + c6x6

Exponential:

y = cebx   (e = Euler's number)

Logarithmic:

y = c ln(x) + b

Potential:

y = cxb

Moving average:

f(xi) = (xi + xi-1 + xi-2 + ... + xi-n+1) / n

Based on series

Here, you can specify the data series for which the trendline is to be displayed.

Forecast section

Here, you can extend the trendline beyond the existing data points.
If, for example, there are 3 data points (for example, the sales results for the 1st, 2nd and 3rd year), you can enter a 1 for Forward. The line is then extended by an additional period (1 data point), thus displaying a forecast for sales in the 4th year.

Crossing point

Here, you can force the trendline to intersect the Y axis at a specific Y coordinate. This option is available only for certain types of trendlines.

Show formula in chart

If this option is enabled, the formula used to calculate the trendline will be displayed in the chart.

Show correlation in chart

If this option is enabled, the correlation coefficient R2 of the data series will be displayed in the chart.

Name section

Here, you can change the name of the trendline if desired. This name is displayed in the legend. To do so, select the option Custom and enter the desired name.