Yes, Excel can interpolate between numbers, and the quickest method is to use the FORECAST.LINEAR function. For example, with known x-values in A2:A5 and known y-values in B2:B5, the formula =FORECAST.LINEAR(3.5, B2:B5, A2:A5) returns the estimated y-value for x=3.5. This function performs linear interpolation (and extrapolation) by fitting a straight line through your known points, which is exactly what most people mean by “interpolate between numbers.”
What Interpolation Actually Means in a Spreadsheet
Interpolation is the process of estimating an unknown value that falls between two known values on a line or curve. In Excel, you are typically working with a set of (x, y) data points—for example, temperature readings at specific times, or a yield curve at specific maturities. When you need a value at an x that lies between your sampled points, you cannot just look it up; you must estimate it. Linear interpolation assumes the relationship between consecutive points is a straight line, so the unknown y is proportionally placed between the two surrounding y-values. This is different from extrapolation, which estimates values outside your known range and is far less reliable.
Using FORECAST.LINEAR for Straight-Line Interpolation
The FORECAST.LINEAR function is the modern replacement for the older FORECAST function and is available in Excel 2016 and later, including Microsoft 365. Its syntax is =FORECAST.LINEAR(x, known_ys, known_xs). The first argument is the x-value you want to interpolate for; the second is the range of known y-values; the third is the range of known x-values. The function calculates the least-squares regression line through all your points, not just the two surrounding ones, so it works best when your data is roughly linear overall. For a simple two-point interpolation, it gives the exact same result as the manual proportion formula. For example, with x-values 1, 2, 3 in A2:A4 and y-values 10, 20, 30 in B2:B4, =FORECAST.LINEAR(2.5, B2:B4, A2:A4) returns 25, which is exactly halfway between 20 and 30.
Manual Linear Interpolation Formula (No Built-In Function)
If you want to interpolate strictly between two adjacent points without fitting a line through all data, use the classic proportion formula. Suppose your known lower point is (x1, y1) and upper point is (x2, y2), and you want y at x. The formula is: y = y1 + (x - x1) * (y2 - y1) / (x2 - x1). In Excel, with x1 in A2, y1 in B2, x2 in A3, y2 in B3, and your target x in D2, the formula in E2 would be =B2 + (D2 - A2) * (B3 - B2) / (A3 - A2). This method only uses the two points that bracket your target x, which is ideal for tables where the relationship is not globally linear but is locally linear between each pair of rows. For instance, if (1, 100) and (2, 200) are your points and you want x=1.5, the formula returns 150.
Choosing Between FORECAST.LINEAR and Manual Formula
The choice depends on the shape of your data and what you need. Use FORECAST.LINEAR when your data follows a general trend and you are comfortable with a best-fit line through all points; it is also the only option if you need to extrapolate beyond your data range. Use the manual two-point formula when you have a lookup table with irregular spacing and you want the estimate to follow the exact local slope between adjacent entries. A third option is the TREND function, which is similar to FORECAST.LINEAR but can handle multiple x variables. For purely linear interpolation on a sorted table, you can also combine INDEX and MATCH to find the surrounding rows and then apply the manual formula, but that is more complex than the direct two-point formula.
Common Problems and How to Avoid Them
The most frequent error is using unsorted x-values with the manual formula—if your target x is not between the two points you reference, the formula still runs but gives extrapolated nonsense. Always ensure your x-values are sorted ascending and that you pick the correct bracketing rows. Another issue is mixing up the order of arguments in FORECAST.LINEAR: the known_ys come before known_xs, which trips many users. Also, if your known_xs contain duplicates, the function may return a #DIV/0! error because the regression line cannot be computed. Finally, remember that FORECAST.LINEAR assumes a linear relationship; if your data is curved, the result will be biased. For curved data, consider using polynomial regression via the LINEST function or a spline interpolation add-in, but those are advanced topics.
Interpolation for Non-Linear Data (Splines and Trends)
When your data is clearly curved, linear interpolation between each pair of points still works reasonably if the points are close together, but you can improve accuracy with a cubic spline. Excel does not have a built-in spline function, but you can approximate with the TREND function for polynomial fits or use the GROWTH function for exponential data. For example, if you have exponential growth data, =GROWTH(B2:B5, A2:A5, D2) returns an interpolated value that follows the exponential curve rather than a straight line. For true cubic splines, you would need to write a VBA macro or use a third-party add-in; this is rarely necessary for typical business data.
Frequently Asked Questions
Can I interpolate between two numbers without a formula? Yes, you can use the fill handle for linear series, but that only works for evenly spaced known values. For arbitrary points, you need a formula. The quickest is to type the manual proportion formula directly into a cell.
Does FORECAST.LINEAR work if my x-values are dates? Yes, Excel dates are stored as serial numbers, so you can use them directly. For example, with dates in A2:A5 and values in B2:B5, =FORECAST.LINEAR(DATE(2025,6,15), B2:B5, A2:A5) will return the estimated value for that date. The result is a number, not a date, so format the cell as needed.
What is the difference between interpolation and extrapolation in Excel? Interpolation estimates within the range of your known x-values; extrapolation estimates outside that range. FORECAST.LINEAR does both, but extrapolation is risky because the linear assumption often breaks down beyond your data. The manual two-point formula only interpolates; if you give it an x outside the bracket, it still computes a value but it is extrapolation.
Why does my FORECAST.LINEAR return #N/A? This usually happens when the known_xs and known_ys ranges have different sizes, or when the x-value you are trying to predict is not numeric. Check that both ranges have the same number of rows and that your x argument is a number or a cell reference to a number.
Can I interpolate in a pivot table? No, pivot tables do not support interpolation directly. You would need to copy the pivot data to a regular range and then apply the formulas. Alternatively, use the GETPIVOTDATA function to pull values into a calculation area and interpolate there.
Is there a keyboard shortcut to interpolate? No, interpolation requires a formula. However, you can use the fill handle to create a linear series if you have two known values and want to fill in between them with equal steps—select the two cells, then drag the fill handle down or across.
Related
- Excel: How to Divide Text in Columns
- Excel Subtotals: How to Add Them Step by Step
- Excel Hourly Schedule Template: How to Make One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- PowerPoint Brand Template: How to Build a Reusable DesignCreate a reusable PowerPoint brand template by setting your brand’s colors, fonts, logo, and slide layouts in Slide…
- How to Archive Emails in Outlook Desktop: Step-by-StepTo archive an email in Outlook desktop, select it and choose Home > Archive. Outlook removes the message…
- Power Query Text to Columns: Split Data Step by StepTo split text into separate columns in Power Query, select the column in Power Query Editor, then choose…
- Mail Merge in Word: Step-by-Step InstructionsTo perform a mail merge in Microsoft Word, prepare a recipient list, choose a document type under Mailings…
- Google Sheets COUNTIF With Multiple Criteria ExplainedGoogle Sheets’ COUNTIF function accepts one condition; to count cells that meet multiple conditions at the same time,…
- How to Create a PowerPoint Background Template That WorksTo create a reusable PowerPoint background template, open View > Slide Master, design the top master and its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.