Extending the Trendline

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:

Format Trendline Options

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

Chart showing predicted future values

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:

Reading values on the linear chart

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:

Selecting the chart data points

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

Cells B2 to C9 highlighted in the 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.

Dragging chart data to add more data points

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

Excel has added the data points to the chart

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.

<--Back to the Excel Contents Page