Excel Trendline Formula: Find, Read, and Calculate It

Charts & pivot tables · Updated November 21, 2026

An Excel trendline formula is the equation Excel fits to data in a chart; for a linear trendline, it describes the relationship as y equals slope times x plus an intercept. To display it, select the chart and choose Chart Design > Add Chart Element > Trendline > More Trendline Options, then select Display Equation on chart. You can also calculate a linear trendline’s slope, intercept, and predictions directly in worksheet cells.

What an Excel trendline formula tells you

A trendline summarizes a pattern between two variables. In the linear case, the displayed equation is commonly written as y = mx + b: y is the estimated outcome, x is the input, m is the slope, and b is the y-intercept. The chart’s equation is a fitted model, not a formula you can type into a cell unchanged. For example, a chart may show y = 2x + 1, meaning that the model estimates an increase of two units in y for each one-unit increase in x, with an estimated y value of 1 when x is zero.

Excel estimates the line from the selected data, usually by minimizing the squared differences between observed y values and the values predicted by the line. The formula is useful for describing a relationship and estimating values within a reasonable range of the observed data. It does not prove that one variable causes the other, and a good-looking line does not guarantee that the pattern will continue.

Show the equation on an Excel chart

For Microsoft Excel for Microsoft 365 on Windows, create a chart from the two data columns, select the chart, and use Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, choose the trendline type and check Display Equation on chart. You can also check Display R-squared value on chart to show a measure of fit. Selecting the chart and using its chart-elements button may provide a quicker route to the same trendline options.

  1. Put the independent variable, such as time or distance, in one column and the measured outcome in another. Include headers if useful.
  2. Select the data and insert an XY (Scatter) chart when the x values are numerical measurements.
  3. Add a trendline, choose its type, and enable the equation and, if wanted, R-squared display.
  4. Read the equation and confirm that the chosen model makes sense for the data and question.

In Excel for Mac, the chart-design controls are similar, but their placement and wording can differ slightly by release. Select the chart and look under Chart Design for Add Chart Element and Trendline options. Excel for the web has a more limited set of chart editing controls than the desktop app; if the trendline settings or equation display are unavailable in your workbook, open it in desktop Excel.

Choose the right chart and data layout

A trendline depends on how Excel interprets the horizontal axis. An XY (Scatter) chart uses the actual numeric x values, which is important for regression when measurements are unevenly spaced. A Line chart generally treats its horizontal axis as categories. For example, dates or labels may be plotted at evenly spaced category positions even if the time gaps between them differ. If the spacing itself matters, use a scatter chart and provide numeric date or time values.

Arrange paired observations so each x value is on the same row as its corresponding y value. Avoid blank rows, text mixed into numeric observations, and mismatched ranges. A trendline fitted to the wrong x and y columns can still appear on a chart, but its meaning will not match your intended analysis. If the chart has multiple data series, check that the trendline is attached to the correct series.

Calculate a linear trendline in worksheet cells

Suppose x values are in A2:A6 and corresponding y values are in B2:B6, with x values 1, 2, 3, 4, 5 and y values 3, 5, 7, 9, 11. The fitted linear relationship has a slope of 2 and an intercept of 1. With x in A2:A6 and y in B2:B6, enter this formula in a cell to calculate the slope; it returns 2:

=SLOPE(B2:B6,A2:A6)

With the same data layout, enter this formula to calculate the intercept; it returns 1:

=INTERCEPT(B2:B6,A2:A6)

To predict y for a new x value, put 6 in D2. With known x values in A2:A6, known y values in B2:B6, and the new x value in D2, this formula returns 13:

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

The dollar signs keep the known-data ranges fixed if you copy the prediction formula down, while D2 changes to the next input. Excel’s FORECAST.LINEAR function is a shorter alternative. With the same known x and y ranges and the new x value in D2, this formula also returns 13:

=FORECAST.LINEAR(D2,B2:B6,A2:A6)

To predict several x values together, place 6, 7, and 8 in D2:D4. With known y values in B2:B6, known x values in A2:A6, and new x values in D2:D4, this formula returns 13, 15, and 17, spilling the results into adjacent cells in Microsoft 365:

=TREND(B2:B6,A2:A6,D2:D4)

These formulas calculate a linear fit. They will not reproduce a polynomial, exponential, or other nonlinear chart trendline. For a fair comparison, use the same observations and the same model type in both the chart and worksheet calculation.

Understand the equation and R-squared

In a linear chart equation, the coefficient multiplying x is the slope. A positive slope indicates that the fitted y value rises as x increases; a negative slope indicates a decline. The intercept is the model’s estimated y value at x = 0. That value may not be practically meaningful if zero is far outside the observed x range. The chart equation uses an equals sign and variables as mathematical notation, whereas a worksheet formula must begin with an equals sign and refer to cells or functions.

