Interpolation in Excel lets you estimate missing values between known data points, a common need in forecasting, engineering, and financial modeling. This list covers ten practical methods, from simple formulas to built-in tools, so you can choose the right approach for your data and skill level. Whether you’re a beginner or an advanced user, you’ll find a technique that fits.
1. Linear Interpolation with FORECAST.LINEAR
The FORECAST.LINEAR function (formerly FORECAST) is the simplest way to interpolate linearly. It fits a straight line through your known x and y values and returns the estimated y for a new x. For example, =FORECAST.LINEAR(A2, known_y's, known_x's) gives a quick result, but it assumes a linear trend, which may not suit curved data.
2. TREND for Multiple Points
TREND is similar to FORECAST.LINEAR but can return an array of interpolated values at once. It uses least-squares regression to fit a line, making it ideal for filling several gaps in a series. Enter it as an array formula (Ctrl+Shift+Enter in older Excel) to get all predictions in one go.
3. GROWTH for Exponential Interpolation
When your data grows exponentially (e.g., population or compound interest), GROWTH fits an exponential curve instead of a line. It works like TREND but returns values based on the equation y = b * m^x. Use it when the rate of change is proportional to the current value, not constant.
4. Manual Linear Interpolation Formula
For full control, write your own formula: =y1 + (x - x1) * (y2 - y1) / (x2 - x1). This is the classic two-point interpolation, and it’s transparent—you see exactly which points are used. It’s perfect for small datasets where you want to avoid the overhead of statistical functions.
5. INDEX and MATCH for Piecewise Interpolation
To interpolate between two specific points in a large table, combine INDEX and MATCH to find the surrounding x values. For instance, MATCH finds the position of the largest x less than your query, then INDEX retrieves the corresponding y values. This is a robust way to handle non-linear data by interpolating locally.
6. OFFSET with MATCH for Dynamic Ranges
OFFSET can create a moving range that shifts as you copy the formula down, enabling interpolation across many rows. Pair it with MATCH to locate the base point, then OFFSET to grab the next point. This is powerful for automating interpolation over a large dataset, but it’s volatile and can slow down large workbooks.
7. Interpolate with the FORECAST.ETS Function
FORECAST.ETS is designed for time-series data with seasonal patterns, but it can also interpolate missing values. It uses exponential smoothing to account for trends and seasonality, making it more accurate than linear methods for cyclical data. You can even use its seasonality parameter to fine-tune the fit.
8. Using the LINEST Function for Polynomial Interpolation
LINEST returns the coefficients of a regression line, which you can then use to interpolate. For polynomial interpolation, add x^2 or x^3 as additional variables in the LINEST argument. This lets you fit a curve through your points, giving more accurate estimates for non-linear relationships.
9. The FORECAST Function (Legacy)
While FORECAST.LINEAR is the current version, the older FORECAST function still works in Excel for compatibility. It performs the same linear interpolation but is kept for legacy reasons. If you’re working with older spreadsheets, you might see it, but it’s best to use the newer function for future-proofing.
10. VBA Custom Function for Interpolation
If none of the built-in functions meet your needs, you can write a simple VBA function to interpolate. For example, a custom function that takes two x-y pairs and a new x, then returns the linear interpolation. This is ideal for specialized interpolation methods like splines or for automating complex workflows, but it requires enabling macros.
How to Choose
The right method depends on your data’s shape and your goal. For linear data, FORECAST.LINEAR or a manual formula is best. For exponential trends, use GROWTH. For seasonal time series, FORECAST.ETS is superior. If you need to interpolate within a large table, INDEX/MATCH or OFFSET give you control. For non-linear but non-seasonal data, LINEST with polynomial terms is flexible. Finally, if you need a custom method, VBA is the way to go. Also consider your audience—simple formulas are easier to audit than array formulas or VBA.
Frequently Asked Questions
What is the difference between interpolation and extrapolation? Interpolation estimates values within your known data range; extrapolation predicts outside it. Excel functions like FORECAST.LINEAR can do both, but extrapolation is less reliable.
Can I interpolate with missing x values? Yes, but you need to decide the x-step. If your x values are equally spaced, you can use a simple linear formula. If not, you must use a method that accounts for actual x positions, like INDEX/MATCH.
Why does my interpolation look wrong for curved data? Linear interpolation assumes a straight line between points. For curved data, use polynomial or exponential methods like LINEST or GROWTH to get a better fit.
Is FORECAST.ETS only for seasonal data? No, but it’s optimized for it. You can set seasonality to zero for non-seasonal data, but then it’s similar to a moving average.
How do I interpolate in Excel without a function? You can use the chart trendline feature to fit a line or curve, then use the equation to calculate values. But that’s manual and less precise than using functions directly.
Can I interpolate between two points only? Yes, the manual formula =y1 + (x - x1) * (y2 - y1) / (x2 - x1) is exactly for that. It’s simple and doesn’t require any statistical assumptions.
Final Thoughts
Excel offers a variety of interpolation methods, each suited to different data patterns and needs. Start with the simplest that works—often FORECAST.LINEAR or a manual formula—and only move to more complex methods like VBA or polynomial fits when your data demands it. Understanding the assumptions behind each method ensures your interpolated values are credible and useful.