How to extend a trendline in Excel
Here we have a dataset that tells how the prices increase as the demand increases.
We want to see this data plotted as a trendline. To do that, let’s first make a scatter plot out of it 📈
- Select the dataset.
- Go to the Insert tab > Scatter Chart Icon > Simple Scatter chart.
And you’d have a scatter plot inserted in your sheet.
We see points plotted on the chart. Now we will make a trend line out of these dots to understand the trend (the relationship) between the demand and price of an item🚴♂️
To make a trendline on a scatter chart:
- Select the chart.
You’d see three icons to the right of the chart.
- Click on the Plus icon.
- From the drop-down menu, check the box for Trendline.
- This will add a trendline to your chart.
This trendline depicts the trend between the dataset provided to it. The trendline ends around 100 and 920 on the x-axis and y-axis, respectively.
A blue dotted trendline is the default trendline in Excel. You can always change it to your liking 🙈
For example, you can set it to a straight or curved line, a solid or dashed line, and in different colors. Sometimes. a scatter plot can also have multiple trendlines.
We want to extend this trendline to forecast future prices and demands. To do that:
- Double-click the trendline.
- This will launch the Format Pane to the right of your Excel sheet.
Look out for the Forecast Options in the Format pane under the Trendline Options.
The Forward period tells how much you want the trendline to be forecasted for the forward periods 🔮
Let’s set it to 10.
And here is how the trendline extends 10 periods beyond the last data point.
Note how the trendline is extended across 100 and 1000 now. This extension predicts how the trendline is expected to be in the future.
Similarly, the backward period extends the trendline towards the back. Let’s set it to 5 too 😎
The trendline is slightly extended backward from the first data point.
Extending the trendline forward and backward, you can forecast trends. Interesting and easy at the same time, isn’t it?