Excel Scatter Plot Line of Best Fit: Add and Interpret

How-to basics · Updated November 21, 2026

To add a line of best fit in Excel for Microsoft 365 on Windows, select your scatter plot, then choose Chart Design > Add Chart Element > Trendline > Linear. Excel adds a straight trendline; to show its equation or R-squared value on the chart, choose More Trendline Options and select the relevant checkboxes.

What a line of best fit shows

A line of best fit, also called a trendline in Excel, summarizes the relationship between two numeric variables. A scatter plot places one variable on the horizontal axis and the other on the vertical axis. The trendline then shows the overall direction of the points: upward, downward, or nearly flat. It can help reveal a pattern that is hard to judge from individual observations.

For a linear trendline, Excel calculates a straight line that minimizes the sum of the squared vertical differences between the observed points and the line. Those vertical differences are called residuals. The line does not necessarily pass through any particular data point, and it will not generally connect the points in the order they appear in your worksheet.

A trendline describes an association in the data; it does not establish that one variable causes the other. For example, a positive line between hours of daylight and ice-cream sales may show that they rise together, but the chart alone does not prove that daylight causes the change. Other factors, such as temperature or season, may matter.

Prepare data for an XY scatter chart

Arrange the observations in paired columns: one column for the X values and one for the corresponding Y values. For example, a worksheet might have study hours in A2:A11 and test scores in B2:B11, with headers in row 1. Each row must represent one observation, so the hours and score on a row stay together. If the X and Y values are mismatched, the chart and its trendline will describe the wrong relationship.

Select both columns, including headers if present, and use Insert > Insert Scatter (X, Y) or Bubble Chart. Choose the plain scatter option with markers. A scatter chart is the appropriate choice when the horizontal values are numeric measurements; unlike a line chart, it treats the X values as numbers and spaces points according to their actual values. A line chart commonly treats horizontal labels as categories instead.

  • Check that both columns contain numeric values, not numbers stored as text.
  • Keep units consistent, and make clear what each variable measures.
  • Remove or investigate rows with missing or impossible values before fitting a line.
  • Use a chart title and axis titles so readers can understand the variables and units.

If Excel assigns the wrong column to the horizontal or vertical axis, select the chart and choose Chart Design > Select Data. Edit the series so the X values refer to the intended measurement column and the Y values refer to the outcome column. A correct series is essential: a trendline cannot fix incorrectly assigned axes.

Add and format the trendline

  1. Click the scatter chart, then select Chart Design > Add Chart Element > Trendline > Linear. Excel adds a straight line to the selected data series.
  2. To adjust the model or show statistics, open Chart Design > Add Chart Element > Trendline > More Trendline Options. The Format Trendline pane opens.
  3. Choose the trendline type that fits the pattern and the context. For a straight-line relationship, leave Linear selected.
  4. Under the trendline options, select Display Equation on chart and/or Display R-squared value on chart if those values are useful to your audience.
  5. Use the pane’s formatting controls to change the line’s color, width, or dash style. Make sure it remains visually distinct from the markers.

In Excel for Mac, select the chart and use the Chart Design tab to add a trendline; the precise arrangement of commands can differ slightly from Windows. The Format Trendline options provide the same basic model choices and display settings in current desktop versions. In Excel for the web, chart editing features can vary, so if a trendline command is not available in your workbook, open it in the desktop app.

Choose the right trendline type

Linear is a sensible starting point when the points follow an approximately straight pattern. Excel also offers several non-linear options. A curve can fit a visible bend, but a more complex model is not automatically a better explanation. Consider the shape suggested by the data and the real-world process, rather than picking a model only because it raises R-squared.

  • Exponential: Useful for some patterns that rise or fall at a rate related to their current level. The data must meet the model’s requirements; in particular, exponential trendlines require positive Y values.
  • Logarithmic: Can suit a pattern that changes rapidly at first and then levels off. It requires positive X values.
  • Polynomial: Fits a curve with one or more bends. Excel lets you set the order; higher orders can follow the observed points closely while behaving poorly between or beyond them.
  • Power: Models a relationship in which one variable changes according to a power of the other. Both X and Y values must be positive.
  • Moving average: Smooths fluctuations across a chosen number of data points. It is not a regression line that estimates a direct relationship between X and Y, and the plotted result depends on the selected period.

Excel may not offer a particular model for data that do not meet its requirements. If a logarithmic, exponential, or power option is unavailable or produces an error, check for zero, negative, or nonnumeric values as relevant. Do not change or discard valid observations merely to force a model to work.

Read the equation and R-squared

