# How to Add a Regression Line in Excel (Step by Step)

To add a regression line in Excel, plot your data as an XY (scatter) chart, then add a linear trendline to the data series. Excel draws the best-fit line and can print the equation and R-squared value directly on the chart. You can also compute the same slope, intercept and R-squared with worksheet formulas.

## Quick Answer

- Put your independent variable (x) in one column and your dependent variable (y) in another.
- Select both columns and insert a Scatter chart with only markers.
- Click the chart, then go to **Chart Design > Add Chart Element > Trendline > Linear** [1].
- Open **More Trendline Options** and check **Display Equation on chart** and **Display R-squared value on chart** [1].
- For exact numbers in cells, use `=SLOPE()`, `=INTERCEPT()` and `=RSQ()` on the same two ranges [2][3].

## Before You Start

You need two numeric columns. The x column holds the variable you control or measure first, such as study hours. The y column holds the outcome, such as test score. Keep them in the same rows so each pair belongs to one observation.

A trendline can only be added to unstacked 2-D area, bar, column, line, stock, xy (scatter), or bubble charts [1]. For regression work, an XY scatter chart is the right choice because it treats both axes as numeric values. A line chart spaces categories evenly along the x axis, which distorts the fit.

Excel fits the line with the method of least squares, the same method the Regression tool in the Analysis ToolPak uses [4]. The math is identical whether you draw a trendline or call a function. The trendline is a picture of the fit. The functions give you the numbers.

If you want a refresher on building the chart itself, see [how to make a chart in Excel](/blog/data-analysis/how-to-make-chart-in-excel).

## Step by Step

1. Enter your data with headers in row 1. Put x in column B and y in column C, starting at row 2.
2. Select only the two numeric columns, for example B1:C13, then go to **Insert > Charts > Insert Scatter (X, Y) or Bubble Chart > Scatter with only Markers**.
3. Click any data point to select the series, then go to **Chart Design > Add Chart Element > Trendline > Linear** [1].
4. With the trendline selected, click **Chart Design > Add Chart Element > Trendline > More Trendline Options**.
5. In the Format Trendline pane, check **Display Equation on chart** and **Display R-squared value on chart** [1].
6. Drag the equation and R-squared labels to an empty part of the plot so they do not sit on top of the points.
7. To confirm the numbers, type the formulas below in empty cells and compare them with the chart labels.

The equation appears in the form $y = mx + b$, where $m$ is the slope and $b$ is the y-intercept [5]. The R-squared value is the coefficient of determination, the share of the variation in y explained by x.

## Worked Example

The table below holds study hours and test scores for 12 students. Column D predicts each score from the fitted line, and column E holds the residual, which is the actual score minus the predicted score.

| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Student | Study Hours | Test Score | Predicted Score | Residual |
| 2 | Ana | 2 | 65 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B2` -> 66.37 | `=C2-D2` -> -1.37 |
| 3 | Ben | 3 | 70 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B3` -> 69.32 | `=C3-D3` -> 0.68 |
| 4 | Cara | 4 | 72 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B4` -> 72.27 | `=C4-D4` -> -0.27 |
| 5 | Dan | 5 | 75 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B5` -> 75.21 | `=C5-D5` -> -0.21 |
| 6 | Eli | 6 | 78 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B6` -> 78.16 | `=C6-D6` -> -0.16 |
| 7 | Fay | 7 | 82 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B7` -> 81.11 | `=C7-D7` -> 0.89 |
| 8 | Gus | 8 | 85 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B8` -> 84.06 | `=C8-D8` -> 0.94 |
| 9 | Hana | 9 | 88 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B9` -> 87.00 | `=C9-D9` -> 1.00 |
| 10 | Ivan | 10 | 90 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B10` -> 89.95 | `=C10-D10` -> 0.05 |
| 11 | Jo | 11 | 93 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B11` -> 92.90 | `=C11-D11` -> 0.10 |
| 12 | Kim | 12 | 95 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B12` -> 95.85 | `=C12-D12` -> -0.85 |
| 13 | Lia | 13 | 98 | `=INTERCEPT($C$2:$C$13,$B$2:$B$13)+SLOPE($C$2:$C$13,$B$2:$B$13)*B13` -> 98.79 | `=C13-D13` -> -0.79 |

The summary cells sit beside the data.

| Row | G | H |
|---|---|---|
| 1 | Statistic | Value |
| 2 | Slope | `=SLOPE(C2:C13,B2:B13)` -> 2.95 |
| 3 | Intercept | `=INTERCEPT(C2:C13,B2:B13)` -> 60.48 |
| 4 | R-squared | `=RSQ(C2:C13,B2:B13)` -> 0.994777 |
| 5 | Equation | `="y = "&TEXT(H2,"0.00")&"x + "&TEXT(H3,"0.00")` -> y = 2.95x + 60.48 |
| 6 | R² | `=TEXT(H4,"0.0000")` -> 0.9948 |

The slope of 2.95 means each extra study hour is linked to about 2.95 more points on the test. The intercept of 60.48 is the predicted score at zero study hours. R-squared of 0.9948 means study hours explain about 99.5 percent of the variation in scores.

The residuals are small and balanced, ranging from -1.37 to 1.00. That pattern supports a straight-line fit. A curved pattern in the residuals would suggest the relationship is not linear.

## Other Ways to Do It

**Worksheet functions.** `SLOPE` returns the slope of the linear regression line through the data points, and `INTERCEPT` returns the point where that line crosses the y axis [2][3]. `RSQ` returns the square of the Pearson correlation, which equals R-squared for a simple linear fit. These three functions are the fastest way to get the equation into cells you can reuse.

**LINEST.** The `LINEST` function returns the slope and intercept together, along with extra statistics if you ask for them [5]. It uses the same least squares method as the trendline. The Regression tool in the Analysis ToolPak also uses `LINEST` under the hood [4].

**TREND.** `TREND` fits a straight line to known y and known x values and returns predicted y values for the x values you supply [6]. It is handy when you want a whole column of predictions without writing the slope and intercept into each row.

**FORECAST.LINEAR.** This function predicts a single y value for a given x using linear regression [7]. It is useful for one-off predictions, such as the expected score for 15 study hours.

If you want the full fitting workflow with more detail, see [how to do a linear fit in Excel](/blog/data-analysis/linear-fit-in-excel).

## Troubleshooting

**The trendline option is grayed out.** You are probably on a chart type that does not support trendlines, such as a stacked or 3-D chart. Trendlines work only on unstacked 2-D area, bar, column, line, stock, xy (scatter), or bubble charts [1]. Switch to a scatter chart.

**The equation does not match my formula.** Check that both use the same ranges and the same x and y order. `SLOPE` and `INTERCEPT` take known y first, then known x [2][3]. Reversing the arguments gives a different line.

**The R-squared label shows many decimals.** Right-click the label, choose Format Trendline Label, and under Number set a fixed number of decimal places.

**The line looks wrong.** Look at the scatter plot before trusting the fit. A straight trendline forced onto curved data will still draw, but it will not describe the relationship well.

**A function returns #DIV/0!.** `SLOPE` and `INTERCEPT` return this error when the data is undetermined or collinear, for example when all x values are identical [2][3]. Check that your x values vary.

## Common Mistakes

- **Using a line chart instead of a scatter chart.** A line chart spaces the x values evenly and hides the true shape of the data. Use an XY scatter chart for regression.
- **Swapping x and y.** The slope and intercept change when you swap the columns. Put the predictor in x and the outcome in y.
- **Reading the equation without checking R-squared.** A high slope on a low R-squared fit tells you little. Report both.
- **Extending the line far beyond the data.** A trendline describes the range you measured. Predictions far outside that range are unreliable [1].
- **Ignoring outliers.** One extreme point can pull the whole line. Plot the residuals and check for points that sit far from the rest.
- **Rounding the slope too early.** Keep full precision in the cells and round only for display, as the `TEXT` formula does above.

## Limitations

A linear trendline only fits a straight line. If the real relationship curves, the line will miss in a systematic way, for example understating the values at the ends and overstating them in the middle. Excel offers other trendline types for curved patterns, but each assumes a specific shape, so check the residuals before trusting any of them.

R-squared does not prove cause. A high R-squared means the points sit close to the line, not that x causes y. Two variables that both rise over time can produce a strong fit with no real connection. Also, a single outlier can move both the slope and R-squared a lot, so always look at the scatter plot and the residuals alongside the numbers.

## Frequently Asked Questions

### How do I find the regression equation in Excel?

Add a linear trendline to a scatter chart and check **Display Equation on chart** [1]. The label shows the equation in the form $y = mx + b$. For the numbers in cells, use `=SLOPE(y_range,x_range)` for the slope and `=INTERCEPT(y_range,x_range)` for the intercept [2][3].

### What does R-squared mean on a trendline?

R-squared is the coefficient of determination. It tells you the share of the variation in the y values that the line explains. A value of 0.9948 means about 99.5 percent of the variation is captured by the fit. The rest is scatter the line does not account for.

### Can I add a regression line without a chart?

Yes. `SLOPE`, `INTERCEPT` and `RSQ` return the slope, intercept and R-squared directly from two ranges [2][3]. You can build the equation text with `TEXT` and use it to predict values in other cells. No chart is required.

### Why is my trendline option missing?

Trendlines are only available on certain chart types, including unstacked 2-D scatter, line, column, bar, area, stock and bubble charts [1]. If your chart is stacked or 3-D, the option will not appear. Rebuild the chart as a scatter plot.

### How do I predict a value with the regression line?

Plug the x value into the equation. With a slope of 2.95 and an intercept of 60.48, a student with 15 study hours has a predicted score of $2.95 \times 15 + 60.48 = 104.73$. You can also use `FORECAST.LINEAR` to get the same prediction in one step [7]. Treat predictions outside your observed range with caution.

## References

1. [Predict data trends | Microsoft Support](https://support.microsoft.com/en-us/excel/predict-data-trends)
2. [SLOPE function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/slope-function)
3. [INTERCEPT function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/intercept-function)
4. [Use the Analysis ToolPak to perform complex data analysis | Microsoft Support](https://support.microsoft.com/en-us/excel/use-the-analysis-toolpak-to-perform-complex-data-analysis)
5. [LINEST function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/linest-function)
6. [TREND function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/trend-function)
7. [FORECAST and FORECAST.LINEAR functions | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/forecast-and-forecast-linear-functions)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)

## Related Articles

- [How to Calculate the Mean in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-mean-in-excel)
- [How to Make a Line Chart in Excel (Step by Step)](/blog/data-analysis/how-to-make-line-chart-excel)
- [How to Make a Chart in Excel (Step by Step)](/blog/data-analysis/how-to-make-chart-in-excel)
- [How to Do a Linear Fit in Excel (Step by Step)](/blog/data-analysis/linear-fit-in-excel)
- [How to Run ANOVA in Excel (Step by Step)](/blog/data-analysis/how-to-run-anova-in-excel)