Linear Trendline in Excel: Add, Read, and Use It

Charts & pivot tables · Updated November 21, 2026

To add a linear trendline in Excel for Microsoft 365 on Windows, select a chart, then choose Chart Design > Add Chart Element > Trendline > Linear. A linear trendline summarizes the overall straight-line direction of your data; you can also show its equation and R-squared value to examine the fit.

What a linear trendline shows

A linear trendline is a straight line fitted to data points in a chart. It represents the relationship between an independent variable, usually shown on the horizontal axis, and a measured value on the vertical axis. Excel calculates the line that minimizes the sum of the squared vertical differences between the observed values and the line’s predicted values. This method is called ordinary least squares.

The line is commonly written as y = mx + b. Here, x is the horizontal-axis value, y is the estimated vertical-axis value, m is the slope, and b is the y-intercept. A positive slope means the fitted values rise as x increases; a negative slope means they fall. The intercept is the line’s predicted y-value when x equals zero, which may not have a useful real-world interpretation if zero is outside the data range.

A trendline describes a pattern; it does not establish that one variable causes another. Nor does a straight line mean every point lies close to it. Check the original points and the context of the data before drawing conclusions.

Add a linear trendline to an Excel chart

First, arrange the data in columns, with the x-values in one column and the corresponding y-values in the next. Select both columns, including their headings, and insert a chart. For measurements with meaningful numeric x-values, such as elapsed time or temperature, an XY scatter chart is generally the clearest choice: select Insert > Insert Scatter (X, Y) or Bubble Chart, then choose a scatter option.

  1. Click the chart to reveal the chart tabs.
  2. Choose Chart Design > Add Chart Element > Trendline > Linear.
  3. To show the fitted equation or R-squared value, right-click the trendline and select Format Trendline.
  4. In the Format Trendline pane, select Display Equation on chart and, if useful, Display R-squared value on chart.

You can also select the chart, click the + Chart Elements button beside it, and use the Trendline option. The Chart Design ribbon path is a useful alternative when that button is not visible. To change the line’s appearance, use the formatting controls in the pane. In Excel for Mac, select the chart and use the Chart Design tab to add a trendline; pane labels and their placement may differ slightly by release. Excel for the web has fewer chart-formatting controls, so use the desktop app if you cannot find the equation or R-squared options.

Choose a chart that treats x-values correctly

Chart type matters because the horizontal axis may represent either numbers or categories. An XY scatter chart places points according to their actual numeric x-values. It is usually appropriate when the gaps between x-values have meaning, including irregularly spaced dates. A line chart is often useful for a sequence of categories, but its horizontal axis can space categories evenly even when the dates or numbers behind them are not evenly spaced. In that situation, a trendline may reflect category order rather than the real intervals. Use a scatter chart when the numeric spacing itself matters.

For a time series with regular intervals, either a line chart or a scatter chart may communicate the pattern, but check how the x-axis is being interpreted. If dates are converted to Excel date serial numbers in a scatter chart, the line’s slope is calculated per serial-number unit (a day), not automatically per year. The equation can therefore look unfamiliar. You can still use it for predictions if you use x-values in the same units and understand the scale.

Read the equation and R-squared value

Suppose the trendline equation displayed on a chart is y = 2.2x + 7.4. For each one-unit increase in x, the fitted y-value increases by 2.2 units. At x = 6, the line predicts y = 20.6. These are statements about the fitted line, not a guarantee that a future observation will equal 20.6.

R-squared, also called the coefficient of determination, indicates how much of the observed variation in y is accounted for by the fitted linear relationship in the data. It ranges from 0 to 1 in the usual setting: values closer to 1 indicate a closer fit to the observed y-values, while values closer to 0 indicate a weaker fit. A high R-squared does not prove that the relationship is causal, that the model is suitable for every purpose, or that predictions outside the data range will be reliable. A low value does not automatically mean there is no useful relationship; the pattern might be curved, affected by outliers, or obscured by other factors.

Excel rounds the displayed equation according to its formatting. If you need accurate calculations, do not copy rounded coefficients from a chart label. Calculate the coefficients from the source cells or increase the displayed decimal places, and keep the underlying data and units clear.

Calculate a trendline with worksheet formulas

Worksheet functions are useful when you need coefficients or predictions in cells rather than only a line on a chart. The following example uses x-values 1 through 5 in A2:A6 and corresponding y-values 10, 12, 13, 16, and 19 in B2:B6. The fitted line is y = 2.2x + 7.4.

  • With x-values in A2:A6 and y-values in B2:B6, calculate the slope with =SLOPE(B2:B6,A2:A6). The result is 2.2.
  • With x-values in A2:A6 and y-values in B2:B6, calculate the intercept with =INTERCEPT(B2:B6,A2:A6). The result is 7.4.
  • With x-values in A2:A6 and y-values in B2:B6, calculate R-squared with =RSQ(B2:B6,A2:A6). The result is 0.968.

