While working with Microsoft Excel, you may need to extrapolate trendlines to predict the future value outside of your existing data points. With this in mind, this article demonstrates how to extrapolate a trendline in Excel.
What Is Extrapolation?
Extrapolation is the prediction or estimation of unknown data based on known data. Simply put, it tries to predict future data based on past data, assuming the current trend continues. The mathematical equation for linear extrapolation is given below.
y(x) = y1 + [(x - x1) / (x2 - x1)] x (y2 - y1)
Assuming the dataset given below in the B4:D6 cells where the Month’s Name and the Month number are shown along with the Sales amount.
Let’s say we have the data for January (x1, y1) and February (x2, y2). We can predict the Sales amount for March and April using the mathematical formula for extrapolation as shown below.
=D5+(G5-C5)/(C6-C5)*(D6-D5)
Here, D5 and D6 cells refer to the Sales amount of $1246 and $1783, respectively, whereas the C5 and C6 cells indicate the Month numbers 1 and 2.
Finally, use copying the formula to the next cell to obtain the results for April.
How to Extrapolate Trendline in Excel: 4 Methods
Considering the dataset in the B4:C9 cells, which contains the US Population data for each decade starting from the Year 1950.
1. Extrapolating Linear Trendline in Chart
Luckily, Excel allows us to extrapolate trendlines using charts. So, without further delay, let’s see the process bit-by-bit.
Step 01: Insert a Line Chart
- Firstly, insert 3 new rows in the Year column.
- Next, navigate to the Insert > Line Chart drop-down.
- Now, choose the 2D Line Chart as shown in the image below.
Step 02: Enter Chart Data
- Secondly, select the chart and right-click on the mouse.
- Then, choose the Select Data option.
- Next, a dialog box appears where we can change the Axis Labels to show Years.
- Following, select the range of cells for the Years and click OK.
- Moreover, rename the series by clicking Edit the button.
- In the example below, the Series is renamed to Actual Population.
- Additionally, confirm your changes by clicking the OK and closing the dialog box.
Step 03: Insert and Format Trendline
- Thirdly, go to the Chart Elements > Trendline > Linear.
- Next, choose the Chart Elements > Trendline > More Options.
- As a result, a Format Trendline Panel appears on the right where we enter a name and assign Forecast periods on our chart.
- Then, we can choose a Color and the Dash type for the trendline as shown in the picture below.
- Eventually, the trendline for the 3 Years appears but we have to add the Axis Labels for these years.
- In a similar fashion, go to the Data Source > Edit.
- This time, hold the CTRL key and add the remaining Years to the previous selection.
- Additionally, you can edit the Axis Titles, Chart Title, and the Legend from the Chart Element option.
Finally, the resulting chart should look like the picture shown below.
2. Extrapolate Non-Linear Data with Trendline
In case you’re wondering, how to extrapolate non-linear data with trendlines? Then you’ve come to the right place. Allow us to demonstrate.
Let’s say we have the following dataset shown in the B4:D12 cells below. The dataset shows the Month’s Name, Month numbers, and Credit Card Balance in USD.
Steps:
- Firstly, select the D5:D12 cells and go to Insert > Line Chart.
- Next, choose the 2D Line Chart option to represent the dataset as a line chart.
- Then navigate to Chart Elements > Trendline > Exponential to insert a suitable trendline.
In the same fashion as the previous method, you can format the chart to get the results as shown below.
In this section, we will discuss how to choose a suitable trendline that fits your dataset.
| Types of Trendline | Uses |
|---|---|
| Linear | A linear trendline suits a dataset that is similar to a line and where the data points change at a steady rate. |
| Exponential | An exponential trendline is useful for non-linear data where the values rise or fall at increasingly higher rates. |
| Logarithmic | A logarithmic trendline is suitable when the rate of change of values increases or decreases rapidly and then flattens out. |
| Polynomial | A polynomial trendline is handy when examining gains and losses in a large dataset. |
| Power | A power trendline matches datasets that increase at a specific rate. |
| Moving Average | A moving average trendline smoothens fluctuations by averaging a certain number of data points and using it in the trendline. |
Read More: How to Extrapolate a Graph in Excel
3. Extrapolate Trendline with FORECAST Function
If you’re one of those people who enjoy using Excel functions, then our next 2 methods have you covered. In fact, for this method, we’ll use the FORECAST function. So, just follow along.
Steps:
- Firstly, make a new table for calculating the extrapolated values.
- Then, insert the following expression given below.
=FORECAST(G5,D5:D12,C5:C12)
Here, the G5 cell refers to the x argument while the D5:D12 and C5:C12 cells represent the known_ys and known_xs arguments, respectively.
- Finally, use the Fill Handle tool to copy the formula into the cells below.
Here, I have skipped some of the uses of the FORECAST function, which you may explore if you want.
4. Extrapolate Trendline with TREND Function
Our last method employs the TREND function to extrapolate data. So, let’s see it in action.
Steps:
- Firstly, copy and paste the table for computing the extrapolated values.
- Then, insert the expression given below.
=TREND($C$5:$C$9,$B$5:$B$9,E5)
In this expression, the $C$5:$C$9 and the $B$5:$B$9 cells point to the known_ys and known_xs arguments, respectively. Moreover, the E5 cell refers to the new_xs argument.
Lastly, complete the table by copying the formula to the cells below.
Things to Remember
- Firstly, the #REF! error occurs if the known_xs and the known_ys arguments have different sizes.
- Secondly, the #VALUE! error is shown if you enter a non-numeric value.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
I hope this article helped you understand how to extrapolate trendline in Excel. If you have any queries, please leave a comment below.
