Text, logical values, and blank cells are ignored.
Cells containing numeric zero are included.
The two arrays must contain the same number of paired values, or Excel returns #N/A.

Example data

Advertising spend and sales revenue, both in thousands.

Ad Spend	Sales
10	85
20	102
30	118
40	135
50	98
60	152
70	168
80	175
90	190
100	210

Enter the formula in a blank cell

Put the cursor in a result cell such as D2, then enter the CORREL formula.

Excel formula

=CORREL(A2:A11, B2:B11)

Press Enter

Excel returns the Pearson correlation coefficient immediately.

Result

r = 0.9632

Interpret the direction and strength

The relationship is very strongly positive: as advertising spend increases, sales tend to increase strongly and consistently.

Absolute reference version

=CORREL($A$2:$A$11, $B$2:$B$11)

Type the formula directly

Enter =CORREL(A2:A11, B2:B11) in a blank cell and press Enter.

Use the function wizard

Click a target cell, press the fx button, search for CORREL, then select Array1 and Array2.

Open it from the formula menu

Use Formulas -> More Functions -> Statistical -> CORREL when you do not remember the exact name.

Method 2

Analysis ToolPak: multiple variables at once

Use ToolPak when you have three or more variables and want a full correlation matrix without writing many CORREL formulas.

Enable Analysis ToolPak first

Click File -> Options.

Choose Add-ins in the left menu.

At the bottom, set Manage to Excel Add-ins, then click Go.

Check Analysis ToolPak, then click OK.

Return to the Data tab and confirm Data Analysis appears.

Generate the correlation matrix

Click Data -> Data Analysis.

Choose Correlation, then click OK.

Set Input Range to all variable columns, including headers, such as A1:C11.

Choose Columns under Grouped By.

Check Labels in First Row if row 1 contains headers.

Choose an Output Range such as E1, then click OK.

Important

ToolPak output is static. If your source data changes, the matrix does not update automatically. Use CORREL formulas if the worksheet needs to stay live.

On Mac, the add-in path is usually Tools -> Excel Add-ins -> Analysis ToolPak.

Example matrix output

	Ad Spend	Sales	Satisfaction
Ad Spend	1.00
Sales	0.96	1.00
Satisfaction	0.43	0.51	1.00

Method 3

Manual formula calculation

Use this when a course, worksheet, or report requires the calculation process, not just the final r value.

Pearson computational formula

r = [nΣxy - (Σx)(Σy)] / sqrt([nΣx^2 - (Σx)^2][nΣy^2 - (Σy)^2])

Manual Excel formula

=(COUNT(A2:A11)*SUMPRODUCT(A2:A11,B2:B11)-SUM(A2:A11)*SUM(B2:B11))
/SQRT((COUNT(A2:A11)*SUMSQ(A2:A11)-SUM(A2:A11)^2)
     *(COUNT(A2:A11)*SUMSQ(B2:B11)-SUM(B2:B11)^2))

This should return the same result as =CORREL(A2:A11, B2:B11). If it does not, check your ranges and missing values.

Break the calculation into helper columns

Helper	Excel formula	Meaning
C2 = xy	=A2*B2, fill down	Product for each paired observation
D2 = x^2	=A2^2, fill down	Squared X values
E2 = y^2	=B2^2, fill down	Squared Y values
Sum x	=SUM(A2:A11)	Total of X values
Sum y	=SUM(B2:B11)	Total of Y values
Sum xy	=SUM(C2:C11)	Total of paired products
Sum x^2	=SUM(D2:D11)	Total of squared X values
Sum y^2	=SUM(E2:E11)	Total of squared Y values
n	=COUNT(A2:A11)	Number of paired observations

C2 = xy

=A2*B2, fill down

Product for each paired observation

D2 = x^2

=A2^2, fill down

Squared X values

E2 = y^2

=B2^2, fill down

Squared Y values

Sum x

=SUM(A2:A11)

Total of X values

Sum y

=SUM(B2:B11)

Total of Y values

Sum xy

=SUM(C2:C11)

Total of paired products

Sum x^2

=SUM(D2:D11)

Total of squared X values

Sum y^2

=SUM(E2:E11)

Total of squared Y values

=COUNT(A2:A11)

Number of paired observations

Google Sheets

The same steps in Google Sheets

Google Sheets uses the same CORREL syntax as Excel and also supports PEARSON as a clearer alias.

CORREL in Google Sheets

=CORREL(A2:A11, B2:B11)

PEARSON in Google Sheets

=PEARSON(A2:A11, B2:B11)

Matrix cell example

=CORREL($A:$A, B:$B)
=CORREL(B:$B, $A:$A)

Google Sheets does not include Excel's Analysis ToolPak. Build a matrix manually by locking source columns with absolute references, then dragging the formula across the matrix grid.

After r

How to get the p-value in Excel

Excel does not give a direct p-value from CORREL. Convert r into a t statistic, then use the two-tailed t distribution.

Use the p-value calculator →

Step 1: t statistic

=D2*SQRT((COUNT(A2:A11)-2)/(1-D2^2))

