Excel Trendline Values: Show, Calculate, and Use Them

Charts & pivot tables · Updated November 21, 2026

In Excel, trendline values are the estimated y-values produced by a trendline for particular x-values. To see a trendline equation on a chart, select the chart and choose Chart Design > Add Chart Element > Trendline > More Trendline Options, then select Display Equation on chart; to get usable, precise values in worksheet cells, calculate them from the data instead of copying the rounded chart label.

What Excel means by trendline values

The phrase can refer to several related things: the trendline’s equation, its fitted values at the observations in your data, or predicted values at new x-values. Excel draws the trendline over a chart, but it does not automatically put a column of those fitted values beside the source data. The chart equation is a displayed summary of the model, while a worksheet formula is the practical way to calculate and reuse individual estimates.

For example, suppose a chart compares an input such as time or distance with a measured result. The observations are the actual data points; the trendline represents a mathematical pattern fitted to those points. At a given x-value, the trendline’s y-value is the estimate implied by that pattern. It is not necessarily an observed result, and it is not proof that the same pattern will continue in the future.

A trendline value is also different from a forecast in Excel’s Forecast Sheet feature. A trendline can be used to estimate values beyond the observed range if the model and x-values support that use, but a chart’s trendline is primarily a visual and mathematical fit. Treat extrapolated values cautiously, especially when the future may behave differently from the historical data.

Display the equation and R-squared on a chart

In Excel for Microsoft 365 on Windows, create or select a chart, then use Chart Design > Add Chart Element > Trendline and choose a trendline type. To control the model, choose More Trendline Options. In the Format Trendline pane, select Display Equation on chart to show its equation. You can also select Display R-squared value on chart to show a measure of how closely the fitted line matches the plotted data.

The exact options available depend on the chart and trendline type. The equation appears as a chart label and can be moved or formatted like other chart elements. To show more decimal places, select or right-click the equation label, open Format Trendline Label, and adjust its Number formatting where available. Extra displayed digits can make the label easier to read, but the label is still not a substitute for calculations using the original data.

On Mac, the chart design controls and trendline formatting pane are also available, although ribbon placement and pane layout can vary slightly by Excel release. Excel for the web has chart editing capabilities, but some detailed chart formatting controls may be limited compared with the Windows desktop app. If a specific trendline option is missing in the web interface, open the workbook in the desktop version.

  • Select a trendline type that matches the pattern you intend to describe.
  • Use Display Equation on chart to inspect the fitted equation.
  • Use Display R-squared value on chart as a fit statistic, not as proof that the model is appropriate.
  • Keep the source data and chart axes clear so readers can tell what x and y represent.

Calculate linear trendline values in cells

For a linear trendline, the equation has the form y = mx + b, where m is the slope and b is the intercept. Excel’s SLOPE and INTERCEPT functions calculate these coefficients from paired observations. The following example uses five points that fall exactly on the line y = 2x + 1.

With x-values in A2:A6 (1, 2, 3, 4, 5), corresponding y-values in B2:B6 (3, 5, 7, 9, 11), and a new x-value of 6 in A7, enter this formula in B7:

=SLOPE($B$2:$B$6,$A$2:$A$6)*A7+INTERCEPT($B$2:$B$6,$A$2:$A$6)

The result is 13: the slope is 2, the intercept is 1, and 2 × 6 + 1 = 13. The dollar signs keep the source ranges fixed if you copy the formula down, while A7 changes to the x-value on each row. This method calculates the fitted line directly from the underlying observations rather than from the rounded equation printed on a chart.

For values at the original x-values, put the same formula in a new column beside the data and fill it down. With x-values in A2:A6, y-values in B2:B6, and the first predicted value in C2, use:

=SLOPE($B$2:$B$6,$A$2:$A$6)*A2+INTERCEPT($B$2:$B$6,$A$2:$A$6)

Then copy C2 through C6. Those cells contain the linear model’s fitted y-values at each x. They generally will not equal every observed y-value; the differences are residuals, or observed values minus fitted values. In the example above, the points sit exactly on the line, so each fitted value matches its observation.

Use FORECAST.LINEAR for a new x-value

Excel also provides FORECAST.LINEAR to estimate a y-value from a linear relationship. It is useful when you want a prediction at one new x-value without writing the slope-and-intercept calculation yourself. The function uses the known y-values and corresponding known x-values to fit a straight line.

With x-values in A2:A6, y-values in B2:B6, and the new x-value 6 in A7, enter:

=FORECAST.LINEAR(A7,$B$2:$B$6,$A$2:$A$6)

For the example data, the result is 13. FORECAST.LINEAR is intended for linear estimates; it does not reproduce an exponential, logarithmic, polynomial, or other non-linear chart trendline. Make sure the x and y ranges contain the same number of paired observations and that the data is arranged consistently. Blank cells, text, or mismatched pairs can produce unexpected results or errors.

For older Excel workbooks, FORECAST is the legacy function for linear forecasting. In current Microsoft 365 Excel, FORECAST.LINEAR makes the linear purpose explicit. If you need to share a workbook with older versions, check the function compatibility of the intended audience before distributing it.

Choose a trendline type that fits the data

