=CORREL(A2:A21, B2:B21) returns Pearson's r. For the p-value, compute t = r × SQRT((n − 2) / (1 − r²)) and then =T.DIST.2T(ABS(t), n − 2). If p < 0.05, the correlation is statistically significant.
The example data
We use the same 20 students as in the regression guide: hours studied in A2:A21 and exam score in B2:B21. Put r in E1 and n in E2.
Step 1: Pearson's r
=CORREL(A2:A21, B2:B21)Result: r = 0.951, a very strong positive correlation. =PEARSON(A2:A21, B2:B21) gives the same value. Squaring it gives r² = 0.905, the share of variation the two variables have in common.
Step 2: the p-value
Google Sheets has no function that returns the p-value of a correlation. With r in E1 and =COUNT(A2:A21) in E2:
=T.DIST.2T(ABS(E1*SQRT((E2-2)/(1-E1^2))), E2-2)Here t = 13.11 with 18 degrees of freedom and p = 1.2 × 10−10 (p < .001).
Step 3: the 95% confidence interval
Use Fisher's z transformation, which Google Sheets provides as FISHER and FISHERINV:
Lower: =FISHERINV(FISHER(E1)-NORM.S.INV(0.975)/SQRT(E2-3))
Upper: =FISHERINV(FISHER(E1)+NORM.S.INV(0.975)/SQRT(E2-3))Result: 95% CI [0.879, 0.981]. Even the lower bound shows a strong relationship.
Select your data, click Run: full results table, assumption checks, a plain-language interpretation and an APA line, free in Google Sheets.
Spearman's rank correlation
Use Spearman instead of Pearson when the data are ranks or ordinal scores (like a 1–5 scale), when the relationship is monotonic but not straight, or when outliers distort r. There is no Spearman function, so rank each column first:
C2: =RANK.AVG(A2, $A$2:$A$21, 1)
D2: =RANK.AVG(B2, $B$2:$B$21, 1)Fill both down to row 21, then:
=CORREL(C2:C21, D2:D21)Result: ρ = 0.954. The third argument 1 ranks in ascending order, and RANK.AVG gives tied values their average rank, which is what Spearman's formula needs. For n of about 10 or more, the p-value formula from Step 2 is a good approximation (here p < .001).
Reading the strength
| |r| | Usual label |
|---|---|
| below .10 | negligible |
| .10 to .29 | small |
| .30 to .49 | medium |
| .50 and above | large |
The sign gives the direction: positive means both go up together, negative means one goes down when the other goes up. Always look at the scatter plot too. A single outlier or a curved pattern can make r misleading.
Correlating many variables at once
Google Sheets has no correlation matrix tool. You can write one CORREL per pair, or use ExplainStats, which builds the full matrix of r values in one step.
How to report it (APA style)
There was a strong positive correlation between hours of study and exam score, r(18) = .95, 95% CI [.88, .98], p < .001.
The number in brackets is the degrees of freedom, n − 2. Leave out the leading zero for r and p, because they cannot be larger than 1.
Frequently asked questions
What is the difference between CORREL and PEARSON?
None. Both return Pearson's correlation coefficient r and give the same result.
Is there a Spearman function in Google Sheets?
No. Rank each variable with RANK.AVG (ascending, with the third argument set to 1) and run CORREL on the two rank columns. The result is Spearman's rho.
What is a strong correlation?
A common rule of thumb (Cohen) is |r| around .10 small, .30 medium and .50 or more large. What counts as strong also depends on your field.
Does a significant correlation mean causation?
No. A correlation shows that two variables move together. A third variable, or a reverse effect, can explain it.
Select your data, click Run: full results table, assumption checks, a plain-language interpretation and an APA line, free in Google Sheets.