How to Extrapolate a Graph in Excel (2 Easy Methods)

Extrapolation is a way to use math to predict data that goes beyond what is already known. This is done through programming. So it’s a way to evaluate and visualize data in Excel. To extrapolate a graph, we use the data we already have to make a graph and then follow the line to guess what kind of results we will get outside of the data range we already have. Here are step-by-step instructions with explanations on how to extrapolate a graph in Excel.

How to Extrapolate a Graph in Excel 2 Easy Methods

1. Use Trendline Feature to Extrapolate a Graph in Excel

A line of best fit also called a trendline, is a straight or curved line on a chart that shows the overall pattern or direction of the data. Using a trendline to extrapolate from a graph in Excel helps you show how data changes over time. It’s a basic feature of Excel that lets us predict data within a reasonable range. We’ll learn how to add a trendline to a chart here. For an example of the Trendline feature in Excel, let us consider these two tables.

Linear Data shows how much oil is needed to fry potatoes in a restaurant, while Non-Linear Data shows how much a store sells over the course of a few months.
We will be extrapolating both these Linear and Non-Linear graphs with Trendline Feature.

1.1 Extrapolate Linear Graph by Trendline Feature

To extrapolate a Linear graph in Excel, let us assume we want to find out how much oil is needed for 100 kg of potatoes.

To find that out, we should follow the following steps.
Steps:

At first, choose the range of data (B4:C12).
Secondly, go to the ribbon and click on the Insert tab.
Thirdly, click on the Scatter chart in the chart area (you can pick up the Line chart too).

Fourthly, click on the (+) sign beside the chart and open Chart Elements.
Finally, enable the Trendline feature to predict the expected data from the graph. If you double-click on the graph’s trend line, you can open the Format Trendline panel and make your own changes.

Here, we can see that 100 kg of potatoes will need almost 20 liters of oil. We can make this prediction even more accurate by adding more ranges.

1.2 Extrapolate Non-Linear Graph by Trendline Feature

For extrapolating a graph of Non-Linear data in excel, let us assume we want to find out the sales for the 8th and 9th months from previous data.

Here we will be following those steps below.
Steps:

At first, use the given data to make a Scatter plot by following the steps above for Linear Data.
Then, press on the (+) sign beside the chart and open Chart Elements.
After that, by selecting Trendline, we can have a Linear Trendline. But by choosing the arrow beside, we can have multiple trendline options like Exponential, Moving Average, Logarithmic, and so on.

By clicking More Options, we can have more types of trendlines and options to edit the trendline.

Here is an example of an Exponential Trendline.

Also, we can see Moving Average Trendline in the below image. Here we can see Moving Average trendline is closer to our real graph.

2. Extrapolate Graph in Worksheets

Excel 2016 and later versions have a tool named Forecast Sheet that can be used to extrapolate the entire sheet both graphically and mathematically. This tool turns your data into a table that finds the Lower and Upper confidence bounds and a corresponding Linear Trendline graph. Let us consider the following data set. Here we want to find out how much oil is required to fry 50 kg of potatoes.

We will follow these steps to extrapolate a graph with this method.
Steps:

In the beginning, we need to select the entire data range (B4:C11).
Next, go to the Data tab in the ribbon and select the Forecast Sheet option.

Furthermore, a dialog box will appear. In the box, find the Forecast End option and set it to the expected value. For us, the expected value is 50.

Lastly, clicking the Create button will create a new sheet with a table that has all the data up to 50 kg, as well as the amount of oil that is forecasted, along with an Upper and Lower Confidence Bound.

It’ll also create a linear graph with a trendline in it.

Data Extrapolation with FORECAST Function

If you want to extrapolate your data without making charts and graphs, use the FORECAST function in Excel. You can extrapolate numbers from a linear trend with the help of the Forecast function. You can also use a periodic template or a sheet to figure out what to do. Forecast functions come in different forms. Here are some forecast functions that are often used and the steps to do them:

1. FORECAST Function

Extrapolation says that the relationship between values that are already known will also hold for values that are not known. The FORECAST function helps you figure out how to extrapolate data that has two sets of numbers that match each other. Here are the steps to follow to use the FORECAST function:

Steps:

First, select the empty cell we want to forecast. Then click on the function button in the formula bar.

Then a dialog box will appear. Search for the FORECAST function and select FORECAST from the results and click OK.

Again, a dialog box will appear. In this box, for X, select the cell that corresponding cell’s value we need to find out. In our case, that cell carries 100.

For known_ys, select all the cells containing known oil amounts.

For known_xs, select all the cells containing known potato amounts. Then press OK.

Finally, we will have the forecasted value in the empty cell.

2. Use FORECAST.LINEAR Function

The FORECAST.LINEAR function is the same as the FORECAST function. Each and every step is similar as well. Here is an example of this method.

3. Apply FORECAST.EST Function

In some cases, there is a seasonal pattern that needs a certain function to be able to predict the future. Then we have to use the FORECAST.EST function. Here is the previous example with FORECAST.EST function:

Extrapolate Data with Excel TREND Function

Excel also has a function called the TREND function that can be used to extrapolate data without making graphs. Using linear regression, this statistical function will figure out what the next trend will be based on what we already know. Here is the previous example of the FORECAST function with the TREND function.

Here is the output of using the TREND function.

Use Extrapolation Formula to Extrapolate Data

We will put the Extrapolation Formula in the formula bar after selecting the desired cell. The extrapolation formula is:
Y(x) = b+ (x-a)*(d-b)/(c-a)
Here is an example of this method:

After we apply this equation to the empty cell, we will get the extrapolated value like the picture below.

Things to Remember

TREND and FORECAST functions might look like the same thing, but the difference is that the FORECAST function only works as a regular formula that returns one value. On the other hand, the TREND function is an array formula to figure out how many y values go with how many x values.
Forecast Sheet only works when you have a constant difference between known values.
Extrapolation isn’t very reliable because we can’t be sure that the trend of the data will continue beyond the range of our data. Also, there is no way to see if our prediction is right or not. But if our original data is consistent, we can use extrapolation to get a better idea.

Download Practice Workbook

You can download the practice workbook here.

Extrapolate Graph.xlsx

Conclusion

This article shows how to use Excel to produce charts and graphs, predict data, and extrapolate graphs. These technologies let us anticipate data and track its changes using graphs and charts. If you’re still having trouble with any of these steps, let us know in the comments. Our team is ready to answer all of your questions.

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

Get FREE Advanced Excel Exercises with Solutions!