R-squared indicates how much of the observed variation in y is accounted for by the fitted model under the usual regression definition. It ranges from 0 to 1 in many standard chart situations, with values nearer 1 indicating a closer fit to those observations. It is not a measure of whether the model is appropriate, whether the relationship is causal, or how accurate future forecasts will be. A curve with extra flexibility can raise R-squared simply by following the sample more closely.

With known y values in B2:B6 and corresponding x values in A2:A6, this formula returns the coefficient of determination; for the example data on a perfect straight line, it returns 1:

=RSQ(B2:B6,A2:A6)

The equation printed on a chart is often rounded for display. If it shows a coefficient such as 1.98, using that rounded number may produce slightly different predictions from calculations based on the full-precision data. For calculations, use worksheet functions such as SLOPE and INTERCEPT or FORECAST.LINEAR rather than copying rounded chart coefficients.

Compare Excel trendline types

Excel provides several trendline choices in its chart options. Pick one based on the shape and meaning of the relationship, not just on which option produces the highest R-squared.

  • Linear: A straight-line relationship, appropriate when changes in y are roughly constant for equal changes in x.
  • Exponential: A curve that changes by a roughly constant proportion over equal x intervals. Excel’s exponential fit requires positive y values; zeros and negative values make this model unsuitable.
  • Logarithmic: A curve that changes quickly at first and then levels off, often considered when x is positive and the measured pattern has that shape.
  • Power: A relationship in which y changes according to a power of x. This type requires positive x and y values for the standard fit.
  • Polynomial: A curved fit whose order controls its flexibility. Higher orders can bend more, but may overfit a small data set or behave implausibly outside the observed range.
  • Moving average: A smoothed line that averages a selected number of neighboring points. It highlights local movement rather than giving a single equation for predicting y from x.

Some nonlinear trendline types impose data requirements because their fitting methods use transformations that are not defined for certain values. If Excel cannot fit a selected type, check for zero, negative, or nonnumeric values and consider whether that model makes sense. A polynomial or moving-average line may be more appropriate for a particular visualization, but neither should be selected solely to make the chart look smoother.

Common problems and how to fix them

  • The equation looks wrong: Confirm that the trendline belongs to the intended series and that x and y are paired correctly. For numeric spacing, use an XY (Scatter) chart rather than assuming a category axis represents intervals accurately.
  • The prediction differs from the chart: Check whether the chart displays rounded coefficients, whether you selected the same trendline type, and whether the worksheet ranges include exactly the points shown in the chart.
  • The trendline is unavailable or fails: Check the chart type, series, and data values. Certain trendline types cannot be fitted to values such as zero or negative observations.
  • The model predicts extreme values: Avoid trusting a line far beyond the observed x range without a reasoned model. Extrapolation assumes the relationship continues, which the sample alone cannot establish.
  • R-squared is high but the fit is misleading: Plot the residual pattern and inspect the data. Outliers, a curved pattern, or a small sample can make a summary statistic look more reassuring than the model warrants.

When to use a chart equation instead of a cell formula

A chart equation is convenient for explaining a fitted relationship directly on a chart or quickly checking the general direction and scale of a trend. A worksheet formula is better when you need repeatable calculations, full-precision coefficients, predictions for many inputs, or results that update as the source data changes. The displayed equation is a chart label; it does not automatically create a reusable cell formula.

For a single linear prediction, FORECAST.LINEAR is direct and readable. SLOPE and INTERCEPT are helpful when you want to inspect or report the coefficients separately. TREND is useful for calculating fitted values for a set of new x values. These options all implement linear regression; they are not interchangeable with a chart’s nonlinear trendline settings.

Frequently Asked Questions

What is the formula for a linear trendline in Excel?

A linear trendline is described mathematically as y equals mx plus b, where m is the slope and b is the intercept. In a worksheet, calculate those values with SLOPE and INTERCEPT, or use FORECAST.LINEAR to estimate y for a specified x value.

How do I display a trendline equation in Excel?

Select the chart, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, select Display Equation on chart. The equation appears as a chart label and may be rounded.

Can Excel calculate a trendline without a chart?

Yes. For a linear model, SLOPE, INTERCEPT, FORECAST.LINEAR, and TREND calculate the fit or predicted values directly in worksheet cells. You need paired numeric x and y observations arranged in corresponding ranges.

Why does my trendline equation not match my Excel formula?

The chart may round its displayed coefficients, use a different trendline type, or include a different set of data points from your cell calculation. Check the chart series and ranges, and calculate with the same model and full-precision observations for a closer match.

Which Excel chart should I use for a trendline?

Use an XY (Scatter) chart when x represents numeric values and the spacing between them matters. A Line chart treats its horizontal axis as categories in many cases, so uneven numeric intervals may not be represented as actual distances.

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.