**What Is Extrapolation?**

Extrapolation is the prediction or estimation of unknown data based on known data assuming that the current trend continues. The mathematical equation for linear extrapolation is given below.

`y(x) = y1 + [(x - x1) / (x2 - x1)] * (y2 - y1)`

We have a 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 **and **February** and assume that we’ll have linear growth for the following months. We can predict the **Sales** for **March**** **using the extrapolation formula as shown below:

`=D5+(G5-C5)/(C6-C5)*(D6-D5)`

**D5** and** D6** cells refer to the **Sales** amount of **$1,246** and **$1,783**, respectively, whereas the **C5** and **C6** cells indicate the **Month** numbers **1** and **2**. **G5** contains the March month number, or the new x value where the formula is calculating the y.

Copying the formula to the next cell obtains the results for **April**.

**How to Extrapolate a Trendline in Excel: 4 Methods**

We’ll use the dataset in the **B4:C9 **cells, which contains the **U.S. Population** data for each decade starting from the **1950**.

### Method 1 – Extrapolating a Linear Trendline in Chart

__Step 1 – Insert a Line Chart__

- Insert 3 new rows in the
**Year**column. - Navigate to the
**Insert**tab and select the**Line Chart**drop-down.

- Choose the
**2D Line Chart**as shown in the image below.

__Step 2 – Enter Chart Data__

- Go to the chart and right-click on it.
- Choose the
**Select Data**option.

- A dialog box appears where we can change the
**Axis Labels**to show**Years**. Select the Edit button under Horizontal Axis Labels.

- Select the range of cells for the
**Years**and click**OK**.

- Rename the series by clicking
**Edit**on the Legend Entries section on the left.

- The
**Series**is renamed to**Actual Population**.

- Confirm your changes by clicking the
**OK**and closing the dialog box.

__Step 3 – Insert and Format the Trendline__

- Go to the
**Chart Elements**and expand**Trendline,**then select**Linear**.

- Under
**Trendline,**select**More Options**.

- A
**Format Trendline**panel appears on the right. Enter a name and assign**Forecast**periods on our chart (Forward: 3).

- Choose a
**Color**and the**Dash type**for the**trendline**as shown in the picture below.

- The trendline for the next three decades
**Axis Labels**for them. - Go to the
**Data Source**and select**Edit**for Horizonal Axis Labels.

- Hold the
**Ctrl**key and add the remaining**Years**to the previous selection.

- You can edit the
**Axis Titles**,**Chart Title**, and the**Legend**from the**Chart Element**option.

- The resulting chart should look like the picture shown below.

### Method 2 – Extrapolate Non-Linear Data with a Trendline

We have the following dataset shown in the **B4:D12 **cells below. The dataset shows the **Month Name**, **Month** numbers, and **Credit Card Balance** in USD. The balance doesn’t grow linearly.

__Steps:__

- Select the
**D5:D12**cells. - Go to
**Insert**and select**Line Chart**.

- Choose the
**2D Line Chart**option to represent the dataset as a line chart.

- Navigate to
**Chart Elements,**expand**Trendline,**and select**Exponential**.

- Format the chart like in the previous method to expand the horizontal axis and add labels.

Here are the various trendline types you can find in Excel and when they’re most useful:

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

**Method 3 – Extrapolate a Trendline with the FORECAST Function**

__Steps:__

- Make a new table for calculating the extrapolated values. Our existing data is in the C5:D12 range.
- Insert the following expression given below in H5 for the first result.

`=FORECAST(G5,D5:D12,C5:C12)`

The **G5** cell refers to the ** x** argument while the

**D5:D12**and

**C5:C12**cells represent the

**and**

*known_y***arguments, respectively.**

*known_x*- Use the
**Fill Handle**tool to copy the formula into the cells below.

**Method 4 – Extrapolate a ****Trendline ****with the TREND Function**

__Steps:__

- We’ll use the U.S. population dataset in B5:C9.
- Create a new table in E5:F7 to store new results.
- Fill the E5:E7 range with new x-values (years you want to calculate for).
- Insert the following formula in F5.

`=TREND($C$5:$C$9,$B$5:$B$9,E5)`

- Complete the table by copying the formula to the cells below.

**Things to Remember**

- The
**#REF! error**occurs if theand the*known_xs*arrays have different sizes.*known_ys* - The
**#VALUE! error**is shown if you enter a non-numeric value. - Linear extrapolation only works if the function is indeed linear. If the values don’t follow a linear progression, you can get wildly varying results.

**Download the Practice Workbook**

## Related Articles

**<< Go Back to Excel Extrapolation | Excel for Statistics | Learn Excel**