EXCELAdding TrendlinesIf you have plotted a simple two-dimensional line, bar,
column or xy-scatter chart you can add a trendline to show a smoothed line
representing the pattern the results are following and to predict the values for
the future. The trendline can be
chosen to produce a linear, exponential, logarithmic, polynomial or moving
average trend, these trendlines use different types of mathematical formula to
produce a line that fits well.
The graph
above shows the average house price since 1991.
The data seems to be moving in an uphill curve rather than along a
straight line so a linear trendline will probably not give a very good fit.
As this is a curve we could try a polynomial trendline to see if that
fits better. To add a trendline, click on the chart and then click on the Chart drop-down menu and select Add Trendline. A window appears as below where you can choose the type of line to fit to your data. If you have more than one series on your chart make sure you choose the series you are interested in from the Based on series box at the bottom of the window.
We will choose the Polynomial line as it is likely to give a smooth increasing curve. When you click OK the trendline is added to your chart. As you can see from the example below, this polynomial trendline fits our data very well.
If you want
to know the equation
for the trendline you can add it to your chart, you can also
get Excel to predict
future values. To
do this, click on the trendline or use the Chart Object Selector on the
Chart
Toolbar to select the trendline and press CTRL +
1 to bring up
the Format Trendline window. Click
on the Options section of the window and you will see the options shown
below.
It is a good idea to add a name for your trendline to
make the legend more meaningful. You
can also use the Forecast boxes to extend the trendline forwards and/or
backwards a certain number of periods (categories) to predict values.
By ticking the box at the bottom you can show the equation of the
trendline on the chart. The diagram below shows our finished chart with the trendline extrapolated to 2009 and its equation. You can drag the equation box to move it outside of the plot area if you wish.
To remove a trendline, click your right mouse button over the trendline and choose Clear from the menu that appears. Click here to return to the Excel index. |
