Interpolate in Excel: Linear, FORECAST, and TREND Methods Explained

Learning how to interpolate excel data is one of the most practical skills for analysts, engineers, scientists, and finance professionals who need to estimate values between known data points. Whether you are filling gaps in a sensor log, projecting revenue between quarterly reports, or building a calibration curve for a lab experiment, Excel offers several built-in approaches that range from a simple two-point linear formula to multi-variable regression with the TREND function. This complete guide walks through every method with real datasets, downloadable formulas, and visual checks so you can pick the right tool for your data shape.

Interpolation differs from extrapolation in one critical way: interpolation estimates a value inside the range of your known x-values, while extrapolation pushes beyond that range and carries far more uncertainty. Excel handles both, but the safest applications are interior estimates where the underlying relationship is reasonably smooth. Throughout this article you will see examples with monthly temperature readings, dose-response chemistry curves, financial yield curves, and time-stamped sales data so you can match the technique to your own use case without guesswork.

The most common technique is linear interpolation, which assumes a straight line between two adjacent known points. It is fast, transparent, and works beautifully when your data changes at a roughly constant rate. We will start there because it builds intuition for the more advanced methods. After mastering the linear approach, we move into FORECAST.LINEAR, TREND, and GROWTH — three functions that fit a regression line through your entire dataset and produce interpolated values that respect the overall trend rather than just the two nearest neighbors.

Excel also offers indirect interpolation paths through chart trendlines, the Solver add-in, and array formulas built around INDEX, MATCH, and SLOPE. Power users sometimes wire up cubic spline interpolation with custom helper columns, and we will show a simplified version that gets you 95% of the accuracy of dedicated statistical packages without leaving the spreadsheet. Each method has trade-offs in accuracy, transparency, and update behavior, and a clear comparison table later in this article makes the choice obvious for your situation.

Before diving into formulas, sort your x-values in ascending order. Almost every interpolation method assumes monotonic input, and unsorted data is the single most common cause of wrong answers. A quick way to validate ordering is the simple check =IF(SUMPRODUCT((A2:A100<A1:A99)*1)=0,"sorted","NOT sorted") in a helper cell. If your dataset has duplicate x-values, average their y-values first because interpolation formulas behave unpredictably when two points share the same horizontal coordinate.

One more setup tip: convert your data to a structured Excel Table with Ctrl+T. Tables auto-expand formulas, named ranges, and chart series whenever you add rows, which means your interpolation worksheet keeps working when new data arrives next week. Combined with named ranges like KnownX and KnownY, your formulas read like English and survive months of edits without breaking — exactly the kind of robustness that separates a one-off calculation from a tool you actually reuse.

By the end of this guide you will have a complete interpolation toolkit, copy-ready formulas for every scenario, a diagnostic checklist for verifying results, and clear guidance on when to step up from linear methods to polynomial or spline fitting. Bookmark this page because the formulas in the examples are written so you can drop them straight into your own worksheets with only the cell references changed.

The classic linear interpolation formula in Excel is built from a single line of arithmetic that any analyst can audit. Given two known points (x1, y1) and (x2, y2), the interpolated y at a target x is y = y1 + (x − x1) × (y2 − y1) / (x2 − x1). Translated into an Excel formula with the target x in cell D2, the two surrounding known x-values in A2:A3, and their y-values in B2:B3, you write =B2+(D2-A2)*(B3-B2)/(A3-A2). The result updates instantly whenever your inputs change.

To make this scale across a large dataset, replace the manual cell picks with INDEX and MATCH. The lookup formula =FORECAST.LINEAR(D2,OFFSET(B1,MATCH(D2,A:A,1)-1,0,2),OFFSET(A1,MATCH(D2,A:A,1)-1,0,2)) automatically grabs the two surrounding rows for any target x. If you have used vlookup excel formulas to pull exact matches, this OFFSET pattern is the interpolation equivalent — it returns approximate matches and then interpolates the remainder.

Sorting matters enormously here. MATCH with a third argument of 1 requires ascending x-values, and a single out-of-order row produces silently incorrect results because the function returns the wrong surrounding pair. Add a guard with =IF(AND(D2>=MIN(A:A),D2<=MAX(A:A)),your_formula,"Out of range") so that any extrapolation attempt is flagged rather than answered. This single change has saved analysts from countless embarrassing reports built on linearly projected values that sit outside the observed data.

Linear interpolation shines on small, evenly spaced datasets like monthly KPIs, hourly temperature logs, or property valuations between quarterly appraisals. It is also the right choice when your stakeholder will scrutinize each calculation in a meeting because the math is verifiable on a calculator. The downside is that it ignores curvature — if your underlying process is exponential, logarithmic, or oscillating, linear interpolation between two points will systematically over or underestimate the truth, and the error grows with the gap between known points.

