Excel Scatter Plot Linear Regression: How to Add a Trendline

Charts & pivot tables · Updated November 21, 2026

To show a linear regression on an Excel scatter plot, select your numeric X and Y data, choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter, then select the chart and choose Chart Design > Add Chart Element > Trendline > Linear. To show the fitted equation and R-squared value on the chart, open the trendline options and select Display Equation on chart and Display R-squared value on chart.

What a scatter plot and linear regression show

A scatter plot places each paired observation at a position determined by its X value and Y value. For example, a business might plot advertising spend on the horizontal axis and weekly sales on the vertical axis. Each dot represents one week. A linear regression fits a straight line to the points, summarizing the average relationship between the two variables.

The line is usually written as y = mx + b. Here, m is the slope, or the estimated change in Y for a one-unit increase in X, and b is the intercept, or the estimated Y value when X is zero. In Excel, adding a linear trendline to an XY scatter chart is a quick way to visualize that fitted line. The chart does not, by itself, establish that X caused a change in Y.

Arrange the data correctly

Put each matched observation on the same row, with one numeric variable in each column. For instance, enter values of X in A1 and down, and the corresponding values of Y in B1 and down. Include clear headers, such as Hours studied and Test score. Avoid merged cells, blank rows inside the data, and totals mixed in with observations.

An XY scatter chart is important because Excel treats the horizontal values as numbers on a numeric axis. A line chart, in contrast, commonly spaces categories evenly, even when the underlying X values are unequal. If the observations are at days 1, 2, and 10, a scatter plot preserves those distances; a category-based chart may not.

  • Use one X value and one corresponding Y value per row.
  • Check that numbers are stored as numbers, not text. Text-aligned values or imported numbers with unexpected decimal separators can cause problems.
  • Decide how to handle missing observations. Do not pair a Y value with the wrong X value just to fill a gap.
  • Keep unusual values in the data unless you have a documented reason to exclude them; an outlier can substantially affect a fitted line.

Create the scatter plot and add a linear trendline

In Excel for Microsoft 365 on Windows, select the two data columns, including their headers, then use Insert > Insert Scatter (X, Y) or Bubble Chart and choose a scatter subtype, usually Scatter with only Markers. If Excel assigns the wrong X or Y range, right-click the chart, choose Select Data, select the series, and choose Edit to set the correct Series X values and Series Y values.

Click a plotted point to select the data series. Then choose Chart Design > Add Chart Element > Trendline > Linear. Another route is to right-click the selected series and choose Add Trendline, then select Linear in the Format Trendline pane. The line is a fitted straight line, not a line connecting each dot in row order.

To display the equation and fit statistic, open Format Trendline and check Display Equation on chart and Display R-squared value on chart. Add axis titles through Chart Design > Add Chart Element > Axis Titles, and give the chart a descriptive title. Those labels make the graph easier to interpret, especially when the units are not obvious. Excel for Mac has comparable chart and trendline controls, though ribbon placement can differ slightly by release. Excel for the web offers chart editing, but some advanced analysis and formatting controls are more limited than in desktop Excel.

Read the equation and R-squared carefully

Suppose the chart displays y = 0.8x + 2.6. The slope says the fitted Y value rises by 0.8 units for each one-unit increase in X, on average. The intercept says the line crosses the Y axis at 2.6. That intercept is meaningful only if X = 0 is relevant to the data and the fitted relationship is reasonable there; it may be an extrapolation otherwise.

R-squared, often written R², describes how much of the observed variation in Y is accounted for by the fitted linear relationship in the data used to fit the line. It ranges from 0 to 1 in ordinary simple linear regression with an intercept. An R² of 0.64 means 64% of the variation in the observed Y values is accounted for by the fitted line in that sample. It does not mean the model is 64% accurate, that predictions are correct 64% of the time, or that X causes Y.

Excel rounds the equation printed on the chart for display. A displayed slope such as 0.8 may be rounded, so do not copy it into a calculation where full precision matters. Use worksheet functions or increase the displayed number of decimal places in the trendline label. Also inspect the actual pattern of dots: a high R² can coexist with a curved pattern, influential outliers, or poor predictions outside the observed X range.

Calculate the regression in worksheet cells

Worksheet functions are useful when you need reproducible values, predictions, or a report that updates when the source data changes. Consider this small illustrative data set, with X values 1, 2, 3, 4, 5 in A2:A6 and corresponding Y values 3, 5, 4, 7, 6 in B2:B6. Its least-squares line is y = 0.8x + 2.6, and its R² is 0.64.

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

