In this tutorial, you will learn how to interpolate missing values in Excel.
Let’s start by defining interpolation. In other words, interpolation is the process of figuring out or locating unknown values that reside between known or existing data points. In essence, we will use the known data points to complete the values of the missing data in our data collection.
In addition, we’ll go through three quick and easy ways to interpolate missing numbers in Excel.
We can use the linear trend technique first. Second, there is the growth trend approach. The weighted moving average formula can then be used. And all kinds of data sets are subject to these methods’ application.
We can select the best way to interpolate missing values in Excel after we know the type of data collection we have and how the values relate to one another.
Once ready, we’ll get started by utilizing real-world examples to show you how to interpolate missing values in Excel.
Table of Contents
Interpolate Missing Values in Excel
Before we begin we will need a group of data to interpolate missing values in Excel.
Step 1
First, you need to have a clean and tidy group of data to work with.
Step 2
In this example, we want to find out the missing data. We can first create a line chart.
Step 3
We can select the range beginning before and after the missing numbers, then choose ‘Home’, then select ‘Editing’, then select ‘Fill’ and select ‘Series’ to add the missing data.
Step 4
Excel will use the following formula to decide what step value to use to fill in the missing data if we leave the Type set to Linear.
Step = (End – Start) / (#Missing obs + 1)
This example’s step value is calculated as follows: (29-8) / (1+1) = 10.5.
Step 5
Excel will automatically fill in the missing values once we click ‘OK’.
