Excel > Excel 2003 Intermediate > Charts

Adding a Trendline to a Chart

A trendline shows the trend of values by averaging out high and low points. It can also be used to predict future values.

There are 6 different trendlines available in Excel, and it is important to choose the best type to show an accurate trend in your values.

Trendline

When Used

Linear

Used for values that increase or decrease at a steady rate

Logarithmic

Used for values that increase or decrease quickly before levelling out

Polynomial

Used for values that fluctuate

Power

Used for values that increase or decrease at a specific rate

Exponential

Used for values that increase or decrease at increasingly higher rates

Moving Average

Averages out the high and low points smoothing any fluctuations

The accuracy of a trendline is recognised by its R-Squared value. The R-Squared value is a number from 0 to 1. The closer to 1 the value is, the more accurate the trendline. The R-Squared value can be displayed on the chart.

To add a trendline:

  1. Select the chart
  2. Click Chart > Add Trendline
  3. Adding a trendline to a chart

  4. The Add Trendline dialogue box is displayed
  5. Add a trendline
  6. Select the trendline that accurately plots your data
  7. When using a Polynomial trendline you should set the Order, which is how many bends the trendline will have. Order 2 has 1 bend, order 3 has 2 and so on.

    For Moving average trendlines you should set the Period, which is the number of values to average into a point on the trendline.

  8. Select the series that the trendline is based on
  9. Click the Options tab
  10. Set trendline options

  11. Click the Display R-Squared value on chart checkbox
  12. It is also possible to predict future values by using the Forecast pane and to customise the legend entry using the Trendline name pane within the Options tab.

  13. Click Ok
  14. Chart trendline

You can add more than one trendline to a chart and format a trendline following the same procedure as formatting any other chart element.

To remove a trendline, select the trendline and press Delete.

Follow us on

Facebook  Twitter  You Tube