This listicle covers ten practical methods for interpolating data in Excel, from built-in functions to manual techniques. Whether you’re a financial analyst filling missing quarterly figures, an engineer estimating intermediate values from a sensor log, or a student working with experimental data, you’ll find a method here that fits your skill level and data type. Each item explains what the technique does and when it’s worth using.
1. FORECAST.LINEAR Function
FORECAST.LINEAR is the modern replacement for the older FORECAST function, and it’s the quickest way to perform linear interpolation between known data points. You supply the x-value you want to estimate, the known y-range, and the known x-range, and Excel fits a straight line through all your data. It’s ideal when your data roughly follows a linear trend and you need a single estimated value without building a chart.
2. TREND Function
TREND is similar to FORECAST.LINEAR but can return multiple estimated y-values at once when you enter it as an array formula. You can give it a set of new x-values and it will return the corresponding interpolated y-values in adjacent cells. This is a huge time-saver when you need to fill a whole column of missing data points, not just one.
3. GROWTH Function for Exponential Data
When your data grows or decays exponentially—like population growth, compound interest, or certain scientific measurements—linear interpolation will give misleading results. GROWTH fits an exponential curve to your known data and returns interpolated values along that curve. Use it when your data shows a consistent percentage rate of change rather than a constant additive change.
4. LINEST for Regression-Based Interpolation
LINEST is a more advanced function that returns the coefficients of the best-fit line, which you can then use to interpolate manually with the equation y = mx + b. It’s more transparent than FORECAST.LINEAR because you can see the slope and intercept, and you can use those coefficients in other calculations. This is worth it when you need to document your interpolation method or when you want to apply the same trend line to multiple new x-values.
5. Interpolation with the FORECAST.ETS Function
FORECAST.ETS is designed for time-series data with seasonality, such as monthly sales or website traffic. It detects seasonal patterns and uses exponential smoothing to forecast missing values, which makes it far more accurate than linear methods for cyclical data. You can control the seasonality length and confidence interval, making it a powerful tool for business forecasting.
6. Using the Fill Series Feature
Excel’s built-in Fill Series can interpolate numeric data linearly or with a growth trend, but it works on a single series, not between two known points. You select a range, go to Home > Fill > Series, and choose Linear or Growth, and Excel fills the selected cells with evenly spaced values. This is perfect for generating a complete sequence of x-values, like dates or measurement intervals, before you interpolate the corresponding y-values.
7. Chart Trendline with a Forecast
You can add a trendline to a scatter chart and set the forecast forward or backward to extend your data visually, then read the interpolated values from the chart. Right-click the data series, choose Add Trendline, and select Linear, Exponential, or Polynomial. This method is great for a quick visual check, but it’s less precise than using functions because you’re reading values off a chart, not calculating them exactly.
8. The FORECAST Function (Legacy)
Older Excel versions use FORECAST instead of FORECAST.LINEAR, and while it’s been deprecated, it still works in current versions for compatibility. If you’re sharing a workbook with someone using an older Excel, using FORECAST ensures your formulas won’t break. It does exactly the same linear interpolation as FORECAST.LINEAR, so the choice is purely about compatibility.
9. Polynomial Interpolation with LINEST
When your data curves, a straight line won’t fit well, but you can use LINEST with a polynomial order (like squared or cubed) to fit a curve. You enter LINEST with the y-range and x-range raised to the powers you want, and it returns the coefficients of the polynomial equation. Then you use that equation to interpolate any x-value. This is more accurate than linear for data that curves, but it can overfit if you use too high an order.
10. Manual Linear Interpolation Formula
For full control, you can write your own interpolation formula using the equation for a line between two points: y = y1 + (x – x1) * (y2 – y1) / (x2 – x1). You’ll need to identify the two known points that surround your target x-value, which you can do with MATCH and INDEX functions. This method is transparent, doesn’t rely on any hidden fitting, and is the most accurate when your data is piecewise linear.
How to Choose
The right interpolation method depends on the nature of your data and what you need from the result. If your data is roughly linear, FORECAST.LINEAR or manual linear interpolation is simple and accurate. For exponential growth, use GROWTH; for seasonal patterns, use FORECAST.ETS. If you need to document your work or apply the same trend to many points, LINEST gives you the coefficients to reuse. Always check your data visually with a chart to see if a straight line or a curve fits better, and be wary of extrapolating far beyond your known data range—interpolation between points is safer than predicting outside them.
Frequently Asked Questions
Can I interpolate missing data in a column without writing formulas? Yes, you can use the Fill Series feature for linear or growth trends, but it fills the entire selected range with a uniform trend, not based on surrounding points. For true interpolation between two points, you’ll need a formula or a chart trendline.
What’s the difference between interpolation and extrapolation? Interpolation estimates values within the range of your known data points, while extrapolation estimates values outside that range. Interpolation is generally more reliable because you’re filling gaps between actual measurements, whereas extrapolation assumes the trend continues beyond your data.
Why does my FORECAST.LINEAR return an error? The most common cause is mismatched array sizes—your known y-range and known x-range must have the same number of cells. Also, if your known x-values contain duplicates or are not sorted, Excel may return an error or an inaccurate result.
Can I interpolate non-numeric data like categories? No, interpolation requires numeric values because it calculates intermediate numbers. For categorical data, you’d need to assign numeric codes first, but the interpolated results won’t have real-world meaning.
Is there a way to interpolate in Excel without a chart? Absolutely—all the functions like FORECAST.LINEAR, TREND, and GROWTH work without charts. Charts are only needed for the trendline method, which is a visual alternative.
How do I interpolate between two specific points only? Use the manual linear interpolation formula with the two points that bracket your target x-value. You can find those points with MATCH and INDEX, or simply look them up manually if your data is small.
Final Thoughts
Interpolating data in Excel is a core skill that saves time and improves accuracy when dealing with incomplete datasets. Start with the simplest method that fits your data’s shape—FORECAST.LINEAR for linear, GROWTH for exponential, and FORECAST.ETS for seasonal. As you get comfortable, explore LINEST for more control and transparency. The key is to understand your data’s behavior before choosing a method, and always verify your interpolated values against any known context.