Linear Trendline in Excel: Make One and Show Its Equation

Charts & pivot tables · Updated November 18, 2026

To make a linear trendline in Excel for Microsoft 365 on Windows, select your paired data, choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter, then select the chart and choose Chart Design > Add Chart Element > Trendline > Linear. Excel draws a straight line that summarizes the relationship between the X and Y values; you can also display its equation and R-squared value on the chart.

Before you start, arrange the data in two columns: one for the independent or input values (X) and one for the measured results (Y). A trendline is most useful when you want to see a general direction or estimate a relationship, rather than connect every point in order. The instructions below use a scatter chart because it treats the X values as numbers. If your data uses dates or categories, see the notes on chart choice and troubleshooting.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. Ribbon names can vary slightly in other releases.
  • At least two paired observations: one numeric X value and one numeric Y value for each observation. More points usually give a more informative trend.
  • A worksheet with clear column headings and no blank rows within the data. For example, columns A and B might contain Month Number and Sales.

Step-by-Step

  1. Arrange and check the data. Put each X value beside the Y value measured for it, with headings in the first row. For example, A1 could be Month and B1 Sales, with month numbers 1 through 5 in A2:A6 and sales values 5, 7, 9, 11, and 13 in B2:B6. Each row must represent one matching pair. Check that numbers are stored as numbers, not text; left-aligned numbers or a green warning marker can be clues that a value may be stored as text. Remove unintended blank rows and check for obvious entry errors before charting.
  2. Select the complete data range. Select both columns, including the headings, such as A1:B6 in the example. Including headings helps Excel identify the series and label it in the chart. If the columns are not next to each other, select the first range, then hold Ctrl on Windows while selecting the second range. For a chart, adjacent X and Y columns are simpler and less error-prone.
  3. Insert an XY scatter chart. Go to Insert > Insert Scatter (X, Y) or Bubble Chart, then choose the basic Scatter option, usually shown as markers without connecting lines. Excel places the first selected numeric column on the horizontal axis and the second on the vertical axis. In the example, the month numbers become X values and sales becomes Y values. A scatter chart is important because the spacing between X values reflects their numeric distance.
  4. Confirm the plotted series. Check that the horizontal axis shows the X values and the vertical axis shows the Y values, and that the chart has one marker for each data pair. If Excel has treated a heading as a point or put the wrong values on an axis, use Chart Design > Select Data to review the series. Select the series and choose Edit to set its series name, X values, and Y values to the intended cell ranges. A chart can look plausible even when its axes are reversed, so verify them before adding the line.
  5. Add the linear trendline. Click one marker in the plotted series once; this selects the entire series rather than just one point. Open Chart Design > Add Chart Element > Trendline > Linear. Excel draws a straight best-fit line through the points. If the chart has multiple series, select the series you want first: Excel adds a separate trendline for the selected series, not automatically for every series.
  6. Show the equation, if useful. To reveal the line’s equation, open Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, select Display Equation on chart. Excel displays an equation in the form y = mx + b, where m is the slope and b is the vertical intercept. In the example values 1, 2, 3, 4, 5 and 5, 7, 9, 11, 13, the points follow y = 2x + 3 exactly. The displayed slope is 2 and the intercept is 3, so each one-unit increase in X corresponds to a two-unit increase in the fitted Y value.
  7. Optionally display R-squared. In the same Format Trendline pane, select Display R-squared value on chart if you want a quick measure of how closely the line fits the data. In the example, R-squared is 1 because every point lies exactly on the line. For real data, the value is usually below 1. It describes fit in this data set; it does not prove that X causes Y or that the line will predict future observations accurately.
  8. Label and tidy the chart. Use Chart Design > Add Chart Element > Axis Titles to add names and units to both axes, and add or edit a chart title. Give the axes meaningful labels such as Month Number and Sales in dollars rather than leaving a reader to infer what the values mean. Drag the equation label to a clear area if it covers points. Resize the chart or adjust number formats so the values and labels are easy to read.
  9. Check whether the line makes sense. Look at the point pattern, not just the equation. A linear trendline is appropriate when a straight-line summary is reasonable across the range you are analyzing. If the points curve, form separate clusters, or include an influential outlier, the line may hide important structure. Keep the original observations visible and describe the trendline as a summary, not as a replacement for the data.

On Mac, the chart tools and options are similar, but tabs and controls can appear in a different order or under a Chart Design tab that appears only after selecting the chart. Select the data, insert a scatter chart from the Insert tab, select the data series, and use the chart design controls to add a linear trendline. In Excel for the web, chart editing options can differ from the desktop app; if the trendline or equation setting is not available in your ribbon, open the workbook in the desktop version of Excel.