Excel offers multiple trendline models in chart formatting, and the displayed equation changes with the selected type. The best choice depends on the shape of the data and the question being asked, not simply on which curve looks closest to the points.

  • Linear: A straight-line relationship, suitable when changes in y are roughly constant for equal changes in x.
  • Exponential: A curve that grows or declines by a roughly constant percentage. Excel’s exponential trendline requires positive y-values.
  • Logarithmic: A curve that changes quickly at first and then levels out, or the reverse. It requires positive x-values.
  • Polynomial: A curved model that can represent bends in the data. Excel lets you choose the order; higher orders can fit the observed points closely while behaving poorly outside them.
  • Power: A relationship in which y changes as a power of x. It requires positive x- and y-values.
  • Moving average: A smoothing line based on a selected number of adjacent points. It shows local movement rather than a single equation for predicting arbitrary x-values.

These requirements matter when the chart’s equation is missing or the option is unavailable. For example, a zero or negative value cannot be used in the logarithmic transformations behind Excel’s exponential and power trendlines. A model should also make sense for the context: a high-order polynomial may bend dramatically between or beyond observations even when it tracks the plotted points closely.

Understand the equation and R-squared

For a linear trendline, the chart equation typically appears in a form such as y = 2x + 1. The coefficient of x is the slope, and the constant is the intercept. For other model types, the equation uses a different form. You can use the equation to understand the model, but a chart label may round coefficients. Rounding a slope or intercept can noticeably change estimates, particularly when x-values are large or when the value is far beyond the observed range.

R-squared describes how much of the variation in the observed y-values is accounted for by the fitted model under the standard definition used for that model. A larger value can indicate a closer fit to the points, but it does not establish causation, validate the choice of model, or guarantee accurate future estimates. A model can fit historical data well and still be unsuitable for extrapolation. Check the plotted data, residuals, and subject-matter context rather than choosing a trendline only by its R-squared value.

When comparing models, use the same observations and consider whether the added complexity is justified. A polynomial with a higher order can often raise the apparent fit while introducing bends that have no meaningful explanation. A trendline is a simplified description of data, not an automatic discovery of the mechanism that produced it.

Make a worksheet column of values and chart it

If you want the calculated trendline to appear as a distinct series, calculate fitted values in a worksheet column and add that column to the chart. This also makes each value accessible to other formulas, tables, or reports.

  1. Keep the original x-values and observed y-values in separate columns, for example A2:A6 and B2:B6.
  2. Use the linear formula in a third column to calculate a fitted value for each x-value, and fill it down.
  3. Select the chart and use Chart Design > Select Data to add the fitted-value column as a series. Set the x-values and y-values for the new series to the appropriate worksheet ranges.
  4. Format the added series so it is visually distinguishable from the observations, such as a line without markers.

For a numeric horizontal axis, an XY (Scatter) chart is usually the appropriate choice because it positions points according to their actual x-values. A Line chart uses a category axis, where labels are treated as categories and points are spaced evenly. If your x-values are dates, times, or unequal numeric intervals, a category axis can make the spacing look misleading. Select the chart type deliberately before interpreting the trendline or its values.

Common problems and misconceptions

A frequent source of disagreement is comparing a worksheet calculation with the equation printed on the chart. The chart equation is rounded for display, while SLOPE, INTERCEPT, and FORECAST.LINEAR calculate from the data at the workbook’s available precision. Small differences are expected if you manually reproduce a prediction using only the displayed digits. Use the source-data formula when precision matters.

Another common issue is selecting the wrong x-values. If a chart is built from categories, Excel may use sequential positions rather than the numeric labels a reader assumes are the x-values. For example, labels 10, 20, and 100 on a category axis can still be spaced equally. An XY Scatter chart gives those values proportional numeric spacing. Check the chart type and the series’ x-value range before trying to reconcile a chart trendline with a formula.

Also check whether the data contains headers, missing values, text stored as numbers, or mismatched x-y pairs. A trendline is fitted to the points Excel actually recognizes, not necessarily to every visible row in a worksheet. If the trendline seems implausible, inspect the plotted series and source ranges first. Finally, do not interpret a trendline as a guarantee: estimates outside the measured range are extrapolations and can be especially unreliable.

Frequently Asked Questions

How do I show trendline values in Excel?
Select the chart, choose Chart Design > Add Chart Element > Trendline > More Trendline Options, and select Display Equation on chart. That displays the equation, not a worksheet column of fitted values. To get individual values, calculate them in cells using a formula appropriate to the trendline model.

Can Excel put trendline values in cells automatically?
A chart trendline does not automatically create a worksheet column of fitted values. For a linear trendline, use SLOPE and INTERCEPT or FORECAST.LINEAR with the source x- and y-values. For other trendline types, use a calculation that matches that model or another appropriate analysis method.

Why do my calculated values differ from the chart equation?
The equation shown on the chart is rounded to a convenient number of decimal places. Calculations using the original data retain more precision, so predictions can differ. Increase the displayed decimal places to inspect the label, but use worksheet formulas based on the original data for dependable calculations.

What does the trendline R-squared value mean?
It is a measure of how closely the fitted model accounts for variation in the plotted observations. A high R-squared does not show that one variable causes another, prove that the model is correct, or guarantee good predictions beyond the data range.

Why is the trendline option unavailable?
Some trendline types require suitable data, and chart features vary by chart type and Excel version. Check that the chart contains a supported data series, that the required x- or y-values meet the model’s restrictions, and that you are editing the chart in a version with the needed formatting controls.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.