How to Use Linear Trend Interpolation in Excel

Dealing with missing data points in your reports is frustrating, but you don't need a degree in statistics to fix it. If you have gaps in a time-series dataset, like a missing day of sales or website traffic, you can use linear trend interpolation to fill them in with a reasonable estimate. This article gives you three practical ways to calculate and visualize a linear trend to fill blank cells in Excel.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is Linear Trend Interpolation?

In simple terms, linear trend interpolation is a way to find missing values by drawing a straight line between the two closest known data points. The word interpolation means you're estimating a value that falls between two points you already have. The widespread use of data has highlighted the need for interpolation. Whether it be sales, digital marketing, or SEO, using historical interpolation helps fill gaps and gives marketers the edge. You’re simply assuming that the trend between a known dataset is a steady, straight line and using that line to figure out what the middle data point(s) would be.

For example, imagine you have this simple dataset for daily website visitors, but Wednesday's data is missing:

• Monday: 1,500 visitors
• Tuesday: 1,600 visitors
• Wednesday: ???
• Thursday: 1,800 visitors
• Friday: 1,900 visitors

Linear interpolation would look at the data from Tuesday (1,600) and Thursday (1,800) and estimate Wednesday's traffic to be 1,700 - right in the middle. It's best used for filling occasional gaps in data that generally moves in a consistent direction without extreme, unpredictable swings.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: Use Excel’s Fill Series with Trend

For quickly filling gaps in a single column, Excel’s built-in “Fill Series” feature is the fastest method and you might learn to do it in just 30 seconds. It requires no formulas and works like a wonder.

Let's use a sample dataset of monthly revenue with missing values for March and April.

Step 1: Select the Data Range

Click and drag to highlight the range of cells you want to fill. It's important to include the value before the first blank cell and the value after the last blank cell. In our example, that means selecting cell B3 (Feb's value) down to B6 (May's value).

Step 2: Open the Series Dialog Box

Navigate to the Home tab on the Excel ribbon. In the Editing group, click on Fill, then select Series... from the dropdown menu.

Step 3: Apply the Trend

In the Series pop-up box, configure the following settings:

• Make sure Columns is selected under 'Series in'.
• Leave 'Type' as Linear.
• Check the box next to Trend.

Then, click OK.

Excel will instantly fill the blank cells by calculating the linear trend based on your starting and ending values. It calculated that revenue should increase by $3,000 each month to create a straight line from Feb's $11,000 to May's $20,000.

Method 2: Use the TREND Function for More Control

If you prefer using formulas or need to perform more complex calculations, the TREND function is the way to go. It offers more flexibility and is essential when your independent variable (like dates) isn’t perfectly sequential.

The syntax for the function is:

=TREND(known_y's, known_x's, new_x's)

• known_y's: These are your existing dependent values (e.g., the known sales numbers).
• known_x's: These are your existing independent values (e.g., the dates corresponding to the known sales).
• new_x's: This is the independent value(s) for which you want to predict a new 'y' value (e.g., the date with the missing sales number).

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Example: Filling a Single Blank Cell

Using our previous example, let's say only March is missing. To use TREND, we need to point it at the known start and end points of our gap.

Here, our known points are February and April.

• known_y's are the revenue values: $11,000 (B3) and $17,000 (B5).
• known_x's are the corresponding dates: Feb-01-2024 (A3) and Apr-01-2024 (A5).
• new_x's is the date that is missing data: Mar-01-2024 (A4).

In cell B4, you would enter the formula:

=TREND((B3,B5), (A3,A5), A4)

Important: For the known_y's and known_x's, we reference the individual cells and group them inside parentheses like (B3,B5) because they are not next to each other in the sheet.

Excel finds the value on the straight line between February and April, which is $14,000.

Method 3: Visualize the Trend on a Chart

Sometimes you don't need to fill the cells with values - you just need to show a continuous trendline in a report. An Excel chart can do this without changing your underlying data.

Step 1: Create a Line Chart

Highlight your data, including the blank cells, and go to Insert > Charts > Line > 2-D Line. You’ll initially see a gap in your line where the data is missing.

Step 2: Modify the Chart Settings

Right-click anywhere on the chart area and select Select Data....

In the new window that appears, click the Hidden and Empty Cells button, which is usually located in the bottom-left corner.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 3: Connect the Data Points

In a final pop-up window, select the option to Connect data points with line and click OK twice to close the windows.

Your chart will now display a continuous, straight line across the gap, visually interpolating the missing points. Remember, this only affects the chart's appearance - the cells in your sheet will remain blank. But for a quick visual representation on a dashboard or in a presentation, it's perfect.

Final Thoughts

Knowing how to interpolate data in Excel using the Fill Series, a function, or making a dashboard with a chart not only cleans up your data tables but also gives you insights for effective decision-making. These methods are very handy for dealing with gaps in datasets while trying to complete an effective financial model. Whether it's the fast "Fill Series" method, the powerful "TREND" function, or creating charts, this three-pronged approach has a solution for any user.

Manually filling data gaps is useful, but it's often an early step in a much bigger reporting process. If your weekly routine involves logging into multiple analytics tools like Google Analytics, Facebook Ads, and Salesforce just to export CSVs and wrangle them in a spreadsheet, there is a much easier way. We created Graphed to automate that entire manual process. Instead of digging through platforms and piecing together reports, you connect your data sources to Graphed one time, and we keep them automatically updated. With our advanced tech, you can generate complete data-filled, error-proof, and executive-driven decision charts just through a chat. Imagine telling your boss 'here are all the answers' instead of 'here is the data dump for which you need an army to build reports.' Cool, right!