Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


EXCEL

Adding Trendlines

If 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.

webmaster@allaboutoffice.co.uk