**Extrapolation** is the process of using historical data values to predict future values.

The easiest way to extrapolate data in Excel is by using the **TREND** function, which uses the following syntax:

**TREND(known_y’s, [known_x’s], [new_x’s], [const])**

where:

**known_y’s**: The range of known y-values**known_x’s**: The range of known x-values**new_x’s**: The range of new x-values**const**: Whether to force the constant in the trendline equation to be zero.

**Note**: The **TREND** function is designed to be used with data that follows a linear trend. For datasets that exhibit a nonlinear trend, you should use more advanced techniques.

The following step-by-step example shows how to use the **TREND **function to extrapolate data in practice in Excel.

**Step 1: Create the Dataset**

First, let’s enter the values for the following dataset into Excel:

**Step 2: Check if the Data Follows a Linear Trend**

Next, let’s create a scatter plot to check if the data follows a linear trend.

To do so, highlight the range **A2:B14**, then click the **Insert** tab along the top ribbon, then click the **Scatter** icon in the **Charts** group:

The following scatter plot will appear:

From the plot we can see that the points roughly follow a linear trend.

In other words, the points fall roughly along a straight diagonal line.

Thus, we can proceed to use the **TREND** function to extrapolate future data values.

**Note**: If the data exhibits a nonlinear pattern, you may try polynomial curve fitting instead.

**Step 3: Use TREND Function to Extrapolate New Values**

Suppose we would like to use our existing data to extrapolate the y-values for the following new x-values:

**x = 25****x = 30****x = 35**

To do so, we can define these new values in our worksheet and then type the following formula into cell **B17**:

=TREND($B$2:$B$14, $A$2:$A$14, A17)

We can then click and drag this formula down to each of the new values:

Here is how to interpret the output:

- For a value of x = 25, the forecasted value of y is
**26.75229**. - For a value of x = 30, the forecasted value of y is
**31.596**. - For a value of x = 35, the forecasted value of y is
**36.43971**.

If you would like to know the exact formula used to extrapolate these values, you can type the following formula into cell **D2**:

=LINEST(A2:A14, B2:B14)

The following screenshot shows how to use this formula in practice:

The **LINEST** function calculates the linear trendline for the existing data, which turns out to be:

**y = 0.968741x + 2.533759**

The **TREND** function uses this equation under the hood to extrapolate the y-values.

For example, for a value of x = 25, the equation calculates the following:

y = 0.968741*(25) + 2.533759 = **26.75229**

This matches the value calculated by the **TREND** function.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

How to Create a Sales Forecast in Excel

How to Create a Line of Best Fit in Excel

How to Add a Quadratic Trendline in Excel