How to Interpolate Missing Data in Excel (4 Ways)

Because of various reasons, we can have datasets with missing data points. It is possible to estimate an approximate value of that data point using many useful interpolation techniques.  If you are curious to know how you can interpolate missing data in Excel, then this article may come in handy for you. In this article, we discuss how you can interpolate missing data in Excel with elaborate explanations.

Interpolate Missing Data in Excel: 4 Easy Ways

We are going to use the dataset below, where we have the product id and those products’ quantities. But at the same time, we also have Missing data points here. By applying various kinds of interpolation methods, we can estimate the approximate value of that Missing cell.

1. Use of Linear Trend Method

The linear trend means whether a dataset has variables that follow constant relationship relationships with each other. And at the rate at which the variables change their values almost follow a linear pattern.

Formula:

Here,

(x1, y1) = The First coordinate of the interpolation process.

(x2,y2) = Second point of the interpolation process.

x = Known value.

y =Unknown value.

The dataset that we are going to use has this linear pattern, although it has some of its data missing, as seen in the image. The missing data point can be demonstrated by the discontinuities in the line.

If your dataset follows this pattern, then you can use the below method to extract the missing value.

Steps

• We have the below dataset where we have the cell data C7 and C9 missing.
• Using the surrounding values, we need to estimate the quantity values in those cells.

• Next, select the range of cells D6:D8, and then go to the Home tab > Editing group.
• Right after that, go to Fill > Series.

• In the Series window, click on the Columns in the Series in option and select Linear in the Type option.
• You can see that the Step values are now set to 2.5 by default. You can edit these values for your needs.

• You can also calculate the step value using the following formula in the cell C18:

• Repeat the same process for the D9 cell.
• In order to get the missing value of the D9 cell, select the range of cells D8:D10, and then go to the Home tab > Editing group.
• Right after that, go to Fill > Series.
• In the Series window, click on the Columns in the Series in option and select Linear in the Type option.
• You can see that the Step values are now set to 2.5 by default. You can edit these values for your need.
• Click OK after this.

• After clicking OK, we can see that the cell with the missing values now has the values retrieved by using the Linear Trend.

2. Utilizing Growth Trend Method

When predicting future growth based on past data, growth trend interpolation in Excel can be useful. Linear growth suggests a constant extra increase throughout time, resulting in a straight path on a graph. This means that the annual percentage increase is decreasing slightly with each passing year.

Steps

• We have the below dataset where we have the cell data C7 and C9 missing.
• Using the surrounding values, we need to estimate the quantity values in those cells.

• Next, select the range of cells D6:D8, and then go to the Home tab > Editing group.
• Right after then, go to Fill > Series.

• In the Series window, click on the Columns in the Series in option and select Growth in the Type option.
• Tick the Trend check box and click OK.

• Repeat the same process for the second missing value in cell D9.
• Next, select the range of cells D8:D10, and then go to the Home tab > Editing group.
• Right after then, go to Fill > Series.

• In the Series window, click on the Columns in the Series in option and select Growth in the Type option.
• Tick the Trend check box and click OK.

• After clicking OK, you will see that both cells D9 and D7 now have the missing value.

3. Implementing Weighted Moving Average Formula

Here, we will interpolate missing values by implementing the Weighted Moving Average formula.

I am using another dataset with two columns, Named Month and Sales Quantity, with range B4:C13 like the following image. I am considering 10 as the total weight (Wt) and distributing the Wt among the previous month in such a way that recent data gets more weight. As you can see, I have data only for January, February, and March. We will interpolate the Sales Quantity for the rest of the months.

Definition:

Formula:

Here,

A1, A2, A3, A4 ….., is the data point of the datasets.

W1, W2, W3, W4…… are the weight assigned to each data point.

Wt is the summation of all weights.

Let’s apply the above theory to our dataset.

Follow these steps:

Step 1: Select cell C8 => Insert the given formula.

Step 2: Hit Enter to see the result.

Step 3: Hover over the cursor to the bottom right corner of cell C8 to see the Fill Handle icon.

Step 4: Drag the Fill Handle icon to cell C13 to copy the formula.

4. Using Simple Moving Average Formula

Definition:

Formula:

Here,

A1, A2, A3, A4……An is the data point of the datasets.

N = Denotes the number of data points.

A simple moving average is a measurement tool that can help predict if an asset price will maintain or if it falls into reverse bull even in a bear trend. Here, in this method, we will consider the dataset of the first two methods and use the AVERAGE and IF function alongside the weighted moving average to estimate the missing value.

Steps

• In the beginning, we need to choose the span range. here we choose span 3.
• In this method, we have to average the value, which will be done in a spanwise row.
• For example, we have a missing value in cell C7. And the formula span of this cell would be E6:E8.

• For example, we have a missing value in cell C9. And the formula span of this cell would be E8:E10.

• We first select cell E5 and enter the following formula:

• Then repeat the same formula in the cell E6 and enter the following formula:

• Now, select cell E11 and enter the following formula:

• Drag the Fill Handle to cell E13.

• After that, select cell E7 and enter the following formula:

• Drag the Fill Handle to cell E10.
• Doing this will fill the range of cell E8:E10 with a Simple Moving Average Value
• At the same time, our cell E9 now also has the missing value presented of cell C9.

Download Practice Workbook

Download this practice workbook below.

Conclusion

To sum it up, the issue of how we can interpolate missing data in Excel is answered here by 4 different examples. For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

Related Articles

• How to Do Linear Interpolation in Excel
• How to Do Interpolation with GROWTH & TREND Functions in Excel
• How to Do VLOOKUP and Interpolate in Excel
• How to Interpolate Between Two Values in Excel
• How to Perform Bilinear Interpolation in Excel
• How to Use Non Linear Interpolation in Excel
• How to Interpolate in Excel Graph
• How to Do Linear Interpolation Excel VBA

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