To interpolate between two points in Excel, use a straight-line formula: estimated y = y1 + (target x − x1) × (y2 − y1) ÷ (x2 − x1). If x1 and y1 are in A2 and B2, x2 and y2 are in A3 and B3, and the target x is in D2, enter =B2+(D2-A2)*(B3-B2)/(A3-A2). This estimates a value between the known points, assuming the relationship is linear over that interval.
What interpolation means in a spreadsheet
Interpolation is a way to estimate an unknown value that falls between known values. For example, if a table records a temperature at 10 minutes and another at 20 minutes, you can estimate the temperature at 14 minutes by assuming it changed at a steady rate between those readings. Excel does not have a single dedicated INTERPOLATE function; you calculate the estimate with a formula.
Each point has an x-coordinate and a y-coordinate. The x-coordinate is the input or position, such as time, distance, or a measurement. The y-coordinate is the value associated with it, such as temperature, cost, or height. Interpolation uses the two known points that bracket the target x-value. The estimate is only as appropriate as the assumption that a straight line is a reasonable description between those points.
Set up two points and enter the formula
For a basic calculation, put the first point’s x-value in A2 and its y-value in B2. Put the second point’s x-value in A3 and its y-value in B3, then enter the target x-value in D2. For example:
- A2 = 10 and B2 = 100
- A3 = 20 and B3 = 160
- D2 = 14
With those values in those cells, enter =B2+(D2-A2)*(B3-B2)/(A3-A2) in another cell. The result is 124. The calculation starts at y = 100, then adds 4/10 of the change from 100 to 160, which is 24. So the estimated value at x = 14 is 124.
The formula can be read as a starting value plus a fraction of the change. The fraction is how far the target lies between the two x-values: (D2 − A2) ÷ (A3 − A2). Multiplying that fraction by the change in y-values gives the portion of the y-change to add to the first y-value. Using cell references makes it easy to change the points or target without rewriting the calculation.
Understand the assumptions behind the result
Linear interpolation assumes the y-value changes at a constant rate between the two known x-values. In the example, y rises by 60 over an x-interval of 10, so the rate is 6 y-units per x-unit. Moving four x-units from the first point therefore adds 24. This is a mathematical estimate, not a guarantee that the real-world process followed a perfectly straight path.
Choose points that make sense for the question. If a process changes sharply, has a threshold, or follows a curved pattern, a straight line between widely separated measurements may be a poor estimate. Points closer to the target often better capture local behavior, provided the measurements are reliable. Keep track of units as well: if x is in hours and y is in kilometers, the result is a distance estimate at the specified time, not a rate.
Interpolation is also different from extrapolation. Interpolation estimates within the x-range of the known points. Extrapolation estimates beyond it, where the same straight-line trend may not continue. The same formula will calculate either result, but an output outside the endpoints is an extrapolation and usually carries more uncertainty.
Interpolate from a longer data table
When you have many points, first find the two rows whose x-values surround the target. Suppose the x-values are in A2:A10 in ascending order, the corresponding y-values are in B2:B10, and the target is in D2. In Excel for Microsoft 365 on Windows, the following formula finds the lower point with MATCH and calculates between it and the next row:
With x-values in A2:A10 sorted in ascending order, y-values in B2:B10, and the target in D2, enter =IF(D2=INDEX(A2:A10,ROWS(A2:A10)),INDEX(B2:B10,ROWS(B2:B10)),LET(idx,MATCH(D2,A2:A10,1),xlo,INDEX(A2:A10,idx),ylo,INDEX(B2:B10,idx),xhi,INDEX(A2:A10,idx+1),yhi,INDEX(B2:B10,idx+1),ylo+(D2-xlo)*(yhi-ylo)/(xhi-xlo))). For a target inside the table’s range, it returns the interpolated value; if D2 equals the final x-value, it returns the final y-value.
This lookup formula relies on the x-values being numeric, in ascending order, and free of duplicates. The target must be at least the first x-value and no greater than the last; a target below the first point can produce a MATCH error. If the data is not sorted, sort the paired rows together so each x-value stays matched with its y-value. The formula uses LET, which is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, use helper cells to identify the lower and upper points, then apply the basic two-point formula.
Check endpoints, duplicates, and invalid inputs
A few checks prevent common errors from becoming unnoticed estimates:
- Make sure the two x-values differ. If x1 equals x2, the formula divides by zero because there is no horizontal interval to interpolate across.
- Confirm the target is between the chosen x-values when you intend interpolation rather than extrapolation.
- Check that x and y cells contain numbers, not text that merely looks numeric.
- For a multirow table, sort x-values in ascending order and keep the corresponding y-values in the same rows.
- Decide how to handle missing values and duplicate x-values before calculating. A duplicate x-value can make the interval ambiguous or create a zero denominator.
For a target exactly equal to one of the known x-values, the correct y-value is the recorded y-value at that point. The basic formula produces that value when the target is one of its endpoints. In a table lookup, an exact interior match also returns the matching y-value with the formula above because the fraction of the interval is zero. Avoid silently replacing missing observations with zero; that changes the data and can create a misleading line.
Interpolation compared with Excel’s forecasting tools
For just two points, the direct formula is transparent and gives the exact straight line through them. Excel’s FORECAST.LINEAR function is designed to predict a y-value from known x- and y-data using linear regression. With exactly two distinct points, a linear regression line passes through both points, so the result at an x-value between them agrees with straight-line interpolation. With more than two points, however, the regression line generally does not pass through every data point.
That difference matters when the table contains several measured observations. Piecewise interpolation uses the adjacent pair around the target and follows the local segment. A regression forecast uses one fitted line across the data set, which can smooth out individual measurements. Neither method is automatically best: use adjacent-point interpolation when the table represents values between measured breakpoints, and consider a regression forecast when the goal is to model an overall linear trend.
A chart’s trendline is not a substitute for the two-point calculation when the request is specifically to estimate between neighboring observations. A plotted line may help reveal the pattern, but the worksheet formula makes the chosen inputs and calculation inspectable. If the relationship is curved, consider whether a domain-appropriate model is needed rather than assuming a straight segment.
Common mistakes and practical fixes
A frequent mistake is reversing the x- and y-values. The formula needs x-values in the positions used to calculate the interval and matching y-values in the positions used to calculate the change. If the input and output columns are swapped, the arithmetic may still return a number, but it will answer a different question. Label columns clearly, such as Time (minutes) and Temperature (°C), before building the formula.
Another error is using the wrong pair of rows in a larger table. If the target is 14 and the table has x-values 10, 12, 16, and 20, the relevant pair is 12 and 16, not 10 and 20. Using distant points can produce a different estimate because the line may have a different slope. Confirm that the selected interval actually brackets the target.
If Excel displays #DIV/0!, inspect whether the two x-values are equal or whether a formula reference points to the wrong cells. If a table formula returns #N/A, the target may be below the first sorted x-value or the lookup range may contain text or unsorted values. A result that looks unreasonable may instead signal a unit mismatch, such as mixing minutes and hours, or an unintended extrapolation beyond the known range.
When interpolation is useful—and when to be cautious
Interpolation is useful in tables where values are supplied only at intervals: conversion charts, calibration readings, rate schedules, and time-series measurements are common examples. It can fill an intermediate value for a chart or calculation without requiring a new measurement. In each case, document the assumption and retain the original observations so readers can distinguish recorded values from estimates.
Use extra caution when the result affects safety, compliance, engineering limits, medical decisions, or a financial commitment. A simple straight line does not account for measurement error, uncertainty, thresholds, or domain rules. The calculation can be correct while the modeling assumption is inappropriate. Check the relevant technical guidance or consult a qualified professional when a decision depends on a reliable value.
Frequently Asked Questions
Does Excel have an INTERPOLATE function?
No. Excel does not provide a built-in function named INTERPOLATE. Use the two-point formula for a single interval, or combine lookup functions with that formula to select the surrounding points in a longer table.
Can I interpolate between two points without a chart?
Yes. A chart is optional. Enter the two x-y pairs and the target x-value in cells, then use the formula directly. This is often easier to audit than estimating a value visually from a plotted line.
Can I use the formula outside the known points?
The arithmetic works outside the interval, but the result is extrapolation, not interpolation. The formula extends the line beyond the data, where the underlying relationship may change. Use such a result only when extending that trend is justified.
What if my x-values are not evenly spaced?
That is fine. The formula uses the actual distance between the two x-values, so evenly spaced points are not required. The table lookup version does require ascending order so it can find the appropriate interval.
Why does my interpolation formula return #DIV/0!?
The denominator is the difference between the two x-values. If they are equal, it is zero. Check that you selected two distinct x-values and that cell references point to the intended rows.
Related
- Excel Zoom: How to Enlarge a Worksheet on Windows PC
- Google Sheets Zoom: How to Enlarge Your Spreadsheet
- Excel Zoom Shortcuts: How to Zoom In and Out Quickly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Plus Years: Formula and Leap-Year RulesTo add years to a date in Excel, use =EDATE(A2,12*B2), where A2 contains the starting date and B2…
- Excel MAX of Two Values: Formula and ExamplesTo find the larger of two values in Excel, use =MAX(A1,B1), replacing A1 and B1 with the cells…
- Excel FLOOR Formula for Time: Round Down by MinutesTo round an Excel time down to the nearest 15 minutes, use =FLOOR.MATH(A2,TIME(0,15,0)), assuming the time is in…
- Excel Week Number: Formulas, ISO Weeks, and Dates ExplainedIn Excel, use WEEKNUM to return a date’s week number; for the internationally standardized Monday-starting system, use ISOWEEKNUM.…
- Excel Custom Number Format: Round Values to MillionsYes. In Excel for Microsoft 365 on Windows, apply the custom number format #,##0,,"M" to display numbers in…
- Nested XLOOKUP Examples for ExcelA nested XLOOKUP places one XLOOKUP inside another, so one search can supply the lookup value, return array,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.