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.

## Download Practice Workbook

Download this practice workbook below.

## 4 Easy Ways to **Interpolate** **Missing** Data in Excel

We are going to use the below dataset, 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 follows 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 then, go to
**Fill**>**Series**.

- In the
**Series**window, click on the columns in the**Series**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.

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

`=(C16-C15)/(C17+1)`

- 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 then, go to
**Fill**>**Series**. - In the
**Series**window, click on the columns in the**Series**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**.

**Read More: ****How to Deal with Missing Data in Excel (6 Suitable Ways)**

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

**Read More: ****How to Find Missing Values in Excel (3 Easy Ways)**

**Similar Readings**

**How to Find Missing Rows in Excel (3 Easy Ways)****Find Missing Values in a List in Excel (3 Easy Methods)****How to Count Missing Values in Excel (2 Easy Ways)**

### 3. Implementing Weighted Moving Average Formula

**Definition:**

**The weighted moving average (WMA) is a useful metric that gives more weight to the latest data points and less weight to datasets from a long time ago.**

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

The WMA is calculated by multiplying each number in the data set by a fixed weight and adding the results. Recent data points are given more weight, while previous data points are given less weight. Here, in this method, we will use **the SUM function** alongside the weighted moving average to estimate the missing value.

**Steps**

- Just like before, we have
**Missing**cell values in cells**C7**and**C9**. - We will only estimate the
**Missing**value in cell**C9**. - For this method, we need to use the
**linear**values that we calculated before. - At the same time, we will put a factor or weight on the cell values. We are going to put 1 for the
**Missing**value for cell**C9**. - And for the immediate cells
**E8**and**E10,**we input 0.5. - Furthermore, enter
**33**in cells**D7**and**D11, and 0.25**in cells**D6**and**D12**. - And finally, enter 0.13 for cells
**D5**and**D13.**

- Select cell
**F5**and enter the following formula:

`=E5*D5`

- Drag the
**Fill Handle**to cell**F13**. - Now the full table is now filled with the multiplication of
**Weight**and**Linear**values.

- Select the cell
**F14**and enter the following formula:

`=SUM(F5:F13)`

- Then select cell
**E17**and enter the following formula:

`=SUM(D5:D13)`

This will calculate the summation of all of the **Weight.**

- Furthermore, select cell
**E18**and enter the following formula:

`=F14/E17`

This is the value of the **Missing** value in cell **C9.**

**Read More: ****How to Filter Missing Data in Excel (4 Easy Methods)**

### 4. Using Simple Moving Average Formula

**Definition:**

**Simple moving averages compute the average of a price range over a given number of periods.**

**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 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:

`=IF(C5="",D5,C5)`

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

`=IF(C6="",D6,C6)`

- Now, select cell
**E11**and enter the following formula:

`=IF(C11="",D11,C11)`

- Drag the
**Fill Handle**to cell**E13**.

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

`=IF(C7="",AVERAGE(D5:D9),C7)`

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

**Read More: ****How to Fill Missing Values in Excel (5 Useful Tricks)**

## 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. Any suggestion for the betterment of the **Exceldemy** community will be highly appreciable.