=SLOPE(B2:B6,A2:A6)

With the same ranges, calculate the intercept; it returns 2.6:

=INTERCEPT(B2:B6,A2:A6)

With the same ranges, calculate the coefficient of determination for the linear fit; it returns 0.64:

=RSQ(B2:B6,A2:A6)

To calculate fitted values, suppose E2 contains the slope 0.8, E3 contains the intercept 2.6, and the X value for the first observation is in A2. Enter this in C2 and fill down; for A2 = 1, it returns 3.4:

=$E$2*A2+$E$3

That prediction is the line’s fitted value, not necessarily the observed Y value. For example, the first observed Y is 3, so its residual (observed minus fitted) is -0.4. Residuals help reveal whether the straight-line model misses a pattern: plotting residuals against X should generally show scatter around zero rather than a clear curve or funnel shape.

Check whether a straight line is appropriate

Before relying on the trendline, look at the dots without the line. A straight-line model is most plausible when the relationship is roughly linear across the range, observations are reasonably independent, and the spread of residuals is not changing dramatically with X. For formal statistical inference, assumptions about the residual distribution and sampling process also matter. A chart alone cannot verify all of these conditions.

Pay particular attention to influential points. A single point far from the others can tilt the line and change both its slope and R². Compare the fit with and without a questionable point only as a diagnostic, not as a reason to discard data automatically. Record any exclusion criteria and investigate measurement, data-entry, or population differences.

A regression line can also produce implausible predictions when extended beyond the observed data. If a relationship was measured only between X = 1 and X = 5, predictions at X = 100 are extrapolations and may not be useful. Report the range of the original observations alongside any model-based prediction.

Common chart and regression problems

  • The line is missing: Confirm that the data series, rather than the chart area, is selected before adding the trendline. Check that the chosen trendline type is Linear.
  • The X-axis looks like categories: Re-create the chart as an XY scatter chart, not a line chart. Verify that the X series range contains the intended numeric values.
  • The equation looks wrong: Check whether Excel has assigned the columns in the correct order. The first range should be X and the second Y for worksheet functions such as SLOPE and INTERCEPT.
  • The chart equation differs slightly from a cell result: The chart label may be rounded. Increase its decimal places or use SLOPE and INTERCEPT for calculations.
  • R² is unexpectedly low or high: Inspect the point pattern, data types, outliers, and whether X and Y rows remain correctly paired. R² alone is not a measure of whether a model is suitable.

Use the Regression tool for a fuller report

For more than a visual line, Excel’s Analysis ToolPak can produce a regression output table with statistics and residual options. On Windows desktop Excel, enable it through File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box, select Go, check Analysis ToolPak, and choose OK. Then open Data > Data Analysis > Regression, set the Y Input Range and X Input Range, indicate whether the first row contains labels, and choose an output location.

The Regression tool is useful when you want a fuller output than a chart label provides, but it does not replace checking the data or interpreting the result. The basic chart trendline and functions such as SLOPE, INTERCEPT, and RSQ are often enough for a simple two-variable fit. The Analysis ToolPak is a desktop add-in; Excel for the web does not provide the same desktop Data Analysis workflow. On Mac, the add-in is managed from Excel > Tools > Excel Add-ins, although exact availability and menu details can vary by version.

Frequently Asked Questions

How do I add a linear regression line in Excel?
Select the data series in an XY scatter chart, then choose Chart Design > Add Chart Element > Trendline > Linear. You can use Format Trendline to show the equation and R².

Is a trendline the same as linear regression?
A linear trendline on a scatter chart represents a least-squares straight-line fit for the selected series. The chart makes the fit visible; regression functions or the Analysis ToolPak provide numerical results and additional analysis.

How do I get the slope and intercept in Excel?
Use SLOPE with the Y range first and X range second to get the slope, and INTERCEPT with the same argument order to get the intercept. For example, with X in A2:A6 and Y in B2:B6, use =SLOPE(B2:B6,A2:A6) and =INTERCEPT(B2:B6,A2:A6).

What is a good R-squared value?
There is no universal cutoff. The useful value depends on the field, measurement noise, purpose, and data. Evaluate the scatter and residual patterns, the model assumptions, and prediction error rather than accepting or rejecting a regression based on R² alone.

Can I use a linear trendline to prove that X causes Y?
No. A fitted association does not establish causation. Other variables, selection effects, or the way data were collected may explain the relationship. A causal claim requires an appropriate study design and supporting evidence.

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.