Often you may have one or more missing values in a series in Google Sheets that you’d like to fill in.

The following step-by-step example shows how to interpolate missing values in practice.

Step 1: Create the Data

First, let’s create a dataset in Google Sheets that contains some missing values:

Step 2: Calculate the Step Value 

Next, we will use the following formula to determine what step value to use to fill in the missing data:

Step = (End – Start) / (#Missing observations + 1)

For this example, we would calculate the step value as:

Step = (35 – 20) / (4 – 1) = 3

We can type the following formula into cell D1 to automatically calculate this value:

=(A13-A8)/(COUNTBLANK(A9:A12)+1)

The following screenshot shows how to use this formula in practice:

Step 3: Interpolate the Missing Values

Next, we need to add the step value to the missing values, starting with the first missing value:

Step 4: Visualize the Interpolated Values

Lastly, we can create a line chart to see if the interpolated values seem to fit the dataset well.

To do so, highlight cells A2:A21, then click the Insert tab, then click Chart.

In the Chart editor panel that appears on the right side of the screen, choose Line chart as the Chart type.

The following line chart will appear:

The interpolated values seem to fit the trend of the dataset well.

Additional Resource

The following tutorials explain how to perform other common tasks in Google Sheets: