Localized Trendlines Excel 2007
To create a localized trendline
1. Create an x-y scatter graph of all the data. If you have determined that you need a localized trendline - make sure to delete original trendline before moving on!
2. Highlight the desired years and the corresponding data and choose "Copy."
Click on the graph, go to the top of the dashboard and choose the Paste drop-down menu. Choose Paste Special:
Choose the following:
There will be a new series of data overlaid on the end of the existing data series.
Now add a trendline to your new data by right clicking on one of the new data points. Include the R-squared value and equation.
You must choose "Display Equation on Chart" and "Display R-squared value on chart" from the trendline options.
To add a prediction point to your graph. Below the last entry of data in your Excel file, type the prediction year. In the cell next to that year, type in your prediction for that year. Now, using Copy and Paste Special as above (step 4), place your prediction on the graph. Remember to click on the graph between copying and using paste special, or you will not get the correct dialog box! Click to select "New Series" and "Categories (X values) in First Column."