Yes. Excel can interpolate data, most often with a linear formula between two known values or with the FORECAST.LINEAR function. Excel does not have a general-purpose function named INTERPOLATE, so you choose a method that fits the shape and dimensions of your data.
What interpolation means
Interpolation estimates a value that falls inside the range of known observations. For example, if you know a measurement at 10 seconds and another at 20 seconds, you can estimate the measurement at 15 seconds. The estimate depends on an assumption about what happens between the observations; linear interpolation assumes the value changes at a constant rate over that interval.
That assumption is useful for many practical tasks: estimating a reading between instrument measurements, filling a small gap in a time series, converting between calibration points, or finding a value at an intermediate position on a scale. It does not reveal the true value that was never measured. It gives a calculated estimate based on the input points and chosen method.
Interpolation is different from extrapolation. Interpolation stays between known x-values; extrapolation estimates beyond the smallest or largest known x-value. Extrapolation is generally less dependable because the pattern may change outside the observed range. Excel formulas can calculate either, so it is up to you to check that the target really lies inside the data range.
Linear interpolation with a formula
For two known points, the linear method takes the proportion of the distance to the target x-value and applies the same proportion of the change in y. The points do not need to be evenly spaced. For example, suppose x is elapsed time in minutes and y is temperature in degrees. The known readings are 10 minutes at 20 degrees and 20 minutes at 40 degrees. At 15 minutes, the target is halfway between the x-values, so the estimate is halfway between 20 and 40: 30 degrees.
With the first x-value in A2, the second x-value in A3, their corresponding y-values in B2 and B3, and the target x-value in A4, use this formula:
=B2+(A4-A2)*(B3-B2)/(A3-A2)
For the example, enter 10 in A2, 20 in A3, 20 in B2, 40 in B3, and 15 in A4. The formula returns 30. It works by adding to the first y-value the fraction of the x-interval covered by the target, multiplied by the difference between the y-values. If the target were 12, the result would be 24, because 12 is one fifth of the way from 10 to 20 and 20 plus one fifth of the 20-degree change is 24.
The x-values must be different; otherwise the denominator is zero and Excel returns a division error. The y-values must also be paired with the correct x-values. If you sort one column without sorting the other, the calculation will use mismatched points and produce a misleading result.
Using FORECAST.LINEAR
For a straight-line estimate, Excel also provides FORECAST.LINEAR. It calculates a value using known x- and y-data. With the same layout—x-values in A2:A3, corresponding y-values in B2:B3, and the target x-value in A4—the formula is:
=FORECAST.LINEAR(A4,B2:B3,A2:A3)
It returns 30 for the example above. The argument order matters: the target x-value comes first, then the known y-values, then the known x-values. The function is convenient when you have a series of known points, but it fits a straight line to the supplied data rather than simply selecting the two observations nearest to the target. With only two known points, those approaches give the same line. With several points that do not all lie on one line, the result is a regression-based estimate and may differ from interpolation between the adjacent observations.
That distinction is important. If you want a piecewise linear estimate that follows the local segment between the nearest lower and upper x-values, first identify those two observations and then apply the two-point formula. If instead you want one best-fit straight-line relationship across the entire known series, FORECAST.LINEAR is designed for that purpose. Neither method automatically checks whether the target lies within the known range.
Choosing the right known points
For a small table, it is straightforward to identify the two bracketing observations manually: one x-value must be below the target and the other above it. For a larger table, arrange the x-values in ascending order and make sure each y-value remains on the same row as its x-value. Sorting makes it easier to find the interval containing the target and reduces the risk of choosing points from the wrong part of the series.
Consider readings recorded at 9:00, 9:10, and 9:30, with a requested estimate at 9:20. The relevant interval is 9:10 to 9:30, not 9:00 to 9:30, if your goal is piecewise linear interpolation. The unequal time gaps are not a problem: the formula uses the actual spacing between x-values. Excel stores dates and times as numbers, so the same arithmetic can apply to timestamps, provided the cells contain real Excel date or time values rather than text that only looks like a date.
Before calculating, confirm that the target is bracketed by valid observations and that both endpoints are meaningful. A blank, a text value, a duplicated x-value, or a known reading recorded under the wrong date can undermine the estimate. For measurements with quality flags, exclude or review suspect observations rather than treating every value in the worksheet as equally reliable.
Interpolating dates and missing values
A common use is estimating a value for a missing date between two recorded dates. If a daily measurement is 100 on Monday and 160 on Thursday, straight-line interpolation assigns equal increments across the three day-to-day intervals. Tuesday is estimated at 120 and Wednesday at 140. These figures are estimates, not recovered observations, and they may be unsuitable if the measurement normally changes in jumps or follows a weekly cycle.
Excel does not automatically inspect a column, find every blank, and infer the right interpolation model for it. You need to decide which gaps to fill, which observations bound each gap, and whether a straight line is appropriate. A blank can mean a measurement was missed, but it can also mean not applicable, not yet reported, or intentionally withheld. Filling every blank without understanding its meaning can change the interpretation of the dataset.
For a chart, a line drawn across a gap is only a visual connection; it does not put estimated values into the worksheet. Likewise, changing how a chart displays empty cells does not create a calculated dataset. If later analysis, formulas, or exports need the estimates, calculate and record them in cells, and label them as interpolated so readers can distinguish them from measured values.
More than two dimensions and nonlinear patterns
Interpolation is not limited to one x-variable and one y-variable. In a two-variable table, bilinear interpolation estimates a value inside a rectangle using the four corner values. The process first interpolates along one direction at each of the two edges, then interpolates between those two results in the other direction. For example, suppose the four corners of a grid have values 10 and 20 along the lower edge and 30 and 50 along the upper edge. At the midpoint of both axes, the edge estimates are 15 and 40, and the final midpoint estimate is 27.5. This method assumes a locally linear change in each direction.
Curved relationships require more care. A straight-line estimate between nearby points may be adequate over a short interval, but it can miss a curve or produce an unrealistic result when the data change rapidly. Polynomial interpolation, splines, and other curve-fitting methods can model curvature, but their assumptions and behavior differ. A high-degree curve can swing sharply between observations, so adding complexity does not automatically make an estimate more accurate.
Excel charts can display trendlines, and Excel has statistical and forecasting tools, but a trendline is not the same thing as a guaranteed interpolation through the selected observations. A fitted trendline summarizes a pattern according to its chosen model and may not pass through each data point. If exact passage through points is required, choose an interpolation method that has that property and verify its output rather than assuming a chart line supplies it.
Common errors and limits
The most frequent issue is using a plausible formula with unsuitable data. Linear interpolation assumes a constant rate between endpoints. If the underlying process has thresholds, seasonal behavior, sudden events, or measurement noise, the estimate may be mathematically correct under the formula but poor as a description of reality. Domain knowledge matters as much as spreadsheet syntax.
- Target outside the endpoints: the calculation becomes extrapolation, even if the same formula returns a number.
- Duplicate x-values: the two-point formula divides by zero when both x-values are the same.
- Unsorted or mismatched rows: a wrong pair of endpoints can make the estimate invalid.
- Text instead of numbers: dates imported as text or numeric values stored as text may not behave as expected in calculations.
- Rounding too early: rounding intermediate values can introduce avoidable differences; retain precision in calculations and format the displayed result separately.
A useful check is to calculate the estimate, then compare it with both endpoint values. For a target strictly between two x-values and a simple linear interpolation, the estimated y-value should lie between the endpoint y-values. If it does not, inspect the formula, signs, cell references, and assumptions. This check does not prove that the estimate is scientifically appropriate, but it can reveal common setup mistakes.
How to choose a method
Start by asking what relationship the estimate should represent. Use a two-point linear formula when you want the straight segment between the nearest bracketing observations. Use FORECAST.LINEAR when you want a linear fit based on a group of known observations. Use a specialized interpolation approach for curved or multidimensional data only when the model suits the problem and you can validate its assumptions.
Also consider how the result will be used. For a rough visual estimate in a small table, a transparent formula may be enough. For operational or scientific reporting, retain the original observations, document which values were estimated, record the method, and consider adding a flag or separate column for interpolated values. That preserves traceability and prevents estimates from being mistaken for direct measurements.
Excel is a calculator and data-analysis environment, not a guarantee that the chosen model is right. If the estimates drive an important decision, compare the method against known data by temporarily hiding some observations, estimating them from the remaining data, and measuring the errors. This validation gives a practical indication of how well the method performs on that particular dataset.
Frequently Asked Questions
Does Excel have an INTERPOLATE function?
No. Excel does not include a general-purpose function named INTERPOLATE. You can use a formula for two-point linear interpolation, FORECAST.LINEAR for a linear fit, or another method suited to the data.
Can Excel automatically fill missing values by interpolation?
Not as a general automatic operation that understands which blanks are missing measurements and which model is appropriate. You need to choose the observations and method, calculate estimates, and keep them distinguishable from observed data.
Is FORECAST.LINEAR the same as linear interpolation?
Not always. With two known points, it gives the same straight line as two-point linear interpolation. With multiple points that do not lie on one straight line, it fits a line across the known data rather than following only the local segment around the target.
Can I interpolate between dates in Excel?
Yes, if the dates are real Excel date values and the target date lies between the known dates. Excel represents dates numerically, so a linear formula can use the date spacing. The result remains an estimate and may not reflect seasonal or irregular changes.
What is the difference between interpolation and extrapolation?
Interpolation estimates inside the range of known x-values. Extrapolation estimates outside that range. The same Excel formula can calculate both, but extrapolated results are often less reliable because they extend the assumed pattern beyond the observed data.
Related
- Bank Details Format in Excel: Fields, Setup and Safety
- Basic Cash Flow Template in Excel: Build and Use It
- Autofit Column Width in Excel: Steps, Shortcuts & Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Basic Excel Table With Data: How to Build and Use OneA basic Excel table with data is a rectangular set of related records organized under a single header…
- How to Freeze the First Column in Excel While ScrollingTo keep column A visible while you scroll across an Excel worksheet, open the sheet, select View >…
- How to Insert a Full Line in Word: Borders and ShapesTo insert a full-width horizontal line in Word, put the cursor in the paragraph where the divider should…
- Remove Endnote Formatting in Word Without Losing NotesTo remove formatting from endnotes in Microsoft Word for Microsoft 365 on Windows, select the endnote text, press…
- Wedding Budget Spreadsheet: Free Online Options and SetupYes. You can make a wedding budget spreadsheet online for free in Google Sheets or Excel for the…
- Excel Portfolio Analysis: Track Risk and ReturnsExcel portfolio analysis means organizing holdings and market data so you can calculate what you own, how it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.