Interpolate in Excel: Does Solver Help?

How-to basics · Updated November 20, 2026

You can interpolate in Excel with a worksheet formula; you do not need Solver for ordinary interpolation. Excel Solver is useful for a different task: finding an input that makes a model reach a target, such as finding the x-value that produces a specified y-value.

What interpolation means

Interpolation estimates a value between known data points. If you have measurements at two or more x-values and want an estimate at an x-value inside that range, interpolation uses the surrounding data to calculate it. It is common in calibration tables, lookup charts, scientific measurements, and other situations where a value falls between recorded observations.

Interpolation is not the same as extrapolation. Interpolation stays within the range of your known x-values; extrapolation estimates beyond that range. The latter relies on an assumption about what happens outside the observed data and can be much less reliable. A spreadsheet formula may calculate either result, but a valid calculation does not establish that the estimate is meaningful.

In the phrase “Excel analytic solver,” there may also be a naming mix-up. Excel’s built-in Solver add-in and the separate Analytic Solver product are optimization and modeling tools, not prerequisites for basic interpolation. For an ordinary straight-line estimate, a standard Excel formula is simpler and easier to audit.

Linear interpolation with a formula

Linear interpolation assumes the relationship between the two surrounding data points is a straight line. Suppose the lower point has coordinates x = 10 and y = 100, and the upper point has coordinates x = 20 and y = 160. The y-value rises by 60 across an x-span of 10, so an x-value of 14 is 40 percent of the way from 10 to 20. The corresponding estimate is 40 percent of the way from 100 to 160, or 124.

With the lower x-value in A2, lower y-value in B2, upper x-value in C2, upper y-value in D2, and the x-value to estimate in F2, enter this formula in G2:

=B2+(D2-B2)*(F2-A2)/(C2-A2)

With F2 set to 14, G2 returns 124. The formula calculates how far the requested x-value lies between the two known x-values, then applies that same fraction to the change in y. It works for increasing or decreasing y-values, provided the two x-values are different and the requested value is between them.

This is a straight-line estimate, not a claim that the real-world relationship is linear everywhere. If the underlying data curve or changes sharply between observations, a straight line may be a poor model even when the arithmetic is correct.

Interpolate manually between a pair of rows

If your table is short, the safest approach is often to identify the two rows that bracket the requested x-value and use the formula above. For example, if a table contains temperature readings at 10 and 20 degrees, use those rows to estimate a value at 14 degrees. Keep the x- and y-values paired: choosing the right x-values but the wrong y-cells will produce a plausible-looking yet incorrect answer.

  1. Put the known x-values in one column and their corresponding y-values in another.
  2. Find the two rows whose x-values sit immediately below and above the value you want to estimate.
  3. Enter those two x-values and y-values in the cells expected by the formula.
  4. Put the target x-value in its input cell and check that the result lies between the two y-values when the data moves steadily in one direction.

That last check is a useful reasonableness test, not a guarantee. If y changes direction between the selected points, or if values are irregular, the estimate can still be mathematically correct under the straight-line assumption without matching the real process.

Interpolate automatically in a sorted table

For many target values, you can find the lower bracketing row automatically. In this example, known x-values are in A2:A6 in strictly ascending order, corresponding y-values are in B2:B6, and the target x-value is in F2. The target must be at least A2 and less than A6. Enter this formula in G2:

=INDEX(B2:B6,MATCH(F2,A2:A6,1))+(INDEX(B2:B6,MATCH(F2,A2:A6,1)+1)-INDEX(B2:B6,MATCH(F2,A2:A6,1)))*(F2-INDEX(A2:A6,MATCH(F2,A2:A6,1)))/(INDEX(A2:A6,MATCH(F2,A2:A6,1)+1)-INDEX(A2:A6,MATCH(F2,A2:A6,1)))

With ascending data, MATCH using 1 finds the position of the largest x-value that is less than or equal to the target. INDEX retrieves that row and the following row, and the formula interpolates between them. An exact match at an interior row returns that row’s y-value. The table must be sorted ascending because approximate MATCH relies on that ordering; unsorted data can return the wrong bracket.

This version does not cover a target greater than or equal to the last x-value, because there is no following row to use. Handle the table endpoints separately, or use a different formula that explicitly checks the input range. Also guard against duplicate x-values: if the two selected x-values are equal, the calculation would divide by zero. In Microsoft 365, XLOOKUP can also find nearby values, but it is not necessary for this method.

What Solver does—and when it helps

Solver works backward from a goal. It changes one or more input cells to maximize, minimize, or reach a chosen value in a formula cell, subject to any constraints you specify. Interpolation usually works in the other direction: you supply an x-value and calculate the corresponding y-value. That is why a worksheet formula is normally the right tool for interpolation.

