Yes. To calculate a linear trendline estimate in an Excel cell, use FORECAST.LINEAR or calculate the slope and intercept yourself. With x-values in A2:A6, corresponding y-values in B2:B6, and the x-value to predict in D2, enter =FORECAST.LINEAR(D2,$B$2:$B$6,$A$2:$A$6). The result is the predicted y-value; it does not automatically link to a chart’s displayed trendline equation.
What a trendline formula in a cell does
A trendline summarizes the relationship between known x-values and y-values, then uses that relationship to estimate a y-value for a supplied x. In Excel, a chart trendline is a visual chart feature, while a worksheet formula is a calculation you can reuse in cells, reports, or other formulas. They can represent the same model, but the chart’s equation label is not itself a cell formula.
For a straight-line model, the equation is y = mx + b. Here, m is the slope, or the estimated change in y for each one-unit increase in x, and b is the y-intercept, the model’s predicted y when x is zero. If the data is strongly curved, a straight-line estimate may not describe it well; Excel also offers other trendline models.
For a concrete check, suppose A2:A6 contains 1, 2, 3, 4, 5 and B2:B6 contains 3, 5, 7, 9, 11. The points fall exactly on y = 2x + 1. For an input x of 6, a linear trendline predicts 13. This simple example is useful for checking that a formula is arranged correctly, but real data rarely follows a perfect line.
Calculate a linear trendline with SLOPE and INTERCEPT
Use SLOPE to calculate m and INTERCEPT to calculate b. With x-values in A2:A6 and y-values in B2:B6, enter this in an empty cell to return the slope:
=SLOPE($B$2:$B$6,$A$2:$A$6)
For the example data, the result is 2. With the same ranges, this formula returns the intercept:
=INTERCEPT($B$2:$B$6,$A$2:$A$6)
Its result is 1. The order matters: both functions take the y-values first and the x-values second. The dollar signs make the data ranges absolute references, so they remain fixed if you copy the formula to other cells.
To make a prediction directly, with the same data layout and the new x-value in D2, enter:
=SLOPE($B$2:$B$6,$A$2:$A$6)*D2+INTERCEPT($B$2:$B$6,$A$2:$A$6)
If D2 contains 6, this returns 13. This version makes the line equation visible in the formula and is handy when you also want the slope and intercept in separate cells. For a concise prediction formula, use FORECAST.LINEAR instead. With x-values in A2:A6, y-values in B2:B6, and the prediction input in D2, it returns the same result:
=FORECAST.LINEAR(D2,$B$2:$B$6,$A$2:$A$6)
Use the equation shown on a chart
If you have already added a trendline to a chart, Excel can show its equation on the chart. In Excel for Microsoft 365 on Windows, select the chart, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, select Display Equation on chart. You can also select Display R-squared value on chart to show a measure of how closely the trendline fits the plotted data.
The equation label is useful for inspecting a model, but it is not a dependable substitute for a worksheet calculation. Excel displays rounded coefficients, and a prediction based on those rounded numbers can differ slightly from one calculated from the full-precision data. To change how many digits appear, format the equation label in the chart’s Format Trendline pane; more displayed decimal places can reduce visible rounding but do not turn the label into a live formula cell.
For a scatter chart with numeric x-values, the chart’s linear trendline and worksheet regression functions should correspond when they use the same data and model. A Line chart, by contrast, commonly treats horizontal positions as categories. If the spacing between x-values matters—for example, measurements at days 1, 2, and 10—use an XY (Scatter) chart so Excel treats those positions as numeric distances.
Choose the formula for the trendline type
FORECAST.LINEAR, SLOPE, and INTERCEPT calculate a straight-line model. If the chart uses a different trendline type, a linear worksheet formula will not reproduce it. Excel’s trendline choices include exponential, logarithmic, power, and polynomial models, each with different assumptions and calculations.
- Linear: Use
FORECAST.LINEARfor a direct prediction, or combineSLOPEandINTERCEPTwhen you need the coefficients separately. - Exponential: With positive y-values,
GROWTHestimates values using an exponential model. With x-values in A2:A6, positive y-values in B2:B6, and a new x in D2, enter=GROWTH($B$2:$B$6,$A$2:$A$6,D2). It returns a model-based estimate, not a linear one. - Polynomial: A polynomial uses powers such as x and x². You can create helper columns for those powers and use
LINESTto estimate coefficients. A higher degree can follow the observed points more closely but may behave poorly outside the measured range. - Logarithmic or power: These models involve transformed variables and have domain restrictions. Check that the data is suitable for the chosen model before using a transformed calculation as a forecast.
For example, GROWTH is not interchangeable with a chart’s linear trendline, even if both happen to give similar estimates for a short section of data. Choose the worksheet function based on the model you intend to use, not just on which output looks closest to one particular point.
Build a polynomial calculation with helper columns
For a quadratic model, make helper columns for x and x², then give both columns to LINEST. Assume x-values are in A2:A6 and y-values in B2:B6. In C2 enter =A2 and fill down; in D2 enter =A2^2 and fill down. The columns supplied to LINEST are then C2:D6. In Excel for Microsoft 365, enter this in an empty cell with room for the results to spill to the right:
=LINEST(B2:B6,C2:D6,TRUE,FALSE)
The returned coefficients are ordered from the last x-variable column back to the first, followed by the intercept: the coefficient for x², the coefficient for x, then the constant term. For the example’s perfectly straight data, those coefficients are 0, 2, and 1, respectively. The formula estimates coefficients; to evaluate the resulting quadratic for a new input, use the coefficients in a separate prediction formula. Older Excel versions may require selecting the output range and confirming a multi-cell array formula with Ctrl+Shift+Enter; Microsoft 365 handles the returned array dynamically.
Check the data and interpret the result
A correct formula can still produce an unhelpful forecast if its input data or assumptions are wrong. Before relying on the result, check these points:
- Each x-value must correspond to the y-value on the same row. Sorting one column without the other breaks the pairs.
- Use numeric values for x and y. Text that looks like a number, blank cells, or error values can interfere with the calculation or produce unexpected results.
- Keep the x-range and y-range the same length. For time data, Excel dates are stored as serial numbers, so a date column can serve as numeric x-values if the paired observations are aligned.
- Look at a scatter chart of the data. A high or low R-squared value can describe fit, but neither proves that the relationship is meaningful or that extrapolating beyond the observed x-values is safe.
- Be cautious about predicting far outside the measured range. A line may fit the known observations and still fail when extended into a different period or situation.
For the example above, you can check the fit using R-squared. With y-values in B2:B6 and x-values in A2:A6, enter =RSQ(B2:B6,A2:A6). It returns 1 because every example point lies exactly on the same line. Actual datasets usually return a value below 1. R-squared describes variation accounted for by the fitted linear relationship; it does not show that the model is appropriate for every use or guarantee a future result.
Common problems and useful distinctions
A frequent mistake is swapping the x and y arguments. Excel’s chart may still show a line when the axes are reversed, but the slope and prediction have a different meaning. Think of x as the known input and y as the quantity being estimated; use that same interpretation in the chart and formulas.
Another common source of discrepancies is comparing a cell result with a rounded chart equation. Use the underlying data for worksheet calculations, and compare values at the same x using enough displayed precision. Also verify that the chart trendline uses the same observations, excludes the same points, and has the same type as the formula. If one row is omitted from the chart or a trendline is configured to forecast forward, the visible chart and cell calculation will naturally differ.
Excel for Microsoft 365 on Windows provides the formulas described here. On Mac, the worksheet functions use the same syntax, while chart controls can be arranged differently in the ribbon or Format pane. Excel for the web supports many common worksheet formulas, but chart editing and trendline controls can differ from desktop Excel; if you cannot find a particular equation-display option, open the workbook in the desktop app.
Frequently Asked Questions
Can Excel put a chart’s trendline equation directly into a cell?
No built-in chart option writes the displayed equation into a worksheet cell as a linked formula. For a linear trendline, calculate the coefficients with SLOPE and INTERCEPT, or make a prediction with FORECAST.LINEAR. This also avoids relying on rounded chart-label coefficients.
What is the simplest formula for a linear trendline estimate?
Use FORECAST.LINEAR(new_x, known_y, known_x). Put the value to predict first, then the y-values and matching x-values. For example, with the new x in D2, y-values in B2:B6, and x-values in A2:A6, use =FORECAST.LINEAR(D2,$B$2:$B$6,$A$2:$A$6).
Why does my formula not match the equation on the chart?
Common reasons include rounded equation coefficients, different trendline types, different included data points, or a chart that treats x-values as categories. Confirm the ranges and model, use an XY (Scatter) chart for numeric x-values, and avoid calculating from the rounded label.
Can I use a trendline formula to predict future values?
Yes, formulas can estimate values at x-values beyond the observed data, but that is extrapolation. The result is only as useful as the model and its assumptions; a line that fits historical observations may not continue into the future. Compare predictions with subject-matter context and other evidence.
How do I calculate a trendline’s slope only?
With y-values in B2:B6 and x-values in A2:A6, use =SLOPE(B2:B6,A2:A6). The result is the estimated change in y for a one-unit increase in x. Its units depend on the units of the two columns, so it is not necessarily a percentage.
Related
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns and Rows: How to Split Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Make an Excel Dashboard with AI: A Step-by-Step GuideYes. You can make an Excel dashboard with AI by using an assistant to plan the layout, suggest…
- Pareto Chart in Excel: How to Create and Read the ResultsIn Excel for Microsoft 365 on Windows, select your category labels and counts, then choose Insert > Insert…
- How to Zoom In on a Scatter Plot in Excel: Axis ScalingTo zoom in on a scatter plot in Excel, change the horizontal (X) and vertical (Y) axis bounds…
- Excel Trendline Data Labels: Add, Format, and Fix ThemIn Excel for Microsoft 365 on Windows, select the chart, choose Chart Design > Add Chart Element >…
- Create a Balloon Chart in Excel: A Practical GuideTo create a balloon chart in Excel, arrange your data in columns for X values, Y values, and…
- Half Circle KPI Chart in Excel: Build It Step by StepTo create a half-circle KPI chart in Excel, enter the KPI value, the amount remaining to its target,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.