How to Extrapolate Trendline in Excel (4 Quick Methods) 

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.


Download Practice Workbook

You can download the practice workbook from the link below.


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.

Dataset 1

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.

Extrapolating With Mathematical Equation

Finally, use copying the formula to the next cell to obtain the results for April.

Extrapolating With Mathematical Equation


4 Methods to Extrapolate Trendline in Excel

Considering the dataset in the B4:C9 cells, which contains the US Population data for each decade starting from the Year 1950.

Dataset 2

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.

How to Extrapolate Trendline in Excel Using Chart

  • Now, choose the 2D Line Chart as shown in the image below.

Line Chart

Step 02: Enter Chart Data

  • Secondly, select the chart and right-click on the mouse.
  • Then, choose the Select Data option.

Select Data

  • Next, a dialog box appears where we can change the Axis Labels to show Years.

How to Extrapolate Trendline in Excel Using Chart

  • Following, select the range of cells for the Years and click OK.

How to Extrapolate Trendline in Excel Using Chart

  • Moreover, rename the series by clicking Edit the button.

How to Extrapolate Trendline in Excel Using Chart

  • In the example below, the Series is renamed to Actual Population.

How to Extrapolate Trendline in Excel Using Chart

  • Additionally, confirm your changes by clicking the OK and closing the dialog box.

How to Extrapolate Trendline in Excel Using Chart

Step 03: Insert and Format Trendline

  • Thirdly, go to the Chart Elements > Trendline > Linear.

How to Extrapolate Trendline in Excel Using Chart

  • Next, choose the Chart Elements > Trendline > More Options.

How to Extrapolate Trendline in Excel Using Chart

  • As a result, a Format Trendline Panel appears on the right where we enter a name and assign Forecast periods on our chart.

How to Extrapolate Trendline in Excel Using Chart

  • Then, we can choose a Color and the Dash type for the trendline as shown in the picture below.

How to Extrapolate Trendline in Excel Using Chart

  • 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.

How to Extrapolate Trendline in Excel Using Chart

  • This time, hold the CTRL key and add the remaining Years to the previous selection.

How to Extrapolate Trendline in Excel Using Chart

  • Additionally, you can edit the Axis Titles, Chart Title, and the Legend from the Chart Element option.

How to Extrapolate Trendline in Excel Using Chart

Finally, the resulting chart should look like the picture shown below.

How to Extrapolate Trendline in Excel Using Chart

Read More: How to Find the Equation of a Trendline in Excel (3 Suitable Ways)


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.

Dataset 3

Steps:

  • Firstly, select the D5:D12 cells and go to Insert > Line Chart.

How to Extrapolate Trendline in Excel Using Chart

  • Next, choose the 2D Line Chart option to represent the dataset as a line chart.

How to Extrapolate Trendline in Excel Using Chart

  • Then navigate to Chart Elements > Trendline > Exponential to insert a suitable trendline.

Non Linear Trendline

In the same fashion as the previous method, you can format the chart to get the results as shown below.

How to Extrapolate Trendline in Excel Using Chart

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 Calculate Trend Analysis in Excel (3 Easy Methods)


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.

How to Extrapolate Trendline in Excel Using FORECAST Function

  • Finally, use the Fill Handle tool to copy the formula into the cells below.

Using FORECAST Function

Here, I have skipped some of the uses of the FORECAST function, which you may explore if you want.

Read More: How to Create Monthly Trend Chart in Excel (4 Easy Ways)


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.

How to Extrapolate Trendline in Excel Using TREND Function

Lastly, complete the table by copying the formula to the cells below.

Using TREND Function

Read More: How to Calculate Trend Percentage in Excel (With Easy Steps)


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.

Conclusion

I hope this article helped you understand how to extrapolate trendline in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo