Excel Trendline Formula: How to Show and Use It in Charts

Charts & pivot tables · Updated November 21, 2026

To show a trendline formula in an Excel chart, select the chart, choose Chart Design > Add Chart Element > Trendline > More Trendline Options, then select Display Equation on chart in the Format Trendline pane. Excel adds the fitted equation as a chart label; to calculate predictions in worksheet cells, use functions such as SLOPE and INTERCEPT for a linear trend.

What an Excel trendline equation means

A trendline is a mathematical model fitted to data in a chart. Its equation describes the overall relationship Excel estimates between the series’ X values and Y values. It can help summarize a pattern or estimate a value between or beyond observed points, although estimates outside the data range are extrapolations and may be unreliable.

For a linear trendline, Excel displays an equation in the form y = mx + b. Here, m is the slope: the estimated change in Y for a one-unit increase in X. The value b is the Y-intercept, or the model’s predicted Y when X equals zero. If a chart displays y = 2x + 1, the line rises by two Y units for each one-unit increase in X, and its predicted Y at X = 0 is 1.

The equation belongs to the selected chart series. It is not automatically inserted into a worksheet cell, and it does not change the original data. Excel fits the line from the series’ values and draws it on the chart; the equation label simply reports the model’s coefficients.

How to add the equation to a chart

  1. Arrange the X values and corresponding Y values in two columns, with each X value on the same row as its Y value.
  2. Create or select an appropriate chart. For numeric X values, an XY (Scatter) chart is usually the clearest choice.
  3. Select the data series in the chart. If the chart has multiple series, make sure you select the one whose trendline you want.
  4. On the ribbon, choose Chart Design > Add Chart Element > Trendline > More Trendline Options.
  5. In the Format Trendline pane, select the trendline type, then check Display Equation on chart. If useful, also check Display R-squared value on chart.

The equation appears inside the chart area as a text label. Drag it to a clearer position if it covers data, and resize the chart if the equation is difficult to read. Selecting the trendline or its label lets you format its appearance. The steps above describe Excel for Microsoft 365 on Windows. Mac versions provide chart trendline options through the chart-formatting controls as well, though ribbon layout can differ. Excel for the web may offer fewer chart-formatting controls; if an option is unavailable, open the workbook in the desktop app.

Choose the right chart and data layout

A trendline can only be meaningful if Excel interprets the data as intended. An XY (Scatter) chart treats the horizontal values as numeric measurements. A line chart, by contrast, normally places categories at evenly spaced positions on the horizontal axis. If dates or numbers are being treated as labels rather than measured X values, a line chart’s equation may not represent the relationship you expected.

To make an XY chart, select the two data columns and choose Insert > Insert Scatter (X, Y) or Bubble Chart, then select a scatter style. Confirm that the horizontal axis uses the intended X column and that the plotted series uses the matching Y column. For charts with several series, check each series’ X and Y ranges before adding a trendline.

Excel supports trendlines on many two-dimensional chart types, but not every chart type is suitable or supports every trendline feature. Stacked charts and three-dimensional charts are common cases where trendline use is restricted. For regression-style analysis, an XY scatter chart is generally a safer choice than a stacked or 3-D chart because its numeric axes are explicit.

Calculate a linear trend in worksheet cells

A chart equation is convenient for reading, but its displayed coefficients may be rounded. If you need a reusable calculation, use worksheet functions that refer directly to the data. For a concrete example, assume X values 1, 2, 3, 4, and 5 are in A2:A6, and their matching Y values 3, 5, 7, 9, and 11 are in B2:B6. These points follow the exact line y = 2x + 1.

With X values in A2:A6 and corresponding Y values in B2:B6, enter this formula to calculate the slope; it returns 2:

=SLOPE(B2:B6,A2:A6)

With X values in A2:A6 and corresponding Y values in B2:B6, enter this formula to calculate the intercept; it returns 1:

=INTERCEPT(B2:B6,A2:A6)

Those results reproduce the chart’s linear equation: y = 2x + 1. To calculate a fitted Y value for an X value stored in A2, use this formula, with the original X and Y data still in A2:A6 and B2:B6; it returns 3 when A2 contains 1:

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

