Extrapolation is a mathematical method that predicts beyond the distinct range by programming and expanding past known data. So it’s a type of Excel data analysis and visualization. In this tutorial, we’re going to learn how to extrapolate data in Excel.

**Table of Content**

- Extrapolation Formula
- How to Extrapolate a Graph by Trendline
- How to Extrapolate Nonlinear Data by Trendline
- Extrapolation Data by the Forecast Function
- Excel Trend Function

**Extrapolation Formula**

To extrapolate data by formula, we need to use two points of the linear chart that we plotted before.

- A(a, b)
- B(c, d)

The linear extrapolation formula is:

**Y(x)=****b+(x-a)*(d-b)/(c-a)**

You can enter the formula according to two points of your data values and extrapolate the target value.

**How to Extrapolate a Graph by Trendline **

Extrapolating a graph by trendline helps you represent visual data trends. Here we’re going to learn how to add a trendline to our charts:

- Select the data range.
- Go to the
**Insert**tab from the ribbon. - From the chart section, click on the
**Line**chart (you can pick up the Scatter chart too.) - Click on the
**Chart Element**icon and check the Trendline checkbox. - Double-click on the trend line of the graph to open the
**Format Trendline**pane and apply your custom setting.

**How to Extrapolate Nonlinear Data by Trendline**

When you have a nonlinear dataset, you need to detect the data change trend using a trendline and then forecast the desired value. Here’s how you can do it:

- Draw a scatter plot.
- Click on the
**Chart Element**icon and check the Trendline checkbox. - Double-click on the trend line of the graph to open the
**Format Trendline**pane. - Add different types of trendline (exponential, logarithmic, and polynomial) to the chart and check “
**Display R-squared value on chart**” and “**Display Equation on Chart**” boxes.

To determine the best trendline look at the**R-squared**value. The highest R-squared value shows the best trendline for your data. - put x in the equation shown in the chart.

**Choose The Best Trendline**

When you have a data set, you need to detect the data change trend and forecast them in a graph.

Point: Do not use trendlines for **Radar****, ****Pie****, ****Doughnut****, ****Bubble****, and ****3D ****graphs****.**

In Excel, we have six types of trendlines.

- Exponential
- Linear
- Logarithmic
- Polynomial
- Power
- Moving Average

**Exponential**

When data values rise or fall at increasingly higher rates, and there are no zero or negative Y values, we use the Exponential trendline.

**Linear**

When your plotted data set is similar to a line, in other words, when the data is increasing or decreasing at a steady rate, use the Linear trendline.

**Logarithmic**

When you have a swift data decrease or increase, use the Logarithmic trendline. The data could be negative or positive. Invalid for zero or negative X values.

**Polynomial**

Assume you have a large data set that is analyzing gains and losses. Fluctuation is the main reason for using this trendline.

**Power**

This trendline is used when you compare measurements that increase at a specific rate. Invalid for zero or negative X values.

**Moving Average**

This trendline uses the average of the particular number of data points by the Period option.

**Data** **Extrapolation by the Forecast Function**

**Data**

If you need a function to predict your data without creating charts and graphs in Excel, use the Excel **Forecast** function. The Forecast function helps you extrapolate numerical data over a linear trend. Also, you can extrapolate a periodical template or even extrapolate a sheet.

Here we’re going to learn how to use the **Forecast.linear,** and the **Forecast.ETS **functions and how to extrapolate a sheet.

**Forecast.Linear**

Extrapolation adjudges that the relationship between known values will also apply to unknown values. This function helps you extrapolate data that contains two sets of numerical values which correspond to each other.

Below is the Syntax of the Forecast.Linear function:

=FORECAST.LINEAR(x؛ known Ys؛ known Xs)

Assume we have a set of data that shows the number of sales for nine-month. We need to predict sales for the next three months. To use this function, follow these steps:

- Select an empty cell.
- Enter the
**=forecast**or the**=forecast.linear**in the**Formula Bar**. - Click on the
**x**value you want to predict for itself, and enter a semicolon or comma(according to your Excel version.) - Select all
**known Ys**, enter a semicolon, and then select**all known Xs**. - Press
**Enter**.

#### Possible Error

