What is the Excel formula for the correlation coefficient?

Use =CORREL(array1, array2). Select the cells holding the first variable, then the cells holding the second. The result is Pearson's r, a number between -1 and +1. The PEARSON function returns the same value.

Does Excel give a p value for a correlation?

No. CORREL, PEARSON, and the Analysis ToolPak correlation matrix all return the coefficient without a significance test. You can compute it yourself: t = r * SQRT(n-2) / SQRT(1-r^2), then =T.DIST.2T(ABS(t), n-2).

How do you calculate Spearman's rank correlation in Excel?

Excel has no Spearman function. Rank both variables with RANK.AVG, then run CORREL on the two rank columns. RANK.AVG averages tied ranks, which is what Spearman requires. RANK.EQ gives tied values the same rank and will produce a wrong coefficient when ties are present.

How do you make a correlation matrix in Excel?

Enable the Analysis ToolPak add-in, then choose Data, Data Analysis, Correlation. Select the full block of variables, tick Labels in first row, and choose an output cell. Excel returns a lower triangle of Pearson coefficients with no p values and no sample sizes.

Why does CORREL return an error?

Mismatched ranges return #N/A. A variable with no variation, where every value is identical, returns #DIV/0!. Empty cells and text are dropped along with their partner value, so a stray label inside a data range quietly reduces your sample size.

How to Calculate a Correlation Coefficient in Excel

The short answer

Put one variable in a column, the other in the column beside it, and enter:

=CORREL(A2:A13, B2:B13)

That returns Pearson's r, a number from -1 to +1. =PEARSON(A2:A13, B2:B13) returns the identical value. Excel keeps both for historical reasons.

Step by step with real numbers

Twelve students, hours studied against exam score.

Row	A: Hours	B: Score
2	2	57
3	3	69
4	4	56
5	5	63
6	6	50
7	7	75
8	8	71
9	9	81
10	10	69
11	11	76
12	12	80
13	13	77

Type the headers in row 1: Hours in A1, Score in B1.
Enter the twelve pairs in rows 2 through 13.
In any empty cell, enter =CORREL(A2:A13, B2:B13).
Excel returns 0.72.

For the proportion of shared variance, use =RSQ(B2:B13, A2:A13), which gives 0.52. Note the argument order: RSQ takes the y values first, the opposite of CORREL's symmetry. Squaring the CORREL result gets you to the same place.

Excel does not give you a p value

This is the gap that matters. CORREL, PEARSON, and the Analysis ToolPak all hand back a coefficient and stop. An r of .72 from twelve students and an r of .72 from twelve hundred are not the same finding, and Excel shows you the same number for both.

Two extra cells close it. With r in cell D1 and n = 12:

D2: =D1*SQRT(12-2)/SQRT(1-D1^2)
D3: =T.DIST.2T(ABS(D2), 12-2)

D2 is the t statistic, 3.28. D3 is the two-tailed p value, 0.008. So the correlation is significant at the .05 level, and now you can report it.

On Excel 2007 and earlier, the function is =TDIST(ABS(D2), 10, 2) instead.

Excel does not give you a confidence interval either

The interval requires a Fisher z transformation, an inverse normal, and a transformation back. It is four more cells of arithmetic and easy to get subtly wrong.

For the data above the 95% interval runs from .25 to .92. Worth seeing. An r of .72 sounds settled until the interval shows the population value could plausibly be .25. Twelve students do not pin down much, and the coefficient alone hides that.

Skip the extra cells. Paste the same two columns into the correlation calculator and get r, r², the exact p value, the confidence interval, and an APA 7 write-up in one step.

Open the correlation calculator

Spearman's rank correlation in Excel

Excel has no Spearman function. Rank each variable first, then correlate the ranks.

In C2: =RANK.AVG(A2, $A$2:$A$13, 1) and fill down.
In D2: =RANK.AVG(B2, $B$2:$B$13, 1) and fill down.
Then =CORREL(C2:C13, D2:D13), which returns 0.75 for this data.

Use RANK.AVG, not RANK.EQ. Spearman requires tied values to share the average of the ranks they span. RANK.EQ assigns them all the lowest rank in the tie and leaves gaps, which shifts the coefficient whenever ties exist. With no ties the two agree, which is how the mistake survives unnoticed until it matters.

Building a correlation matrix in Excel

For more than two variables, the Analysis ToolPak does it in one pass.

Enable the add-in once. Windows: File, Options, Add-ins, Manage Excel Add-ins, Go, tick Analysis ToolPak. Mac: Tools, Excel Add-ins, tick Analysis ToolPak.
Data tab, Data Analysis, Correlation.
Set Input Range to the whole block of variables including headers, set Grouped By to Columns, tick Labels in first row.
Choose an output cell and confirm.

Excel returns a lower triangle of Pearson coefficients. No p values, no sample sizes, no significance markers, and no option for Spearman. For a matrix you intend to publish, that means adding every p value by hand, once per pair, which is where transcription errors get in.

Where CORREL quietly misleads

It only sees straight lines. A strong curved relationship can return an r near zero. Chart the scatterplot before trusting the number.
One outlier moves it. With small samples a single unusual point can create or erase a correlation. Spearman is more resistant.
Blanks shrink your n silently. If either cell in a pair is empty or holds text, Excel drops the pair. A stray note inside the range reduces the sample without saying so.
Errors mean something specific. #N/A means the two ranges are different lengths. #DIV/0! means a variable has no variation, every value identical.
It still is not causation. Hours and scores moving together does not establish that studying raised the scores.

Correlation coefficient calculator with p value, confidence interval, and APA 7 output
Descriptive statistics calculator for means, SD, and skewness before you correlate
Chi-square test calculator when both variables are categorical
Which statistical test should I use?