Spreadsheet How-To
How to Calculate Correlation and Regression in Excel
Use =CORREL(x_range,y_range) for the correlation coefficient, =SLOPE(y_range,x_range) and =INTERCEPT(y_range,x_range) for the regression line and =RSQ(y_range,x_range) for R². For x = 1 to 5 and y = 2, 4, 5, 4, 5 they return r = 0.7746, slope 0.6, intercept 2.2 and R² = 0.6, so the fitted line is y = 2.2 + 0.6x. The Regression tool of the Data Analysis ToolPak adds standard errors, p-values and confidence intervals.
The functions
Put the x values 1, 2, 3, 4, 5 in A2:A6 and the y values 2, 4, 5, 4, 5 in B2:B6.
| Quantity | Formula (Excel and Google Sheets) | Result |
|---|---|---|
| Pearson correlation r | =CORREL(A2:A6,B2:B6) | 0.7746 |
| R-squared | =RSQ(B2:B6,A2:A6) | 0.6 |
| Slope | =SLOPE(B2:B6,A2:A6) | 0.6 |
| Intercept | =INTERCEPT(B2:B6,A2:A6) | 2.2 |
| Standard error of the estimate | =STEYX(B2:B6,A2:A6) | 0.8944 |
| Prediction at x = 6 | =FORECAST.LINEAR(6,B2:B6,A2:A6) | 5.8 |
| Sample covariance | =COVARIANCE.S(A2:A6,B2:B6) | 1.5 |
Watch the argument order: RSQ, SLOPE, INTERCEPT, STEYX and FORECAST take the y range first and the x range second, while CORREL and the covariance functions do not care. The fitted line is y = 2.2 + 0.6x: each extra unit of x adds 0.6 to the predicted y. Check it with the linear regression calculator and read linear regression explained for the method.
The Regression tool of the Analysis ToolPak
- Load the add-in as described in t-test in Excel, then choose Data, Data Analysis, Regression.
- Enter B1:B6 as the Input Y Range and A1:A6 as the Input X Range, and tick Labels if row 1 holds headers.
- Choose an output location and click OK.
The output for the example is:
| Regression statistics | Value |
|---|---|
| Multiple R | 0.7746 |
| R Square | 0.6000 |
| Adjusted R Square | 0.4667 |
| Standard Error | 0.8944 |
| Observations | 5 |
| ANOVA | df | SS | MS | F | Significance F |
|---|---|---|---|---|---|
| Regression | 1 | 3.6 | 3.6 | 4.5 | 0.1240 |
| Residual | 3 | 2.4 | 0.8 | ||
| Total | 4 | 6.0 |
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
|---|---|---|---|---|---|---|
| Intercept | 2.2 | 0.9381 | 2.3452 | 0.1007 | −0.7854 | 5.1854 |
| X Variable 1 | 0.6 | 0.2828 | 2.1213 | 0.1240 | −0.3001 | 1.5001 |
Multiple R is |r|, the same as CORREL. Standard Error is STEYX. With one predictor, Significance F and the slope's P-value are the same test, 0.1240. That p-value is above 0.05 and the slope interval −0.30 to 1.50 includes zero: five points are too few to call a correlation of 0.77 significant, even though it looks strong.
LINEST in one formula
=LINEST(B2:B6,A2:A6,TRUE,TRUE) returns the regression statistics as a 5 × 2 array:
| Row of the array | Column 1 (slope) | Column 2 (intercept) |
|---|---|---|
| Coefficients | 0.6 | 2.2 |
| Standard errors | 0.2828 | 0.9381 |
| R² and standard error of y | 0.6 | 0.8944 |
| F and residual df | 4.5 | 3 |
| Regression SS and residual SS | 3.6 | 2.4 |
In Google Sheets LINEST works the same way, and its fourth argument is called verbose. Use it when the statistics must update automatically, since the ToolPak output is a static snapshot.
Spearman rank correlation by hand
C2: =RANK.AVG(A2,A$2:A$6,1) D2: =RANK.AVG(B2,B$2:B$6,1) fill down to row 6
=CORREL(C2:C6,D2:D6) returns 0.7379
The ranks of y are 1, 2.5, 4.5, 2.5 and 4.5 because RANK.AVG gives tied values the average of their ranks. The Spearman coefficient is the Pearson correlation of the ranks; see the Spearman correlation calculator.
Mistakes that mislead
- Correlation is not causation. A strong r says the variables move together, not why; see correlation vs causation.
- Extrapolating. The line is fitted on x from 1 to 5. A prediction at x = 6 is close to the data, while a prediction at x = 50 assumes the trend continues far beyond what you observed.
- Ignoring the scatter plot. Very different shapes, including curves and single outliers, can share the same r and R². Always look at the chart before trusting the numbers.
- Reading R² as accuracy. It is the share of variance explained; see how to interpret R-squared.
Try the Linear Regression Calculator
Fit a least-squares line with the slope, intercept, R² and prediction shown step by step.
Try the Correlation Calculator
Compute Pearson's r for two variables with its p-value and a scatter plot.
Frequently Asked Questions
What is the difference between CORREL and RSQ?
CORREL returns Pearson's r, which runs from −1 to 1 and carries the direction of the relationship. RSQ returns r squared, the share of the variance of y that the straight line accounts for, which is always between 0 and 1. For the example r = 0.7746 and r² = 0.6, so 60% of the variation in y is explained by x.
How do I get the p-value of a correlation in Excel?
Convert r to a t statistic and look it up: =T.DIST.2T(ABS(r)*SQRT((n-2)/(1-r^2)),n-2). For r = 0.7746 and n = 5 that gives t = 2.1213 and p = 0.1240. The same p-value is shown as Significance F in the Regression ToolPak output, because with one predictor the two tests are identical.
How do I do multiple regression in Excel?
Put the predictors in adjacent columns and use either the Regression tool of the Analysis ToolPak, with all predictor columns as the Input X Range, or =LINEST(y_range,x_range,TRUE,TRUE) entered as an array. LINEST lists the coefficients in reverse order of the columns, so the last predictor comes first and the intercept is last.
How do I add a trendline and show R² on a chart?
Create a scatter chart, click a data point, choose Add Trendline and pick Linear, then tick Display Equation on chart and Display R-squared value on chart. The equation shown is the same y = 2.2 + 0.6x that SLOPE and INTERCEPT return.
How do I calculate Spearman's rank correlation in Excel?
Excel has no Spearman function. Rank each variable with =RANK.AVG(A2,A$2:A$6,1) in helper columns, which averages the ranks of ties, and then apply CORREL to the two rank columns. For the example data the result is 0.7379. Spearman is preferred for ordinal data, outliers or curved but steadily rising relationships.
What is a good R-squared?
It depends on the field. Physical measurements can reach 0.99, while models of human behaviour often explain far less and can still be useful. A high R² does not prove the model is right, since a curved relationship or one outlier can produce it, and a low R² can still go with a slope that is clearly significant.