When you copy that formula down beside additional X values, the dollar signs keep the source data ranges fixed while the reference to A2 adjusts by row. Do not include headers or blank rows in the ranges. For a prediction at an X value outside the observed range of 1 to 5, the formula still calculates a number, but the result is an extrapolation—not evidence that the same pattern will continue.

Select a trendline type that fits the pattern

Excel offers several trendline models. Choose based on the shape and subject of the data, not just on which option produces the highest fit statistic:

  • Linear: A straight-line model for a roughly constant change in Y as X changes.
  • Exponential: A curved model for a pattern that grows or declines at a rate related to its current level. It is not suitable when the Y data include zero or negative values.
  • Logarithmic: A model that changes quickly at first and then levels off, or the reverse. Its X values must be positive.
  • Power: A curved relationship in which Y varies with a power of X. The data need positive X and Y values for this model.
  • Polynomial: A curved model that can represent bends in the data. Excel lets you set its order, up to six; a higher order can follow the points closely but may overfit.
  • Moving average: A smoothed line based on averages of neighboring observations. It is useful for highlighting short-term fluctuations in an ordered series, but it is not a single fitted equation in the same sense as a regression trendline.

For exponential, logarithmic, power, and polynomial models, the displayed equation has a different form from y = mx + b. Do not interpret their coefficients as a linear slope and intercept. The equation label and any R-squared value refer to the selected model and series, so changing the trendline type changes what those values mean.

Read the equation and R-squared carefully

The chart equation is rounded for display. If Excel shows a coefficient such as 1.9, the underlying fitted value may have more decimal places. A rounded label can therefore produce slightly different predictions from the trendline itself. For more precise calculations, use worksheet functions or increase the number of displayed decimal places by formatting the equation label.

R-squared, often written R², is a measure of how closely a model’s fitted values match the observed data under the regression calculation. A value closer to 1 generally indicates a closer fit to those points, but it does not prove that the model is appropriate, explain why the relationship exists, or guarantee accurate future predictions. A high-order polynomial can achieve a high R² while behaving implausibly outside the measured range.

Look at the scatter of points as well as the statistic. A trendline can hide clusters, outliers, changing variance, or a curved pattern that a straight line misses. If one unusual point changes the fitted line substantially, investigate the data and its context before using the equation. Do not remove a valid observation merely to make the chart look better.

Common problems and practical fixes

  • The equation option is missing: Confirm that a supported chart and data series are selected, then open More Trendline Options. Some chart types and trendline types do not offer the same equation display controls.
  • The equation looks wrong: Check which column Excel uses for X and which it uses for Y. An XY scatter chart makes that assignment more explicit than a chart with category labels.
  • The equation has scientific notation: The chart label may display very small or large coefficients compactly. Format the label for more decimal places, or calculate coefficients in worksheet cells for clearer precision.
  • The line does not pass through every point: A trendline is usually a best-fit model, not a line connecting each observation. Small residual differences are expected unless all data points follow the model exactly.
  • The curved line looks convincing but predicts badly: Check the number of polynomial terms and avoid relying on distant extrapolations. A visually close fit to a small sample can still be a poor model.

Also check for blanks, text stored as numbers, and mismatched row pairs. If a Y value is accidentally paired with the wrong X value, Excel can calculate a valid equation for incorrect data. Keeping labels out of the selected numeric ranges and verifying the plotted points before interpreting the equation prevents many avoidable errors.

Frequently Asked Questions

How do I show the trendline equation in Excel?
Select the chart, choose Chart Design > Add Chart Element > Trendline > More Trendline Options, and check Display Equation on chart in the Format Trendline pane.

Can I use a trendline equation in a worksheet formula?
Yes. For a linear model, calculate the coefficients with SLOPE and INTERCEPT, then use them in a cell formula. This avoids relying on the rounded coefficients printed in the chart label.

Why does my chart equation differ from my calculation?
The chart may round its displayed coefficients, or the chart and worksheet formula may use different X and Y ranges. Check the ranges first, then increase the equation label’s displayed precision.

What does R-squared mean on an Excel trendline?
R-squared summarizes how closely the fitted model matches the observed data. It is not proof of causation or a guarantee that the model will predict future values accurately.

Can I add a trendline to a line chart?
Excel supports trendlines on many two-dimensional chart types, including some line charts. If the horizontal values are numeric and their spacing matters, use an XY scatter chart so Excel treats them as measured X values.

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.