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.

**Table of Contents**hide

**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

**and**

*known_ys***arguments, respectively.**

*known_xs*- 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

**arguments, respectively. Moreover, the**

*known_xs***E5**cell refers to the

**argument.**

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

**Things to Remember**

- Firstly, the
**#REF! error**occurs if theand the*known_xs*arguments have different sizes.*known_ys* - 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.