Table of Contents
Interpolation is a statistical technique used to estimate unknown values in a data set based on the known values. In Excel, missing values can be interpolated by using the “Interpolate” function. This function allows users to fill in the missing values by using linear or polynomial equations to estimate the values based on the surrounding data points. Additionally, Excel also has built-in tools such as trendlines and regression analysis that can help in interpolating missing values. These tools use mathematical algorithms to analyze the existing data and generate a trendline or regression line, which can then be used to estimate the missing values. By using these methods, missing values can be filled in Excel, allowing for a more complete and accurate data analysis.
Interpolate Missing Values in Excel
Often you may have one or more missing values in a series in Excel that you’d like to fill in.
The simplest way to fill in missing values is to use the Fill Series function within the Editing section on the Home tab.
This tutorial provides two examples of how to use this function in practice.
Example 1: Fill in Missing Values for a Linear Trend
Suppose we have the following dataset with a few missing values in Excel:
If we create a quick line chart of this data, we’ll see that the data appears to follow a linear trend:
To fill in the missing values, we can highlight the range starting before and after the missing values, then click Home > Editing > Fill > Series.
If we leave the Type as Linear, Excel will use the following formula to determine what step value to use to fill in the missing data:
Step = (End – Start) / (#Missing obs + 1)
For this example, it determines the step value to be: (35-20) / (4+1) = 3.
Once we click OK, Excel automatically fills in the missing values by adding 3 to the each subsequent value:
Example 2: Fill in Missing Values for a Growth Trend
If we create a quick line chart of this data, we’ll see that the data appears to follow an exponential (or “growth”) trend:
To fill in the missing values, we can highlight the range starting before and after the missing values, then click Home > Editing > Fill > Series.
If we select the Type as Growth and click the box next to Trend, Excel automatically identifies the growth trend in the data and fills in the missing values.
Once we click OK, Excel fills in the missing values:
From the plot we can see that the filled-in values match the general trend of the data quite well.
You can find more Excel tutorials .