Common Mistakes

  • Choosing a line chart for numeric X values. A line chart may space category labels evenly, even when the actual X values have very different gaps. Use an XY scatter chart when the horizontal values are numeric measurements, such as elapsed time, distance, or temperature.
  • Selecting only the Y column. Without the intended X values, Excel may use row positions or make an unintended chart. Select both columns and verify the X and Y ranges in Select Data.
  • Adding the trendline to the wrong series. In a chart with several series, click a marker belonging to the intended series before choosing Linear. If the wrong line appears, remove it and repeat with the correct series selected.
  • Assuming a high R-squared means a useful prediction. R-squared is a measure of fit, not a guarantee of accuracy, causation, or future performance. A line can fit observed points well and still fail outside the range of those observations.
  • Using a rounded equation for precise calculations. The equation label may show only a few decimal places. A displayed slope rounded to 2.4, for instance, may not be precise enough for a calculation that depends on small differences. Treat the label as a readable summary, and retain appropriate precision if using the coefficients elsewhere.
  • Ignoring unusual points or curved patterns. A single outlier can pull the line, while a curved relationship can make a straight line misleading. Check the source data and consider whether the observations represent the same process before relying on the fit.

Tips

  • Make the chart self-explanatory. Include units in axis titles and use a specific chart title. “Weekly orders by advertising spend” tells a reader more than “Chart 1.”
  • Use sensible number formats. Right-click an axis and choose Format Axis to adjust bounds, units, or number display. Avoid narrowing the axis so much that ordinary variation looks dramatic. For dates, use a readable date format and check that the plotted spacing matches the dates.
  • Extend the trendline only when justified. In Format Trendline, the Forecast controls can extend a line forward or backward by a specified number of units. Forecasting beyond the observed X range is extrapolation; use it cautiously and make the extension clear to readers.
  • Keep a clean source table. Use one row per observation, consistent units, and no merged cells in the data range. If values are updated, a chart based on an Excel Table can expand more conveniently when new rows are added; verify that the chart and its trendline include the new observations.
  • Distinguish the trendline from connecting points. The markers show observed values. The trendline is the fitted straight summary. Avoid connecting the markers with lines unless the order and path between observations are meaningful, since those connecting segments are not the linear regression line.

Troubleshooting

  • The Trendline command is unavailable. Click inside the chart, then click a plotted data marker to select a series. The trendline control applies to a data series, so it may be unavailable when the chart area, title, or axis is selected. Some chart types do not support trendlines; change to an XY scatter chart and try again.
  • The line appears flat or wildly steep. Check that X and Y ranges are correct, that units are consistent, and that no values are stored as text. Also inspect the axis bounds: a wide vertical scale can make a real slope look flat, while a narrow scale can exaggerate it. Recheck unusual values against the source.
  • The equation is missing. Select the trendline, open Format Trendline, and turn on Display Equation on chart. If the pane is not open, use the More Trendline Options command from the chart’s Trendline menu. Make sure the equation label is not hidden behind another chart element.
  • The chart uses row numbers instead of the intended X values. Open Chart Design > Select Data, select the series, and choose Edit. Set the Series X values to the cells containing the independent values and Series Y values to the measured values. Avoid including the heading cells in either values range.
  • The points do not appear in date order or spacing looks wrong. Confirm that the dates are real Excel date values rather than text. Use a scatter chart for numeric date values when actual intervals matter. A line chart is useful for ordered categories, but it may give each category equal spacing even if calendar intervals differ.
  • The trendline does not match a curved pattern. A linear model may not describe the data well. Check for a curve, groups, or a change in conditions, and consider whether another trendline type is appropriate. Do not change the model just to raise R-squared; choose a model that makes sense for the data and explain the choice.

Frequently Asked Questions

What does a linear trendline show?
A linear trendline is a straight best-fit summary of the relationship between X and Y values. Its slope indicates the average change in fitted Y for a one-unit change in X, while the intercept is where the line crosses the vertical axis.

Should I use a scatter chart or a line chart?
Use a scatter chart when X values are numeric and their actual spacing matters. Use a line chart when the horizontal axis represents ordered categories and equal spacing between categories is appropriate. For dates with irregular intervals, a scatter chart can show the real time gaps.

Can I add a trendline to every series?
Yes, but add one to each series separately. Select a series, choose the Linear trendline option, and repeat for each additional series you want to analyze. Make sure the series use comparable axes and units before comparing their lines.

How do I remove a linear trendline?
Click the trendline to select it and press Delete. If selecting it is difficult, click the chart, open Chart Design > Add Chart Element > Trendline, and choose None where that option is available.

Can a linear trendline predict future values?
It can provide a simple estimate by extending the fitted line, but the estimate assumes the relationship continues. Predictions are less dependable beyond the observed range, especially if conditions change or the data are seasonal, curved, or affected by outliers.

What does an R-squared value of 1 mean?
It means the fitted line accounts for all the variation in the plotted Y values for those observations. It does not establish causation, guarantee future accuracy, or show that a straight-line model is appropriate beyond the data range.

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.