Interpolating between cells in Excel is a common task when you have data points and need to estimate values in between. This list covers ten practical methods, from simple formulas to built-in tools, suitable for beginners and advanced users alike. Each method is explained with specific steps and use cases, helping you choose the right approach for your data.
1. Linear Interpolation with FORECAST.LINEAR
The FORECAST.LINEAR function is the simplest way to perform linear interpolation. It calculates a predicted value along a straight line based on known x and y pairs. For example, if your x values are in A2:A10 and y values in B2:B10, use =FORECAST.LINEAR(D2, B2:B10, A2:A10) where D2 contains the new x. This method assumes a linear relationship, so it’s best for data that follows a straight line.
2. Using the TREND Function
TREND works similarly to FORECAST.LINEAR but returns an array of values, making it useful for interpolating multiple points at once. Select a range of cells, enter =TREND(B2:B10, A2:A10, D2:D5) as an array formula (Ctrl+Shift+Enter in older Excel), and it fills all selected cells. This is efficient when you need several interpolated values. Like FORECAST, it only handles linear relationships.
3. Manual Linear Interpolation Formula
For full control, you can write the linear interpolation formula manually: =B2 + (D2-A2)*(B3-B2)/(A3-A2). This calculates the value at D2 between the two closest data points (A2,B2) and (A3,B3). This method is transparent and doesn’t rely on built-in functions, which is useful for teaching or when you need to adjust the formula for specific cases. It also works in older Excel versions that lack newer functions.
4. Interpolate with the FORECAST.ETS Function
If your data has seasonality or trends, FORECAST.ETS can interpolate using exponential smoothing. It’s designed for time series and can handle missing points. Use =FORECAST.ETS(D2, B2:B10, A2:A10) to get an interpolated value that considers patterns. This is more accurate for non-linear data than simple linear methods, but it requires a regular time interval and can be overkill for simple datasets.
5. Using the GROWTH Function for Exponential Data
When your data grows exponentially (e.g., population, compound interest), the GROWTH function fits an exponential curve. Its syntax is =GROWTH(B2:B10, A2:A10, D2). This is ideal for data that increases by a constant percentage rather than a constant amount. It’s more accurate than linear interpolation for such cases, but it assumes your data truly follows an exponential pattern.
6. Interpolate with the LINEST Function
LINEST returns the statistics of a linear regression, which you can use to interpolate. For example, =LINEST(B2:B10, A2:A10) returns the slope and intercept, then you can compute =slope*D2+intercept. This is useful when you need the underlying equation or want to see the fit quality. It’s more complex but offers insight into the data’s behavior.
7. Using the SLOPE and INTERCEPT Functions
Similar to LINEST, you can get the slope and intercept separately with =SLOPE(B2:B10, A2:A10) and =INTERCEPT(B2:B10, A2:A10). Then combine them to interpolate: =slope*D2+intercept. This is straightforward and easy to read, making it a good choice for spreadsheets that others will use. It’s essentially the same as linear interpolation but with clear, reusable components.
8. Interpolate with the FORECAST Function (Legacy)
In older Excel versions, FORECAST is the predecessor to FORECAST.LINEAR. Its syntax is identical: =FORECAST(D2, B2:B10, A2:A10). While it’s been replaced, it still works in current versions for compatibility. If you’re using an older Excel or need to share files with users on older versions, this function ensures your formulas work without updates.
9. Using the XLOOKUP or INDEX/MATCH with Interpolation
For non-linear interpolation, you can use XLOOKUP or INDEX/MATCH to find the two surrounding points, then interpolate manually. For example, =XLOOKUP(D2, A2:A10, B2:B10, , -1) finds the last value less than D2, then you can calculate the interpolation. This method is flexible and works with any data, but requires more steps. It’s useful when you need to interpolate within a range that isn’t sorted or when you want to handle edge cases.
10. Interpolate with the FORECAST.ETS.SEASONALITY Function
This function calculates the length of the seasonal pattern in your data, which you can use to improve interpolation. For example, =FORECAST.ETS.SEASONALITY(B2:B10, A2:A10) returns the number of periods in a season. You can then feed this into FORECAST.ETS for more accurate interpolation. This is advanced and best for data with clear seasonality, like monthly sales patterns. It helps avoid over- or under-smoothing.
How to Choose
Choosing the right interpolation method depends on your data’s behavior and your goal. For simple, linear data, FORECAST.LINEAR or a manual formula is sufficient. If your data has trends or seasonality, consider FORECAST.ETS or GROWTH. For full control, manual formulas or SLOPE/INTERCEPT are transparent and easy to audit. If you need multiple interpolated points, TREND or array formulas save time. Always visualize your data first to see if it’s linear, exponential, or seasonal, then pick the method that matches.
Frequently Asked Questions
What is the simplest way to interpolate in Excel? The simplest is the FORECAST.LINEAR function, as it requires minimal setup and handles linear interpolation automatically.
Can I interpolate non-linear data? Yes, use FORECAST.ETS for seasonal data or GROWTH for exponential data. For other patterns, you may need to fit a custom curve or use a manual approach.
How do I interpolate between two specific cells? Use the manual formula: =B2 + (D2-A2)*(B3-B2)/(A3-A2), where D2 is your new x, and (A2,B2) and (A3,B3) are the surrounding points.
Does interpolation work with missing data? Yes, methods like FORECAST.ETS can handle missing points by estimating them based on the overall pattern. Linear methods require you to specify the surrounding points manually.
Can I interpolate in a pivot table? Pivot tables don’t have built-in interpolation, but you can use GETPIVOTDATA or reference pivot cells in your interpolation formulas.
What’s the difference between FORECAST and FORECAST.LINEAR? FORECAST is the legacy version; FORECAST.LINEAR is the current equivalent with the same syntax. Both perform linear regression for interpolation.
Final Thoughts
Interpolation in Excel is a versatile skill that can save time and improve accuracy when working with incomplete datasets. Start with the simplest method that fits your data, and don’t hesitate to experiment with more advanced functions as your needs grow. Remember to check your data’s pattern and choose the method that respects it, ensuring your interpolated values are reliable.