A linear equation on the chart typically appears in the form y = mx + b. Here, m is the slope and b is the vertical intercept. If the equation is y = 4x + 52, the fitted line predicts an increase of four Y units for each one-unit increase in X. The intercept is the line’s predicted Y value when X is zero. That prediction may have no practical meaning if zero is outside the observed range.

For the study-hours example, a slope of four would mean the model estimates four additional score points per extra study hour over the range represented by the data. It does not mean every student gains exactly four points, nor does it prove that additional study alone caused the difference. Predictions from a line are estimates, and the individual points show how much observations vary around it.

R-squared, written R², describes how much of the variation in Y is accounted for by the fitted model within the data used to create it. A value closer to 1 indicates a closer fit by that measure; a value closer to 0 indicates that the model explains less of the variation. It is not a percentage chance that the line is correct, and a high R² does not prove causation, guarantee accurate future predictions, or show that the model is appropriate.

Excel rounds the displayed equation and R² according to the chart label’s number format. If the displayed slope seems too imprecise for a calculation, right-click the equation label, choose Format Trendline Label, and adjust its number format. More displayed decimal places can improve readability of the reported coefficient, but they do not make the underlying data more precise.

Check whether the fit is useful

Before relying on a trendline, look at the points rather than treating the equation as the whole result. A single unusual observation can pull a linear line noticeably, especially in a small dataset. A curved pattern may make a straight line misleading even when the R² looks respectable. A cluster of points or a gap in the X values can also conceal important structure.

  • Check for outliers and confirm that they are valid observations, not data-entry errors.
  • Look for curvature, separate clusters, or a changing spread of points around the line.
  • Consider whether the number of observations and the range of measurements are adequate for the question.
  • Be cautious about extrapolation: a line fitted to one range may not describe values far outside it.
  • Use domain knowledge to decide whether the relationship and chosen model make sense.

For a more detailed regression assessment, examine residuals—the differences between observed Y values and the values predicted by the model. A residual plot can reveal patterns that the original chart hides. Excel’s chart trendline is convenient for visualization, but it is not a complete statistical analysis, particularly when you need uncertainty estimates, significance tests, or a model with multiple explanatory variables.

Common problems and fixes

The line option is missing: Confirm that the chart is an XY scatter chart and that a data series is selected. A category-based chart type may not offer the same trendline choices. Try selecting a marker in the series before opening the trendline menu.

The equation looks wrong: Check the series’ X and Y ranges in Select Data, and verify that each pair occupies the same row. Also check for text-formatted numbers, unintended blank rows, and a chart that includes a totals row or other non-observation.

The line does not pass through the origin: That is normal. A standard linear trendline estimates an intercept rather than assuming it is zero. In Format Trendline, Excel offers a Set Intercept option. Use it only when the underlying model genuinely requires a fixed intercept; forcing a line through zero just because it looks tidy can distort the fit.

The chart equation differs slightly from another calculation: The chart label may round coefficients, and the trendline might use a different data range than the separate calculation. Check the plotted series and increase the label’s displayed precision before comparing values. Small differences can also result from rounding in the displayed equation.

Trendline versus connecting the dots

A trendline and a line connecting scatter-plot points serve different purposes. The trendline summarizes an overall relationship using a model. Connecting lines simply join observations in sequence, which can imply an order or continuous path that may not exist. For example, connecting scores from different students by row number does not create a meaningful progression unless row order itself represents a meaningful sequence.

If the measurements are a time series and the order matters, a line chart may be useful for showing how values change over time. A scatter chart can also show time as numeric X values, particularly when dates are irregularly spaced. In either case, a fitted trendline answers a different question from a line connecting the measurements: it estimates an overall pattern rather than displaying every step between observations.

Frequently Asked Questions

How do I add a line of best fit to an Excel scatter plot?
Select the chart, then choose Chart Design > Add Chart Element > Trendline > Linear. Use More Trendline Options to display the equation or R-squared value.

How do I show the equation on an Excel trendline?
Open More Trendline Options for the chart’s trendline, then select Display Equation on chart. If needed, format the equation label to show more decimal places.

What does R-squared mean on an Excel scatter plot?
R-squared indicates how closely the fitted model accounts for variation in the plotted Y values in the data shown. It is not proof of causation or a guarantee that predictions will be accurate.

Can I force an Excel trendline through zero?
Yes. In the Format Trendline pane, use Set Intercept and enter zero. Do so only when a zero intercept is justified by the relationship being modeled, not simply to make the chart look simpler.

Why is my Excel trendline not showing?
Check that the chart is a scatter chart, that it contains a numeric data series, and that the series is selected when you add the trendline. Also confirm that the chosen model is compatible with the data values.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.