How to Extrapolate Data in Excel (5 Handy Ways)

Microsoft Excel comes to the rescue in the method for calculating numbers outside of a known location of numerical data. It is sufficient to use the available data as a starting point and complete a few simple procedures in order to automate the calculating technique. In this article, we will show you 5 different ways to extrapolate data in Excel.

What Is Data Extrapolation?

A mathematical technique called extrapolation makes predictions beyond the marvelous variety by using programming and growing beyond existing data. Therefore, it’s a style of Excel data evaluation and visualization technique. The mathematical expression for Linear Extrapolation is given below.

How to Extrapolate Data in Excel: 5 Handy Ways

In this given data set, we have a list of 7 people with different heights and weights. Now, we will extrapolate the last 2 person’s unknown weights using 4 different handy methods.

1. Using Formula for Data Extrapolation in Excel

An extrapolated expression is an expression used to estimate the value of a dependent variable for an independent variable that is said to be certainly outside the scope of a particular known dataset and to compute a linear search. In this first method, we will extrapolate 2 people’s unknown weights using the very basic mathematical expression of data extrapolation. The following steps are given below.

Steps:

• First, select cell F10. Write down the following formula for data extrapolation.

• Secondly, press Enter and cell F10 will represent the first person’s weight.

• Thirdly, use the Fill Handle tool and drag it down from cell F10 to F11. Therefore, we will get the results of other cells.

2. Applying Trendline for Data Extrapolation in Excel

In this method, you will show two different ways for data extrapolation in Excel. You can express trends in visual data by extrapolating a graph by a trendline. In this article, we will learn how to add a trendline to our charts.

(a) Trendline for Data Extrapolation in a Graph

Steps:

• First, select the height and weight column from the given data set.
• Then, click on the Insert tab and go to the Recommended Charts command.

• Now, click on any chart from the Recommended Charts option.  Then, you will get a preview of the selected chart on the right sideshow.
• Finally, click on OK.

• Finally, click on the Chart Element option.
• Afterward, click on the Trendline option, then you will get the preview of the selected Trendline in the Graph, which is indicated by a blue arrow sign.

(b) Trendline for Extrapolation of Non-linear Data

If you have a non-linear dataset, you need to use Trendlines to identify trends in data changes and predict the desired values. In this method, we will extrapolate data in Excel using Trendline for Non-linear data.

Steps:

• Following the above Trendline method, we will create the following Graph.

• You can display other types of Trendlines such as exponential, logarithmic, and polynomial on the chart by double-clicking on the aforementioned Trendline and selecting the “Display R-squared value on the chart” and “Display Equation on Chart” boxes.
• Look at the R-squared value to choose the optimal Trendline. The trendline that best fits your data has the highest R-squared value.
• In the equation displayed in the graph above, enter x. View the outcomes of the final two individuals’ weights on the Graph and the data set above after that.

3. Utilizing FORECAST Function for Data Extrapolation in Excel

You will use the FORECAST function when you need a function to forecast your data without making charts and graphs. You can extrapolate numerical data over a linear trend with the help of the FORECAST function. Additionally, you can extrapolate a sheet or even a periodic template. Here, we’ll cover how to extrapolate a sheet as well as how to use the FORECAST.LINEAR and FORECAST.ETS functions.

(a) Utilizing FORECAST. LINEAR Function

Extrapolation states that the relationship between known values and unknown variables will hold true as well. This function enables you to extrapolate data made up of two sets of linked numerical values.

Steps:

• First, select cell F10. Write down the following formula with the FORECAST.LINEAR function for data extrapolation.

• Secondly, press Enter and cell F10 will represent the first person’s weight.

• Thirdly, use the Fill Handle tool and drag it down from cell F10 to F11. Therefore, we will get the results of other cells.

(b) Using FORECAST. ETS Function

If you have a seasonal pattern, for example, this periodic template may require a specific function to project the future. This data set is organized by height and weight. The weights of the first two individuals will be extrapolated.

Steps:

• First, select cell F10. Write down the following formula with the FORECAST.ETS function for data extrapolation.

• Secondly, press Enter and cell F10 will represent the first person’s weight.

• Thirdly, use the Fill Handle tool and drag it down from cell F10 to F11. Therefore, we get the results of other cells.

4. Applying Forecast Sheet Command for Data Extrapolation in Excel

The Forecast Sheet command creates a table according to the data set and evaluates the confidence interval.

Steps:

• Select the Height and Weight columns.

• Click on the Data tab.
• Now, click on the Forecast Sheet command.

• Finally, you will get the following results.

5. Inserting TREND Function for Data Extrapolation

Another characteristic for extrapolating records without drawing a graph is the TREND function characteristic. This statistical characteristic makes use of recognized values primarily based totally on linear regression to expect destiny trends.

Steps:

• First, select cell F10. Write down the following formula with the TREND function for data extrapolation.

• Secondly, press Enter and cell F10 will represent the first person’s weight.

• Thirdly, use the Fill Handle tool and drag it down from cell F10 to F11 Therefore, we get the results of other cells.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

In this article, I’ve covered 4 handy methods to Extrapolate Data in Excel. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Excel Extrapolation: Knowledge Hub

• How to Perform Linear Extrapolation in Excel
• How to Fill a Series Based on Extrapolation in Excel
• How to Extrapolate a Graph in Excel
• How to Extrapolate Trendline in Excel

<< Go Back to Excel for Statistics | Learn Excel