For this example, the mean y-value is 14 and the sum of squared deviations from that mean is 50. The fitted line’s residual sum of squares is 1.6, leaving an R-squared of 1 − 1.6/50, or 0.968. This confirms the value returned by RSQ. The example makes the arithmetic checkable, but its strong fit should not be treated as evidence about a different dataset.

To make a prediction at a new x-value, put 6 in A7. With the original x-values in A2:A6 and y-values in B2:B6, use =FORECAST.LINEAR(A7,B2:B6,A2:A6). The result is 20.6. FORECAST.LINEAR uses the same straight-line least-squares relationship as the trendline. Its predicted value is an estimate from the fitted line, not a prediction interval or a measure of uncertainty.

Prepare data and decide whether a straight line fits

Before adding a trendline, confirm that each x-value is paired with the correct y-value and that both columns contain usable numeric values. Check units, date formats, duplicate measurements, blanks, and obvious data-entry errors. A scatter chart is especially helpful because it exposes the shape of the points before a model is added.

Look for curvature, clusters, sudden changes, or individual points far from the rest. A straight line may be a reasonable summary when the relationship changes at a roughly constant rate over the range being studied. If the pattern bends, one overall linear line can conceal important behavior. Do not remove an outlier simply because it makes the line look less tidy: first determine whether it is an error, a special case, or a valid observation that needs explanation.

Also distinguish a trendline from connecting the data points. A line chart can connect observations in order, producing a series of segments; a linear trendline is a separate fitted summary. The two lines answer different visual questions. The connected series shows observed values and their sequence, while the trendline emphasizes a general linear direction.

Common problems and how to fix them

  • The option is unavailable or greyed out: Check that a chart is selected and that its type supports trendlines. Switch to an XY scatter chart if needed. Trendlines are not available for every chart type or every kind of plotted series.
  • The equation does not match a hand calculation: Make sure the chart is using the intended series and x-values. Check whether the displayed equation has been rounded, and compare it with SLOPE and INTERCEPT results from the same data.
  • The prediction looks wildly wrong: Confirm the x-value’s units. A date serial, a year number, and a month count are different scales. Use the same x-variable and units for the prediction that were used to fit the line.
  • The line seems to ignore uneven date gaps: Use an XY scatter chart with actual numeric dates or elapsed times if the intervals matter. A category axis can treat labels as equally spaced.
  • The trendline looks convincing but forecasts fail: Check whether the prediction is extrapolating beyond the observed range and whether the relationship is likely to continue. A fitted line describes the supplied data; it cannot account for a future change in conditions.

Linear trendlines versus other Excel options

A linear trendline is easy to interpret and useful for a roughly constant rate of change. Excel also offers other trendline types through the Trendline options in the Format Trendline pane, including exponential, logarithmic, polynomial, and moving average. These are not interchangeable decorations: each makes different assumptions about the pattern. For example, a polynomial can follow curvature, but a high-order curve may fit the existing points closely while behaving erratically outside them. Choose a model based on the data and the question, not just on which line looks closest.

If you need cell-based results, SLOPE, INTERCEPT, RSQ, and FORECAST.LINEAR can support a straightforward linear analysis. A chart trendline is convenient for visual exploration; worksheet functions make the calculation reusable in a table or report. Neither approach replaces checking residuals, considering the data-generation process, or communicating uncertainty when a decision depends on the forecast.

Frequently Asked Questions

How do I add a linear trendline in Excel?
Select the chart, then choose Chart Design > Add Chart Element > Trendline > Linear. To display the equation, right-click the trendline, select Format Trendline, and turn on the equation option.

How do I show the trendline equation in Excel?
Right-click the trendline and select Format Trendline. In the pane, select Display Equation on chart. The chart label may round its coefficients, so use SLOPE and INTERCEPT in cells when you need more precise values.

What does the R-squared value mean on an Excel trendline?
It summarizes how closely the fitted line accounts for variation in the y-values in the dataset. A value near 1 indicates a close linear fit to those observations, but it does not prove causation or guarantee accurate forecasts beyond the observed range.

Can I use an Excel trendline to predict future values?
Yes, a fitted line can be extended or used with FORECAST.LINEAR to estimate a value for a specified x. Treat predictions beyond the observed x-range cautiously: the relationship may not continue, and the function does not provide a prediction interval.

Why is my Excel trendline different from the line connecting my points?
The connecting line joins observations in sequence. A trendline is calculated to summarize their overall relationship, so it generally does not pass through every point. In addition, a category axis may space labels evenly; use an XY scatter chart when the actual numeric spacing between x-values matters.

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.