Excel Interpolate Missing Values: 4 Methods

Errors & fixes · Updated November 22, 2026

Yes, Excel can interpolate missing values, and the best method depends on your data: use =FORECAST() or =TREND() for linear interpolation between known points, =GROWTH() for exponential, or fill gaps with the linear trend tool for a quick visual result. For example, to estimate a missing y-value at x=5 between known points (2,10) and (8,22), the formula =FORECAST(5, {10,22}, {2,8}) returns 16. This article explains each approach, how to choose, and common pitfalls.

What Interpolation Means in Excel

Interpolation estimates an unknown value that falls between two known data points, assuming a relationship (usually linear) between them. It differs from extrapolation, which predicts values outside the known range. In Excel, you can interpolate manually with formulas, use built-in functions, or leverage the chart trendline feature. The key is that interpolation assumes your data follows a smooth pattern—if it jumps randomly, any estimate will be unreliable.

Method 1: Using FORECAST or FORECAST.LINEAR

The FORECAST function (renamed FORECAST.LINEAR in Excel 2016 and later) is the simplest for linear interpolation. Syntax: =FORECAST.LINEAR(x, known_ys, known_xs). It fits a straight line through your known points and returns the y-value for a given x.

Example: Suppose you have temperature readings at hours 0, 2, 4, 6, 8 in A2:A6 and corresponding values in B2:B6. To estimate the value at hour 3, use:

=FORECAST.LINEAR(3, B2:B6, A2:A6)

This returns the linear estimate at x=3. Note that FORECAST uses all known points to fit a least-squares line, not just the two nearest neighbors. If you want true piecewise interpolation (only between the two surrounding points), you need a different approach.

Method 2: Using TREND for Linear Regression

TREND works identically to FORECAST.LINEAR but returns an array of values, which is useful for filling multiple gaps at once. Syntax: =TREND(known_ys, known_xs, new_xs). For a single value, it behaves like FORECAST. For multiple missing x-values, enter the formula as an array (Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365).

Example: With known x in A2:A6 and y in B2:B6, and missing x-values in D2:D4, select E2:E4 and type:

=TREND(B2:B6, A2:A6, D2:D4)

Press Ctrl+Shift+Enter (or just Enter in Excel 365) to fill all three estimates.

Method 3: Using GROWTH for Exponential Data

If your data grows exponentially (e.g., population, compound interest), use GROWTH instead of linear methods. Syntax: =GROWTH(known_ys, known_xs, new_xs). It fits an exponential curve y = b * m^x. For a single point, use it like FORECAST. For multiple, array-enter as above.

Example: Known values at x=1,2,3,4 are 10, 20, 40, 80. To estimate at x=2.5, use:

=GROWTH({10,20,40,80}, {1,2,3,4}, 2.5)

This returns approximately 28.28, which is the geometric mean of 20 and 40—the correct exponential interpolation.

Method 4: Linear Trend Fill (Quick and Visual)

For a quick, visual interpolation without formulas, use Excel’s fill handle with linear trend:

  1. Select the cells containing the known values around the gap (e.g., B2 and B6).
  2. Hold the right mouse button and drag down over the missing cells (B3:B5).
  3. Release and choose Fill Series from the context menu.
  4. Excel fills the gap with a linear trend based on the two endpoints.

This method is great for quick estimates but only uses the two endpoint values, ignoring any intermediate known points. It also overwrites any existing data in the selected range, so use it on a copy of your data.

How to Choose the Right Method

  • Linear data (constant rate of change): Use FORECAST.LINEAR or TREND.
  • Exponential growth/decay: Use GROWTH.
  • Only two known points: FORECAST and TREND both give the same result as a straight line between them.
  • Multiple gaps with a clear trend: TREND or GROWTH with array formulas is efficient.
  • Non-linear, non-exponential data: Consider polynomial regression using LINEST with higher-order terms, or use a chart trendline to see which model fits best.

Common Problems and How to Avoid Them

  • Using FORECAST on non-linear data: The function forces a straight line, which can be wildly inaccurate. Always plot your data first.
  • Interpolating beyond the known range: That’s extrapolation, not interpolation, and it’s much less reliable. FORECAST will still return a value, but treat it with caution.
  • Mixing x and y ranges: Ensure known_ys and known_xs are the same length and correspond correctly. A mismatch returns #VALUE! or a wrong result.
  • Gaps in x-values: Interpolation assumes x-values are numeric and evenly spaced or at least sorted. If your x-values are text or have duplicates, the functions may not work.
  • Hidden empty cells: If your known_ys range contains blanks, Excel treats them as zero, skewing the result. Use a dynamic range or clean your data first.

Frequently Asked Questions

How do I interpolate between two specific points only?

To interpolate strictly between two known points (x1,y1) and (x2,y2) at x, use the formula: =y1 + (x - x1) * (y2 - y1) / (x2 - x1). This is the linear interpolation formula. For example, with points (2,10) and (8,22), at x=5: =10 + (5-2)*(22-10)/(8-2) returns 16. This ignores all other data points.

Can I interpolate missing values in a column with blanks?

Yes, but you must first define the known x-values (e.g., row numbers or time stamps). If your x-values are simply the position (1,2,3…), you can use FORECAST with the known y-values and their positions. For a column with blanks, you’d need to create a helper column with the x-values for each row, then use FORECAST for each blank cell individually, referencing the nearest known points.

Does Excel have a built-in interpolation function like in MATLAB?

No, Excel does not have a dedicated INTERPOLATE function. The closest are FORECAST, TREND, and GROWTH, which are regression-based. For more advanced interpolation (e.g., spline), you’d need to use VBA or an add-in like the Analysis ToolPak, which includes a regression tool but not spline interpolation.

Why does FORECAST give a different value than the linear trend fill?

FORECAST uses all known data points to fit a least-squares regression line, while the linear trend fill only uses the two endpoints you select. If your data is not perfectly linear, the regression line will differ from the simple two-point line. For example, with points (1,1), (2,4), (3,9), FORECAST at x=2.5 might return 5.0, but the two-point line between (2,4) and (3,9) gives 6.5. Choose based on whether you want a best-fit line or a direct segment interpolation.

Can I interpolate missing values in a chart?

Yes, you can add a trendline to a chart and use its equation to estimate values. Right-click a data series, choose Add Trendline, select a type (linear, exponential, etc.), and check ‘Display Equation on chart’. Then use that equation in a formula. However, this is manual and less precise than using functions directly.

What if my data is seasonal or cyclical?

Simple linear or exponential interpolation will fail. Consider using Excel’s FORECAST.ETS function (exponential smoothing) for time-series data with seasonality, but note that it’s designed for forecasting beyond the data, not for filling internal gaps. For internal gaps, you might need to use a moving average or a more advanced statistical method, possibly via the Analysis ToolPak’s Moving Average tool.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.