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 .

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.

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

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