Solver can be useful when the question is reversed. Reuse the first example’s layout: A2 is 10, B2 is 100, C2 is 20, D2 is 160, F2 is the unknown x-value, and G2 contains the interpolation formula. Set Solver’s objective to G2, choose the option to reach a value of 124, and set F2 as the changing cell. Add constraints requiring F2 to be at least 10 and at most 20. A solution is F2 = 14, because the formula then returns G2 = 124.

For this straight-line model, Solver is more machinery than needed: algebra or a direct calculation can find the same answer. It becomes more useful when the target is calculated by a larger model, multiple inputs can change, or constraints matter. Solver’s result depends on the formulas, constraints, starting values, and solving method; it does not decide whether your model is a sound description of the real process.

Open Solver in Excel for Windows

In Excel for Microsoft 365 on Windows, Solver may need to be enabled first. Go to File > Options > Add-ins. In the Manage box, select Excel Add-ins, choose Go, check Solver Add-in, and select OK. Once enabled, open the Data tab and choose Solver.

In the Solver Parameters dialog, identify the formula cell under Set Objective, choose the target option or a maximum or minimum, and specify the cells Solver may change. Add constraints where the result must stay within a valid range. Then select a solving method appropriate to the model and choose Solve. For the simple linear example above, Simplex LP is suitable for the linear objective and constraints.

On Excel for Mac, Solver is also an add-in, but the route for managing add-ins differs: use Tools > Excel Add-ins and select Solver Add-in if it is available. Solver is not available in Excel for the web in the same way as the desktop add-in; open the workbook in the desktop application to use this workflow. Menu availability can depend on the Excel edition and organizational settings.

Choose a method that matches the data

Linear interpolation is a practical default when you have only two relevant observations or when a straight line is a reasonable local approximation. For a larger table, the automatic formula avoids manually selecting a new pair of rows for every target, but it needs sorted, clean data and explicit handling of endpoints.

Other methods may suit data with a known shape better. A polynomial curve can pass through several points, while a spline uses piecewise curves to avoid some of the extreme swings that a high-degree polynomial can produce. Excel charts can display trendlines, but a chart trendline is not automatically the same as a local interpolation between neighboring table entries. Choose a method based on the process being modeled, not only on which option is easiest to write in a cell.

If the data comes from a published lookup table or a device calibration, check whether its documentation specifies a particular interpolation rule. That rule may be linear, logarithmic, or another method. Using a different method can yield a different estimate even when every spreadsheet formula is correct.

Common errors and checks

  • Using Solver for a direct estimate: Solver is unnecessary when the target x-value is known and you simply need y.
  • Using unsorted x-values: The automatic approximate-match formula assumes ascending order. Sort the paired x and y rows together, never just one column.
  • Extrapolating by accident: Confirm that the target lies within the known x-range. An estimate outside it is extrapolation and should be identified as such.
  • Dividing by zero: The two x-values in a manual pair must differ. Remove or resolve duplicate x-values before calculating.
  • Mixing units or rows: Check that every x-value is paired with its original y-value and that the input uses the same units as the table.
  • Assuming a precise result is a certain result: Excel may display many decimal places, but the estimate is still limited by the data quality and the interpolation assumption.

For important work, test the formula on a known point or a simple example, inspect the selected bracketing rows, and compare the estimate with domain knowledge or the source’s prescribed method.

Frequently Asked Questions

Does Excel have an INTERPOLATE function?
No built-in worksheet function named INTERPOLATE is needed for ordinary linear interpolation. You can calculate it with a formula using the two surrounding data points, or build a lookup formula for a larger sorted table.

Is Analytic Solver the same as Excel Solver?
No. Excel Solver is Microsoft’s add-in for optimization. Analytic Solver is a separate product from Frontline Systems. Neither is required for a basic interpolation formula, and the features available in Analytic Solver depend on the product and license.

Can Solver interpolate between two points?
Solver can search for an input that makes a model return a chosen output. It can therefore solve a reverse interpolation question, but for a simple straight line, a direct formula or algebra is usually clearer.

Can I interpolate with more than two data points?
Yes. For piecewise linear interpolation, use the two neighboring points around each target, even when the full table has many rows. A method that fits one curve through multiple points is a different modeling choice, not the same operation as local linear interpolation.

What is the difference between interpolation and extrapolation?
Interpolation estimates between known x-values. Extrapolation estimates outside the observed range. Extrapolation is often more uncertain because it assumes the observed relationship continues beyond the available data.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.