To add a regression line in Excel for Microsoft 365 on Windows, select your scatter plot, then choose Chart Design > Add Chart Element > Trendline > Linear. To show its equation or R-squared value, choose Chart Design > Add Chart Element > Trendline > More Trendline Options, then select the relevant display options in the Format Trendline pane.
What a regression line shows
A regression line, which Excel calls a trendline on a chart, summarizes the relationship between the horizontal values (x) and vertical values (y) in a data series. A linear trendline is a straight line that best fits the plotted points according to the least-squares method: it minimizes the sum of the squared vertical distances from the points to the line. It gives a compact description of the direction and approximate rate of change in the data.
The line is useful when you want to see whether larger x-values tend to accompany larger or smaller y-values, and to estimate the average change in y associated with a one-unit increase in x. It does not prove that x causes y to change. Other variables, unusual observations, or the way the data was collected may help explain the pattern.
Prepare the data and insert an XY scatter chart
A regression line is most meaningful when Excel treats the horizontal values as numeric measurements. Put the x-values in one column and the corresponding y-values in another, with each pair on the same row. For example, a table might have hours studied in column A and test score in column B. Include clear column headings if you want Excel to use them as the series name or axis labels.
Select the two columns, including their headings, then choose Insert > Insert Scatter (X, Y) or Bubble Chart and select a scatter chart, such as Scatter with only Markers. For two columns arranged as x then y, Excel ordinarily assigns the first column to the horizontal axis and the second to the vertical axis. Check the plotted axes before interpreting the result; if Excel assigns them incorrectly, use Chart Design > Select Data to edit the series and specify its X and Y values.
An XY scatter chart is different from a line chart. A line chart commonly treats horizontal-axis entries as categories spaced evenly apart, even when their numeric values are uneven. A scatter chart uses the actual numeric x-values, which is essential when distances between measurements matter. For instance, measurements at 1, 2, and 10 should not appear evenly spaced on an XY axis.
Add a linear trendline step by step
- Click the scatter chart, then click one of the data markers in the series you want to analyze. Selecting the series matters if the chart contains more than one set of points.
- On the ribbon, choose Chart Design > Add Chart Element > Trendline > Linear. Excel adds a straight line for the selected series.
- To display more information, choose Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, confirm that Linear is selected.
- To show the fitted equation, check Display Equation on chart. To show the coefficient of determination, check Display R-squared value on chart.
You can also use the chart’s Chart Elements button, shown as a plus sign beside a selected chart, and turn on Trendline. Its arrow opens additional trendline choices. The ribbon route is helpful when you want to reach the detailed settings directly. In Excel for Mac, select the chart and use the Chart Design tab’s chart-element controls; the pane and ribbon layout can differ slightly by release, but the trendline settings have the same purpose.
Choose the right trendline type
A linear trendline is a sensible starting point when the relationship looks roughly straight across the range of observed x-values. Excel also offers other models. Selecting a more complex curve just because it follows the existing points closely can make the result harder to explain and may fit noise rather than a durable relationship.
- Linear: A straight-line model, useful for a roughly constant increase or decrease in y for each unit of x.
- Exponential: A curved model suited to patterns where values change at a rate related to their current size. The data must have positive y-values for Excel to fit this model.
- Logarithmic: A curve that can rise or fall quickly at first and then level off; x-values must be positive.
- Polynomial: A curved model with a chosen order. Higher orders can create bends and turning points, so use them only when the shape has a defensible reason.
- Power: A model for a relationship expressed as a power of x; the data must have positive x- and y-values.
- Moving Average: A smoothed line that averages a specified number of neighboring points. It can help reveal variation in sequential data, but it is not the same kind of fitted equation as a linear regression.
In the Format Trendline pane, choose the model type that matches the pattern and the question you are asking. A curved line should not be chosen solely to make R-squared larger: models with more flexibility can usually track the sample more closely, while being less useful for explaining or predicting new observations.
Read the equation and R-squared
A linear trendline equation appears in the form y = mx + b. Here, m is the slope, or estimated change in y for a one-unit increase in x, and b is the y-intercept, the line’s estimated value of y when x equals zero. The intercept may not have a practical interpretation if zero is outside the observed range or impossible in the real-world setting.
For a small example, consider x-values 1, 2, 3, 4, and 5 paired with y-values 3, 5, 4, 6, and 7. A linear fit is approximately y = 0.9x + 2.3. The slope says that the fitted score rises by about 0.9 units for each additional x unit. At x = 4, the line predicts 5.9, while the observed y-value is 6; the difference is a residual of 0.1. A fitted value is not required to equal an observed point.
R-squared describes how much of the variation in the observed y-values is accounted for by the fitted model in this sample, under the usual regression interpretation. In this example, the linear fit has an R-squared of about 0.81. That indicates a fairly strong linear pattern in these five points, but it does not mean the model is 81 percent accurate, nor does it establish causation. R-squared also does not tell you whether the model is appropriate, whether predictions will hold outside the data range, or whether a small sample is representative.
Excel may round the displayed equation and R-squared. To show more digits, right-click the displayed equation or value, choose Format Trendline Label, and adjust the number format in the formatting pane. More displayed digits improve readability of the reported coefficients but do not make the underlying model more precise than the data supports.
Format the line and chart for clarity
A trendline should be easy to distinguish from the observations without overwhelming them. Select the line and use the formatting controls to change its color, width, or dash style. Use a contrasting line color and keep the data markers visible. If the equation overlaps points or labels, drag it to an open area of the chart.
Give the chart a descriptive title and label both axes with the measured quantity and units, such as “Hours studied” and “Test score (points).” Units help readers interpret the slope correctly: a slope of 0.9 has no useful meaning until it is clear what one x-unit and one y-unit represent. If the chart has multiple series, use a legend and ensure each series has an appropriate trendline rather than assuming one line describes every group.
For a clearer presentation, consider showing the equation only when readers need it to understand or reproduce the model. A chart with too many labels, gridlines, and fitted curves can obscure the data. When reporting a trendline, state the model type and the range of x-values used, especially if someone might mistake it for a rule that applies beyond the observed measurements.
Use the line for estimates carefully
A trendline can support interpolation: estimating y for an x-value that falls within or near the observed range. In the example equation, substituting x = 4 gives an estimated y of 5.9. Such a value is a model-based estimate, not a guarantee about an individual observation. The actual points may sit above or below the line.
Excel can extend a trendline beyond the plotted data. Open More Trendline Options and use the Forecast fields to specify how far to extend it forward or backward. Forecasting outside the observed x-range is extrapolation, and it can be unreliable: a relationship that looks linear over a short interval may flatten, reverse, or change for larger values. Do not treat a chart extension as evidence that the same pattern will continue indefinitely.
If the chart is intended for analysis rather than illustration, inspect the scatter of points as well as the fitted line. A single outlier can pull a least-squares line noticeably. A curved pattern, clusters, or a fan-shaped spread around the line can also indicate that a simple linear model misses important structure. A trendline is a summary, not a substitute for checking the data and the context in which it was collected.
Common problems and how to fix them
- The trendline option is unavailable: Make sure the chart is an XY scatter chart and that you selected a data series, not just the chart background or a single unrelated element. Some chart types do not support the same trendline choices.
- The line looks wrong: Check that the x-values and y-values are paired on the correct rows and that the intended columns are assigned to the series. Also confirm that cells contain numeric values rather than numbers stored as text.
- The equation differs from a calculator: Excel may display rounded coefficients. Increase the number of decimal places in the trendline label before comparing. Ensure the same observations and model type are being used.
- The chart has an unexpected horizontal scale: Confirm that you inserted an XY scatter chart rather than a line chart. A category axis can space values differently from their actual numeric distances.
- The intercept seems implausible: The line’s intercept is its estimate at x = 0. If your measurements begin far above zero, that point may be outside the data range, so the intercept can be mathematically defined but not meaningful in context.
- R-squared looks high but predictions are poor: R-squared is not a guarantee of accuracy. Check residuals, sample size, outliers, and whether the proposed prediction is within the range of observed x-values.
Frequently Asked Questions
How do I add a regression line to an Excel scatter plot?
Select the chart and its data series, then choose Chart Design > Add Chart Element > Trendline > Linear. Use More Trendline Options to display the equation or R-squared value.
Is a trendline the same as a regression line?
For a linear trendline on an XY scatter chart, Excel displays a least-squares fitted line, commonly called a linear regression line. Excel uses the chart label “Trendline,” while regression describes the statistical fitting method.
How do I show the regression equation on the chart?
Open the Format Trendline pane through Chart Design > Add Chart Element > Trendline > More Trendline Options, then select Display Equation on chart. Increase the label’s decimal places if its rounded coefficients are not detailed enough.
Why can’t I add a trendline in Excel?
Check that the chart is a supported chart type, preferably an XY scatter chart, and that a data series is selected. If the chart is selected but the series is not, Excel may not offer the trendline command for the data you intend to fit.
Should I use a linear or polynomial trendline?
Use a linear fit when the points show an approximately straight pattern and a constant average change is reasonable. Consider a polynomial only when there is a credible curved pattern and a reason for its shape; a higher R-squared alone is not enough to justify it.
Related
- Excel Print Area Button Greyed Out? Causes and Fixes
- Excel Power Query: Extract a Year from Date Columns
- Excel Power Query Wildcards: What Works and What Doesn’t
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Detect Circular References in Excel: Step-by-StepTo detect a circular reference in Excel for Microsoft 365 on Windows, open the Formulas tab, choose Error…
- Excel Slicer Video: How to Add, Use, and TroubleshootIf you’re looking for an Excel slicer video, search Microsoft’s Excel help and training resources for slicer tutorials;…
- Excel Maximum Row Height: The 409-Point LimitExcel’s maximum row height is 409 points. In Microsoft Excel for Microsoft 365 on Windows, you can set…
- Create Rules in Outlook for iOS: What Works and HowIn Outlook for iOS, you can make a simple rule for a sender by opening one of their…
- Automatically Transpose Data in Excel: Methods That WorkYes. To keep a transposed copy in sync with its source in Excel for Microsoft 365, enter =TRANSPOSE(A1:C2)…
- How to Edit a PivotTable in ExcelTo edit a PivotTable in Excel for Microsoft 365 on Windows, click any cell inside it, then use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.