Step 2: two-tailed p-value

=T.DIST.2T(ABS(E2), COUNT(A2:A11)-2)

Example conclusion

With r = 0.9632 and n = 10, t is about 10.14 and p is about 0.0000043. Report this as p < .001.

Troubleshooting

When CORREL returns an error

Most spreadsheet errors come from range mismatch, zero variance, outliers, or imported numbers stored as text.

#N/A

Cause

The two arrays contain different numbers of data points.

Example

A2:A11 has 10 values, but B2:B13 has 12 values.

Fix

Check that both ranges have the same row count. Compare =COUNT(A2:A11) with =COUNT(B2:B13).

#DIV/0!

Cause

One range is empty, or one variable has no variation.

Example

B2:B11 is entirely 100, so the standard deviation is zero.

Fix

Select the correct range. If one variable never changes, correlation is mathematically undefined.

r looks wrong

Cause

Outliers, a non-linear relationship, or mismatched rows can make Pearson r misleading.

Example

A single extreme sales value can pull r down or up even when most points follow a trend.

Fix

Insert a scatter plot and confirm each X value is paired with the correct Y value.

Numbers stored as text

Cause

Imported data may look numeric but be stored as text.

Example

Cells show a green triangle, and COUNT() returns 0.

Fix

Select the data column, click the warning icon, and choose Convert to Number.

Visual check

Always add a scatter plot

A correlation coefficient without a scatter plot can mislead you. The number hides outliers, curved patterns, and row-pairing mistakes.

Step 1: Select the two columns, including headers.

Step 2: Choose Insert -> Charts -> Scatter, then pick the dots-only chart.

Step 3: Right-click a point, add a linear trendline, and display R squared.

Step 4: Add the r value to the chart title or nearby worksheet note.

Anscombe's Quartet is the classic warning: four datasets can share the same r value but have completely different scatter plots. Use the chart before you trust the coefficient.

Quick comparison

Which method should you use?

CORREL is the default. ToolPak is efficient for matrices. Manual formulas are for teaching, auditing, or showing work.

Feature	CORREL	ToolPak	Manual
Speed	Fastest: 30 seconds	Medium: about 2 minutes	Slowest: about 5 minutes
Variables	Two variables	Three or more variables	Two variables
Updates when data changes	Yes, dynamic	No, static output	Yes, dynamic
Output	Single r value	Full correlation matrix	Single r value
Google Sheets	Fully supported	Not supported	Supported
Best audience	Most users	Analysts and researchers	Students and teaching

Speed

CORREL: Fastest: 30 seconds

ToolPak: Medium: about 2 minutes

Manual: Slowest: about 5 minutes

Variables

CORREL: Two variables

ToolPak: Three or more variables

Manual: Two variables

Updates when data changes

CORREL: Yes, dynamic

ToolPak: No, static output

Manual: Yes, dynamic

Output

CORREL: Single r value

ToolPak: Full correlation matrix

Manual: Single r value

Google Sheets

CORREL: Fully supported

ToolPak: Not supported

Manual: Supported

Best audience

CORREL: Most users

ToolPak: Analysts and researchers

Manual: Students and teaching

APA report

Report your Excel correlation correctly

Excel gives r. For a complete APA-style sentence, add N, degrees of freedom, and the p-value. The degrees of freedom are n - 2.

APA template

A Pearson correlation coefficient was computed to assess the relationship between advertising spend and sales revenue (N = 10). There was a strong positive correlation between the two variables, r(8) = .96, p < .001.

FAQ

Excel correlation questions

These are the issues that usually come up after someone gets their first r value.

What is the CORREL function in Excel?

CORREL(array1, array2) is Excel's built-in function for calculating the Pearson correlation coefficient between two datasets. It returns a value from -1 to +1.

What is the difference between CORREL and PEARSON in Excel?

In Excel, CORREL and PEARSON return identical results because both calculate the Pearson correlation coefficient. Most users prefer CORREL because it is shorter to type.

Can CORREL handle more than 2 variables?

No. CORREL accepts exactly two arrays. To calculate many pairwise correlations at once, use the Analysis ToolPak or build a correlation matrix with several CORREL formulas.

Why does my CORREL return #N/A?

This usually means array1 and array2 do not contain the same number of data points. Check the row count of both ranges with COUNT().

Does Excel calculate Pearson or Spearman correlation?

CORREL calculates Pearson correlation. For Spearman correlation, rank both variables first with RANK or RANK.AVG, then apply CORREL to the ranked columns.

Next steps

After calculating your r value

Excel gives the coefficient. These pages help you decide whether it is meaningful, significant, precise, and methodologically appropriate.

Interpret your r value →

Is r = .96 strong? Read direction, strength, and practical meaning.

Get the p-value for your correlation →

Test whether your Excel correlation is statistically significant.

Calculate the confidence interval →

See how precise your r estimate is.

Pearson vs Spearman: which is right? →

If your data are non-normal, ranked, or outlier-heavy, compare methods.

What does R squared mean? →

r = .96 means R squared is about .92, or 92% variance explained.