Most Excel trainers tell you to use FORECAST.LINEAR for interpolation. They’re wrong. FORECAST.LINEAR only fits a straight line across your entire dataset — not between two known points. If your X-values aren’t evenly spaced (and they rarely are), it returns garbage. Real interpolation needs local context. Not global regression.
Quick Answer
Excel doesn’t have an INTERPOLATE() function — but you can build linear interpolation in 37 keystrokes using this formula in cell D2: =B2+(B3-B2)*(E2-A2)/(A3-A2), assuming known X-values in A2:A3, Y-values in B2:B3, and target X in E2. No add-ins. No VBA. Works in Excel 2010+.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Manual Linear Formula | Enter =y1+(y2-y1)*(x-x1)/(x2-x1) referencing adjacent pairs |
Single-point lookup between two rows | Fails if x is outside [x1,x2]; requires sorted X-data |
| INDEX + MATCH + FORECAST.LINEAR | Find bracketing rows with MATCH, pull values with INDEX, feed into FORECAST.LINEAR | Dynamic ranges, multiple queries | FORECAST.LINEAR still assumes linearity — misleading near curve inflection |
| LINEST() + array math | Use =INDEX(LINEST(B2:B6,A2:A6),1)*F2+INDEX(LINEST(B2:B6,A2:A6),2) |
Quick slope/intercept reuse across many targets | Fits full range — not local; ignores gaps or outliers |
| TREND() with two-point array | Wrap known X/Y as arrays: =TREND({B4,B5},{A4,A5},{F2}) |
Clean, readable, no helper columns | Hardcoded row refs — breaks if inserted rows shift data |
Method 1 Deep Dive
Use this when you have exactly two reference points — say, temperature readings at 9:00 AM and 11:00 AM, and you need the value at 10:17 AM.
Here’s real data in A1:C6:
| Time (X) | Temp (°C) (Y) | Notes |
|---|---|---|
| 09:00 | 22.4 | Sarah Chen, Acme Corp |
| 11:00 | 28.1 | Jin Lee, Beta Labs |
| 10:17 | =B2+(B3-B2)*(A4-A2)/(A3-A2) | Target time → interpolate |
| 10:17 | 25.9 | Result (rounded) |
That formula in C4? It’s =B2+(B3-B2)*(A4-A2)/(A3-A2). You must sort X first — Excel won’t do it for you. Paste that into C4. Done.
⚠️ Counterintuitive tip: Don’t convert time to decimals manually. Excel stores time as fractions of a day. 09:00 = 0.375, 11:00 = 0.4583. Your formula works directly on those numbers — no conversion needed.
Method 2 Deep Dive
What if you have 12 sensor readings taken every 15 minutes, and need interpolated values for arbitrary timestamps? Use TREND() with hardcoded two-point arrays.
Assume your raw data lives in A10:B21 (timestamps and voltages). You want voltage at 2024-03-15 14:23:00, which falls between A15 (14:15) and A16 (14:30).
Do this:
- Select cell D15
- Type
=TREND({ - Click B15 → type
,→ click B16 → type},{ - Click A15 → type
,→ click A16 → type}, - Click F2 (your target timestamp) → close with
)
Final formula: =TREND({B15,B16},{A15,A16},F2)
Now press Ctrl+Enter — not Enter alone. That locks the array behavior even though it’s a single-cell result. Yes, TREND() is technically array-capable but doesn’t require Ctrl+Shift+Enter here. Still, Ctrl+Enter prevents accidental overwrites.
Sample output with real values:
| Timestamp | Voltage (V) | Interpolated |
|---|---|---|
| 2024-03-15 14:15:00 | 4.22 | 4.37 |
| 2024-03-15 14:23:00 | ||
| 2024-03-15 14:30:00 | 4.51 | |
| 2024-03-15 15:00:00 | 4.89 | (next pair) |
This method avoids volatile OFFSET or INDIRECT. It’s stable. It’s fast. And it’s the only one that works reliably with date-time values without manual decimal conversion.
Cheat Sheet
| Task | Formula / Shortcut | Cell Example |
|---|---|---|
| Linear interp between A2/A3 and B2/B3 | =B2+(B3-B2)*(E2-A2)/(A3-A2) | D2 |
| TREND on dynamic pair (X in A15:A16, Y in B15:B16) | =TREND({B15,B16},{A15,A16},F2) | G15 |
| Force array entry (safe habit) | Ctrl+Enter | After typing any TREND or LINEST |
| Find bracketing row (largest X ≤ target) | =MATCH(F2,A2:A20,1) | H2 |
| Convert time to decimal (if needed) | =HOUR(A2)+MINUTE(A2)/60 | I2 |