An elegant variation uses the SLOPE and INTERCEPT functions together: y = SLOPE(known_ys, known_xs) × target_x + INTERCEPT(known_ys, known_xs). This computes a least-squares line through every point and returns the regression-based estimate. It is identical mathematically to FORECAST.LINEAR but exposes the slope and intercept as separate cells, which is useful for documentation, sensitivity analysis, and Monte Carlo simulations that vary the slope deliberately.

For piecewise linear interpolation with many segments — say a yield curve with 30 maturities — the cleanest pattern uses a helper column with the slope between each adjacent pair: =(B3-B2)/(A3-A2) dragged down the table. Then the interpolated value formula simplifies to =VLOOKUP(D2,A:C,2,TRUE)+(D2-VLOOKUP(D2,A:B,1,TRUE))*VLOOKUP(D2,A:C,3,TRUE) where column C holds the precomputed slopes. This pattern is fast even on datasets of 100,000 rows.

Remember that interpolation accuracy depends on how well the straight-line assumption holds locally. A quick diagnostic is to plot your data on a scatter chart and visually check whether the points roughly fall on a line within each gap. If they curve noticeably, jump to the polynomial trendline or spline approaches covered later. The two-minute chart check has averted more bad interpolations than any formula refinement ever has.

For datasets where linear interpolation is too crude and global regression is too rigid, polynomial and cubic spline interpolation offer a powerful middle ground. Excel does not ship a dedicated spline function, but you can build one with LINEST and a polynomial design matrix. A second-order polynomial fit uses =LINEST(known_ys, known_xs^{1,2}) and returns coefficients for y = a + b·x + c·x². Multiplying these by your target x and summing yields a smooth interpolated value that respects curvature.

Higher-order polynomials — up to order six on a chart trendline — capture more complex shapes but risk overfitting, where the curve oscillates wildly between known points. A safe rule of thumb is to keep the polynomial order well below the number of data points and to validate by removing one point at a time and checking that the model still predicts it accurately. This leave-one-out cross-validation is easy to automate in Excel with a single column of LINEST formulas.

Cubic spline interpolation is the gold standard for smooth curves through every known point. The math involves solving a tridiagonal system of equations, which sounds heavy but reduces to a sequence of helper columns. Build columns for the second derivatives using a recursive formula, then evaluate the spline at any target x with a short arithmetic combination of the surrounding two known points and their second derivatives. Several free Excel templates implement this and run instantly on thousands of points.

For time series with seasonality, decompose the data first with a moving average or a STL-style transformation, interpolate the smoothed trend, and add the seasonal pattern back. This two-stage approach prevents the interpolator from mistaking a recurring summer spike for a permanent shift in level. Power Query in modern Excel can automate the decomposition and the interpolation in a single refresh cycle, making the workflow reproducible and auditable.

The Solver add-in provides a different angle. Define a candidate functional form — say y = a·x^b — set up cells for the parameters a and b, compute squared residuals across known points, and ask Solver to minimize the sum. Once Solver converges, the parameter cells hold the best-fit values and you can interpolate by evaluating the function at any x. This works for arbitrary nonlinear models that no built-in function handles directly, including logistic curves and damped oscillations common in physical sciences.

Two-dimensional interpolation is needed when your data lives on a grid — think look-up tables for thermodynamic properties indexed by both temperature and pressure. The simplest approach is bilinear interpolation: interpolate along one axis at the two surrounding rows, then interpolate the two results along the other axis. The formula uses INDEX and MATCH to grab the four corner points and arithmetic to blend them. Bicubic and biquadratic variants exist but rarely justify the added complexity.

For massive datasets where formula performance matters, switch to Power Query or the new Python in Excel feature. Power Query merges and interpolates in the M language without rebuilding sheets, while Python in Excel exposes pandas and scipy interpolation routines including cubic, quadratic, and Akima spline directly inside a workbook. These tools were not available a few years ago and dramatically expand what is practical inside Excel for analysts who do not want to leave the spreadsheet environment.

The most common interpolation mistake in Excel is unsorted input data, and it produces wrong answers silently. MATCH with approximate matching assumes ascending order, so a single misplaced row redirects the formula to the wrong surrounding pair. Always sort with Data → Sort by x-value ascending before building interpolation formulas. If new data arrives unsorted, wrap the lookup in SORT() — available in Microsoft 365 — to sort dynamically without touching the underlying table layout.

The second classic mistake is duplicate x-values. Two rows with the same x cause MATCH to return only the first one, ignoring the second entirely. The fix is to aggregate duplicates with AVERAGEIFS or a PivotTable before interpolating. If duplicates are intentional — for instance multiple sensor readings at the same time — average them or take the median to produce a single representative y-value per x, then interpolate the cleaned dataset.

