To interpolate in Excel, use a straight-line formula between the two known data points that surround your target value. With x-values in A2:A3, their corresponding y-values in B2:B3, and the target x-value in D2, enter this formula in E2: =B2+(D2-A2)*(B3-B2)/(A3-A2). For example, if the known points are (10, 100) and (20, 160), a target of 16 returns 136. This method estimates a value inside the known range; it assumes the relationship between the points is linear.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or another recent Excel version. The basic formula here also works in Excel for Mac and Excel for the web.
- Two known x-values and their matching y-values. The x-values might be time, distance, temperature, or another measurement; the y-values are the values you want to estimate.
- A target x-value that falls between the known x-values. Keep each x-value paired with the y-value measured at that same point.
- A blank cell for the target and a blank cell for the result. The example below uses D2 and E2.
Step-by-Step
- Set up the known data. In a blank worksheet, enter 10 in A2 and 20 in A3. Enter 100 in B2 and 160 in B3. Treat column A as the independent variable, or x, and column B as the dependent variable, or y. Each row is one known point: A2 goes with B2, and A3 goes with B3. Your own numbers can be in different cells, but keep the pairs together.
- Enter the target x-value. Type 16 in D2. This is the x-value where you want an estimated y-value. For this example, 16 is between 10 and 20, so the calculation is interpolation rather than an estimate beyond the observed data. If your target is not between the two x-values, first check that you chose the correct bracketing points.
- Choose a result cell and enter the formula. Click E2 and type the formula below. The cell layout assumed is x-values in A2:A3, paired y-values in B2:B3, and the target x-value in D2. The formula calculates how far the target lies along the x-interval, then applies that same proportion of the y-change.
=B2+(D2-A2)*(B3-B2)/(A3-A2)
- Press Enter and check the answer. Excel should show 136 in E2. The arithmetic is: the x-distance from 10 to 16 is 6, out of a total x-distance of 10. The y-values rise by 60, so 6/10 of that rise is 36. Add 36 to the starting y-value of 100 to get 136. If Excel displays the formula itself instead of a result, see Troubleshooting below.
- Read the formula from left to right. The starting y-value is B2. The expression D2-A2 is the target’s distance from the first x-value, while A3-A2 is the full x-distance. Dividing those distances gives the target’s fraction of the interval. Multiplying that fraction by B3-B2 gives the corresponding change in y, which is added to B2. This is the standard linear interpolation equation written with cell references.
- Adapt it to your worksheet. Replace A2, A3, B2, B3, and D2 with the cells containing your own two x-values, two matching y-values, and target. For example, if your x-values are in C5:C6, the y-values are in D5:D6, and the target is in F5, update every reference to match those positions. Check that the first x-reference and first y-reference are from the same row, and that the second pair are from the other row.
- Repeat for more target values. Put another target in D3, then copy E2 down to E3. In this example, copying the formula adjusts its references to the next row, so it uses D3 as the new target while retaining the known points in rows 2 and 3. If you want to copy the formula sideways or keep some references fixed while copying, use absolute references such as
$A$2; for a simple vertical fill with fixed known points, edit the formula to use dollar signs around the four known-data references before copying. - Format the result appropriately. The formula returns a number. If your y-values are percentages, select the result cell and choose Home > Number to apply Percentage formatting. For currency or dates, use the matching number format. Formatting changes how a value appears, not the underlying calculation; avoid converting numeric data to text just to add a symbol or label.
Common Mistakes
Using points that do not bracket the target: For ordinary interpolation, choose the two known x-values on either side of the target. If the target is 16, the points at 10 and 20 are suitable. Using points at 10 and 30 may produce a line-based estimate, but it may not represent the local pattern as well if the data curve or changes slope.
Mixing up x and y: The values in A2 and A3 must be the x-values, and B2 and B3 must be the matching y-values. Swapping the columns or pairing values from different rows can produce a plausible-looking but incorrect result. Verify the original observations before diagnosing the formula.
Using identical x-values: The x-values must differ because the formula divides by A3-A2. If both are the same, there is no interval to interpolate across and Excel will return a division-by-zero error. Choose two distinct observations or use a different method suited to the data.
Assuming every relationship is linear: Linear interpolation draws a straight line between two points. It is not a reliable substitute for a curved trend, a step change, or a process with a threshold. The result is an estimate based on an assumption, not a newly observed measurement.
Rounding too early: Keep the source values at their original precision and let Excel calculate with them. Applying a number format to limit displayed decimals is usually better than rounding intermediate data, because early rounding can shift the final estimate.
Tips
Keep a small, clearly labeled table: put x-values in one column, y-values in the next, and targets and estimated results in separate columns. Add headers such as X, Y, Target X, and Estimated Y so that a reader can understand the worksheet without decoding cell references. If values come from a report or instrument, keep the source and units nearby.
Use consistent units throughout. Do not combine minutes with hours, Fahrenheit with Celsius, or distances in meters with distances in kilometers unless you convert one set first. Excel will calculate the numbers exactly as entered, even if the units make the result meaningless.
For targets that fall exactly on one of the known x-values, the formula returns that point’s y-value. In the example, a target of 10 returns 100, and a target of 20 returns 160. That is a useful quick check of the formula’s references and setup.
For several estimates within the same interval, keep the known points in place and enter each target on a separate row. If the worksheet will be reused, label the output as an estimate and record the two source points or the relevant interval. This makes it easier to review the assumptions later.
Dates and times can also be used as x-values because Excel stores them as numbers. Enter them as real Excel dates or times, not text that merely looks like a date. Format the result to display as a date or time if that is what the interpolated y-value represents. If the y-values are dates, confirm that linear interpolation makes sense for the problem before formatting the calculated serial value as a date.
Troubleshooting
The result is #DIV/0!: Check that the two x-values are not identical and that the formula’s denominator refers to the two x-cells. A blank cell may behave like zero in some calculations, so confirm that both x-values are populated with numeric values.
The result is #VALUE! or looks wrong: Check whether any source value was imported as text. A number aligned to the left may be a clue, although alignment can be changed. Re-enter the value as a number or convert the source data to numeric values, then check for stray spaces, units typed into the cell, and incorrect cell references.
The formula appears in the cell instead of a result: The cell may be formatted as Text, or Excel may be set to show formulas. Change the cell format to General, re-enter the formula, and check Formulas > Show Formulas. On Windows, Ctrl+` toggles formula display; on Mac, use Control+` if that key is available on the keyboard, or use the Show Formulas command on the Formulas tab.
The answer is outside the expected range: Confirm that the target is between the two selected x-values, then verify the row pairings and the direction of change in y. A result outside the two y-values can be valid when y decreases or when the input data are unusual, but often signals a mistaken reference, inconsistent units, or a target outside the interval.
Excel rejects the formula: Check that you started with an equals sign and have balanced parentheses. The example uses commas only if a function is included; the interpolation formula itself has no function separators. Some regional settings use a different decimal mark or list separator in formulas that contain functions, so follow the separators used by your Excel installation.
Frequently Asked Questions
What does interpolation mean in Excel?
It means estimating an unknown value between known data points. The formula in this guide uses linear interpolation, which assumes the value changes at a steady rate between the two points.
Can I interpolate between more than two data points?
Yes. For a target between two neighboring x-values in a larger table, use those neighboring rows as the two endpoints in the same formula. Do not automatically use the first and last rows if closer points bracket the target; the local interval is usually the relevant one.
Can I use this formula to extrapolate?
The arithmetic can calculate a value outside the known x-range, but that is extrapolation, not interpolation. It extends the line beyond the observations and can be much less reliable, especially when the underlying relationship changes.
Does the formula work if the y-values go down?
Yes. If the second y-value is lower than the first, B3-B2 is negative and the estimate moves downward along the line. The x-values must still be distinct, and the target should lie between them for interpolation.
Why does my answer have many decimal places?
Excel stores the calculated value at higher precision than it may display. Select the result cell and use Home > Increase Decimal or Decrease Decimal, or choose a suitable number format. This changes the display, not the stored result.
Is linear interpolation the same as forecasting?
No. Interpolation estimates a value within a known range using selected points. Forecasting generally estimates values beyond observed data or uses a model of a trend. A straight-line interpolation result should not be treated as a forecast unless the method and assumptions fit the situation.
Related
- Highlight in Excel Using Ctrl+F: Find and Mark Cells
- How to Highlight in Excel and Keep Gridlines Visible
- Excel Highlight Shortcut: How to Color Cells Quickly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- NPV Calculator in Excel: Formula, Example, and TipsTo calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use…
- Weekday Excel Shortcut: Formulas and Fast Ways to Get ItExcel has no dedicated keyboard shortcut that turns a selected date into its weekday. For a date in…
- Excel MATCH: Find, Filter, and Export Matching DataExcel’s MATCH function does not export data: it finds the position of a value in a range. To…
- Long Invoice Template Excel: Layout and FormulasA long invoice template in Excel is a worksheet designed to handle many billable lines while keeping the…
- Excel Formula for Checking Dates: Validity and RangesIf the date is in A2, use =ISNUMBER(A2) to check whether Excel stores it as a numeric value;…
- XLOOKUP Syntax Examples for ExcelFor an exact lookup, with the value to find in E2, lookup keys in A2:A100, and results in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.