With x in A2:A21 and y in B2:B21: =SLOPE(B2:B21, A2:A21) gives the slope, =INTERCEPT(B2:B21, A2:A21) the intercept and =RSQ(B2:B21, A2:A21) the R². For standard errors, F and the sums of squares in one go, use =LINEST(B2:B21, A2:A21, TRUE, TRUE). Note that y always comes first.
The example data
Twenty students reported how many hours they studied (column A, x) and their exam score (column B, y). We want to know whether hours predict the score and by how much.
| Hours | 2 | 3 | 5 | 1 | 4 | 6 | 7 | 3 | 8 | 5 |
|---|---|---|---|---|---|---|---|---|---|---|
| Score | 61 | 60 | 70 | 52 | 66 | 77 | 71 | 60 | 85 | 69 |
| Hours | 2 | 9 | 6 | 4 | 7 | 10 | 1 | 8 | 5 | 3 |
| Score | 63 | 80 | 73 | 61 | 79 | 90 | 53 | 79 | 67 | 66 |
Step 1: the regression line
| Value | Formula | Result |
|---|---|---|
| Slope (b) | =SLOPE(B2:B21, A2:A21) | 3.6863 |
| Intercept (a) | =INTERCEPT(B2:B21, A2:A21) | 50.8526 |
| R² | =RSQ(B2:B21, A2:A21) | 0.9052 |
| Standard error of the estimate | =STEYX(B2:B21, A2:A21) | 3.2414 |
The equation is Score = 50.85 + 3.69 × Hours. Each extra hour of study goes with about 3.7 more points, and the model explains 90.5% of the variation in scores.
Step 2: the full output with LINEST
Type this in an empty cell with room for a 5 × 2 block below and to the right:
=LINEST(B2:B21, A2:A21, TRUE, TRUE)| Row | Column 1 | Column 2 | Meaning |
|---|---|---|---|
| 1 | 3.6863 | 50.8526 | Slope, intercept |
| 2 | 0.2811 | 1.5690 | Standard error of the slope, of the intercept |
| 3 | 0.9052 | 3.2414 | R², standard error of the estimate |
| 4 | 171.95 | 18 | F statistic, residual degrees of freedom |
| 5 | 1806.68 | 189.12 | Regression sum of squares, residual sum of squares |
Step 3: is the slope significant?
LINEST gives no p-values, but you can get them from t = coefficient ÷ standard error:
=T.DIST.2T(ABS(3.6863/0.2811), 18)t = 13.11 and p = 1.2 × 10−10, so p < .001: hours studied is a significant predictor. In simple regression, the F test gives the same p-value (=F.DIST.RT(171.95, 1, 18)).
The 95% confidence interval of the slope is 3.6863 ± T.INV.2T(0.05, 18) × 0.2811 = [3.10, 4.28].
Step 4: make a prediction
=FORECAST(6, B2:B21, A2:A21)A student who studies 6 hours is predicted to score 72.97. Avoid predicting far outside the range of your data (here, 1 to 10 hours): the line may not hold there.
Select your data, click Run: full results table, assumption checks, a plain-language interpretation and an APA line, free in Google Sheets.
Step 5: draw it
- Select A1:B21 and choose Insert › Chart. Pick Scatter chart as the chart type.
- In Customize › Series, tick Trendline.
- Set Label to Use equation and tick Show R².
Check the assumptions
- Linearity: the scatter plot should look like a straight band, not a curve.
- Independence: one row per person, no repeated measurements.
- Constant spread: plot the residuals (observed − predicted) against x. They should form an even cloud around 0, not a funnel.
- Normal residuals: here a Shapiro-Wilk test on the residuals gives p = .47, which is fine.
A strong relationship does not prove that one variable causes the other. Students who study more may also sleep better or attend more classes.
How to report it (APA style)
A simple linear regression showed that hours of study significantly predicted exam scores, b = 3.69, 95% CI [3.10, 4.28], t(18) = 13.11, p < .001. The model explained 90.5% of the variance in scores, R² = .91, F(1, 18) = 171.95, p < .001.
Frequently asked questions
How do I get the p-value of a regression in Google Sheets?
Divide the slope by its standard error (both come from LINEST with verbose set to TRUE) to get t, then use =T.DIST.2T(ABS(t), n-2) for a simple regression.
What is the difference between R² and adjusted R²?
R² is the share of the variation in y explained by the model. Adjusted R² corrects it for the number of predictors: 1 - (1 - R²) × (n - 1) / (n - k - 1). It matters mostly in multiple regression.
Can Google Sheets do multiple regression?
Yes. Give LINEST several adjacent x columns, for example =LINEST(C2:C21, A2:B21, TRUE, TRUE). The coefficients come back in reverse order: last x column first, intercept last.
How do I show the regression equation on a chart?
Insert a scatter chart, then in the chart editor open Customize, Series, tick Trendline and set the label to Use equation. You can also tick Show R².
Select your data, click Run: full results table, assumption checks, a plain-language interpretation and an APA line, free in Google Sheets.