A third trap is mismatched data types. Excel stores dates as serial numbers, so date-based interpolation works only when both known_xs and the target x are genuine dates, not text strings that look like dates. Use =ISNUMBER(A2) on a sample cell to verify, or convert with DATEVALUE if necessary. Mixing text and numeric x-values causes errors that are surprisingly hard to spot because the visible cell content looks correct in both formats.

Out-of-range targets trigger silent extrapolation in FORECAST and TREND. Add a defensive wrapper such as =IF(OR(D2<MIN(KnownX),D2>MAX(KnownX)),"Out of range",FORECAST.LINEAR(D2,KnownY,KnownX)) so reviewers see a clear flag rather than a confidently wrong number. The same technique with named ranges keeps formulas readable across long worksheets and survives column inserts that break A1-style references.

Volatile recalculation can also bite. Functions like OFFSET and INDIRECT mark the worksheet as volatile and recalculate on every change anywhere in the workbook, which slows large interpolation tables. Where possible, swap OFFSET for INDEX, which is non-volatile and just as flexible. The performance difference on a 50,000-row interpolation worksheet is often the difference between a one-second refresh and a thirty-second pause.

Finally, watch for circular references when interpolation results feed back into other formulas that adjust the known points. This pattern is common in iterative calibration but it is brittle. Use Excel's iterative calculation mode deliberately rather than by accident, document the iteration logic clearly, and prefer the Solver add-in or VBA for genuine iterative procedures. Hidden circularities have caused notorious reporting errors in published financial models — a quick Formulas → Error Checking → Circular References scan catches them.

One pleasant surprise: modern Excel's dynamic arrays make multi-target interpolation trivial. A single =TREND(KnownY, KnownX, NewX) entered in one cell spills the results down the column automatically. Combined with structured table references, the formula reads naturally and updates whenever new data lands, removing the need to drag formulas manually. If you are still using Ctrl+Shift+Enter array entry from Excel 2016 and earlier, upgrading to Microsoft 365 is one of the highest-impact productivity changes you can make.

To consolidate everything, start every interpolation task with three setup steps that take less than two minutes: sort the data, convert to a Table, and create named ranges KnownX and KnownY. These three habits eliminate the majority of interpolation errors before they happen. From that clean baseline you can swap interpolation methods freely without rebuilding formulas, which makes experimentation cheap and encourages picking the right method rather than the first one that returns a number.

Choose linear interpolation when the data is small, smooth, and audit-critical. Choose FORECAST.LINEAR or SLOPE+INTERCEPT when noise is present and you want a single stable line through every point. Choose TREND for batch interpolation of many targets or multivariate inputs. Choose GROWTH for compounding processes. Choose a polynomial trendline or LINEST with a design matrix when curvature is obvious but limited. Reach for cubic splines or Python-in-Excel scipy routines only when simpler methods fail visual inspection.

Document your choice in the worksheet itself. A single comment cell next to the interpolation column reading "Method: FORECAST.LINEAR — R²=0.987 on training data — validated 2026-05-15" tells future reviewers everything they need to know without opening a separate document. This habit is rare among analysts and instantly elevates the perceived quality of your work in audits and stakeholder reviews.

For dashboards that update automatically, combine interpolation with conditional formatting that highlights any interpolated cell with a different background color. This visual cue reminds viewers that the value is estimated rather than measured, which prevents downstream misuse. The conditional formatting rule is simply =ISNUMBER(MATCH(target_cell, known_x_range, 0))=FALSE, which evaluates TRUE for any cell whose x-value is not in the known set.

When sharing interpolated results outside your team, always include a brief methodology note: which function, what dataset, what time period, and what known limitations. A one-paragraph caveat under every chart that uses interpolated data prevents misinterpretation and builds trust. Engineering, scientific, and financial regulators expect this kind of transparency, and adopting it as a default habit pays off the first time a reviewer questions your numbers.

Practice is what cements all of this. Build a small interpolation playground workbook with synthetic data — a sine wave, an exponential, a polynomial, and a noisy linear trend — and apply every method covered here to each dataset. Comparing the estimates against the known true function reveals which method works best for which data shape far faster than reading about it. Re-run the exercise every few months to keep the muscle memory fresh.

Finally, remember that interpolation is a means to an end. The goal is usually to make a decision — buy or sell, ship or reject, alert or ignore. Choose the method whose accuracy matches the cost of being wrong. A back-of-envelope estimate for an internal exploration deserves linear interpolation, while a calibration curve for a medical device needs cubic splines, leave-one-out validation, and documented uncertainty bounds. Right-sizing the method to the stakes is the hallmark of a senior analyst.