Extending the Trendline in Microsoft Excel

Extending the Trendline in Microsoft Excel

In the first part of this tutorial you saw how to add a Trendline Chart. The type of Trendline we used was an Linear Regression one.

You can get Excel to extend the linear regression line on your chart. Click on your chart to highlight it. Click on the plus symbol again (Or bring up the Format Trendline dialogue box again, if you're using Excel 2007 and 2010.). Expand the Trendline option and select More Options. From the Trendline Options, have a look at the Forecast boxes. Type a 6 into Forward:

Press the Enter key on your keyboard and you should see the line extend on your chart:

New values have been added to the X-Axis. It now goes from 0 to 14. Trace the vertical line up from 12 until you come to the sloping line. Now trace a straight horizontal line the left, all the way to the Y-Axis, and you can see it reads a value of just above 20000:

If you look in cell B14 of your spreadsheet, you can see that the value is a more precise 20382.56. So Excel is predicting we'll earn this much in 2018.

If you want to add the new values as dots to your chart. Click on one of the dots on your chart. This will highlight all the dots:

The cells B2 to C9 (the data points) will also be highlighted on your spreadsheet:

To add more data points, drag the blue square in the bottom right of cell C9. Drag it down to cell C15. Now drag the purple square in cell B9. Drag it down to cell B15.

Now have a look at your chart. Click outside of the dots to deselect them:

Excel has added the 6 new dots. It has added them all on the line. (They are all on the line because they are predictions, rather than real data.)

And that's it for X Y Scatter charts and linear regression. The above techniques come in quite handy for when you need to show future predictions: predicting revenue streams, predicting future crime statistics, share prices, and lots more besides. If you're looking for work, one thing that may impress an employer is to say that you can do linear progressions on an X Y Scatter chart!

In the next section, we'll have a look at something called Sparklines.

Posted in: Microsoft Excel,Microsoft Excel 3 (Excel 2007/2010 Charts),Tips,Trick,Tutorial,Tutorial Excel 3