**#N/A:** If the size of the known_Ys and known_Xs is not equal, or if one or both are empty, This error will occur.

**#DIV/0:** If the variance of the known_Xs is equal to zero, this error will occur

**#VALUE:** If the input x is non-numeric, This error will occur. Click on the link if you want to know more about Excel formula errors and why they happen.

**Forecast.ETS**

In some cases, you have a seasonal pattern, and this periodical template needs a particular function to forecast the future. Here we have a sales amount for a year, and we need to predict the first three months of the next year.

The syntax of the Forecast.ETS function is:

=FORECAST.ETS(target_date؛ values؛ timeline؛ [seasonality], [data_complation]; [aggregation])

**Target_date**: The point you need to forecast.

**Values**: Here are all known sales amounts.

**Timeline**: In this case, the number of months.

[**seasonality**]: The length of the seasonal pattern (optional argument.)

**[data_complation]**: Although the timeline requires a constant step between data points, FORECAST.ETS supports up to 30% of missing data and will automatically adjust for it (optional argument.)

[**aggregation**]: The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same timestamp (optional argument.)

Now follow these steps to forecast your target values:

- Select an empty cell where you want to represent the result.
- Enter the syntax of the function and enter the arguments, as we mentioned.
- Press Enter.

**Possible Errors**

**#N/A**: If the **values **and **timeline** arrays have different sizes, this error is returned

**#VALUE**: If any of the **seasonality**, **data completion** or **aggregation** arguments is non-numeric, this error will occur.

**#NUM**: This error will be returned if the **seasonality** exceeds 87600, the **data completion** value is anything other than 0 or 1, **aggregation** value is not valid (any non-integer number or out of 1-7 range), or the function cannot detect a consistent step size in the **timeline. **If you want to know more about Excel formula errors and why they happen, click on the link.

**Extrapolating Sheets**

Excel 2016 and later versions provide a tool to forecast the sheet. This tool creates a table according to your data and determines lower and upper confidence bound.

To use the Forecast Sheet, go to the **Data** tab from the **Forecast** group, click on the **Forecast Sheet** tool to open the **Create Forecast Worksheet** box. You can pick a line chart or column chart by their icons in the top right corner of the box.

If you need to customize the forecast chart, you can edit by clicking on **options**:

- Where the Forecast starts or ends
- Change the confidence interval
- Add the Forecast statistics
- Change the Timeline and Values range
- And aggregate duplicate using

Then press the **Create** button and see the result.

**Excel Trend Function**

Another function to extrapolate data without plotting graphs is the Trend function in Excel. This statistical function is going to predict future trends according to the known values based on linear regression.

The syntax of the Trend function:

=TREND(known_Ys; [known_Xs]; [new_Xs]; [const])

**Known Ys**: The Y values we already know.

**Known Xs**: The X values we already know (optional argument.)

**Const**: according to Y=mX+b, if const is false, b is zero, but if const is true or skipped, b is calculated normally.

**Possible Errors**

**#REF!**: If **known_Xs** and **known_Ys** arguments have different sizes, this error occurs

**#VALUE**: This error occurs if you enter a non-logical value as the **const** argument, or you enter a non-numeric value as the function’s other arguments. Click on the link if you want to know more about Excel formula errors and why they happen.

You can connect with us, ask our experts if you have any inquiries, and get more support via Excel Support Services**.**

Also, reduce costs, accelerate tasks, and improve quality with Excel Automation Services.

## FAQ

**1-**

**Is extrapolation reliable?**Generally, extrapolation is not so reliable because we cannot be sure that the data trend will continue out of our data range. In addition, there is nothing to check how accurate our prediction is. However, when our original data is very consistent, we can rely on extrapolation.

**2-**

**What is the difference between extrapolation and regression?**Regression is the mathematical process to find a line or curve that fits the data. Once you find the proper line or curve, you can predict the unknown value based on its equation.

**3-**

**What is the difference between the FORECAST and TREND functions?**When working with time series, both of them return the same result since they use the same mathematical method.

The difference between these two is that the FORECAST function works only as a regular formula returning one value as output. In contrast, the TREND function can be used as an array formula and calculate multiple y values corresponding to multiple x values.