CORREL Function
Returns the correlation coefficient between two sets of numbers.
Category
Statistical
Available in
All versions, Microsoft 365
Returns
A number from −1 to 1
Overview
CORREL measures how closely two sets of numbers move together in a straight line. 1 means a perfect rising line, −1 a perfect falling line and 0 no straight-line relationship.
As a rough guide, values above 0.7 (or below −0.7) are strong and values near 0 are weak. The order of the two ranges doesn't matter.
Rows where either value is text, TRUE/FALSE or empty are left out in pairs, so the two ranges stay aligned.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array1 | Yes | The first range of numbers. |
| array2 | Yes | The second range, the same size as array1. |
Examples
Example 1 — Do ad spend and sales move together?
Eight months of Northstar's advertising spend and sales, both in $ thousands.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Ad spend ($k) | Sales ($k) | Measure | Result |
| 2 | Jan | 2 | 21 | Correlation | 0.9925 |
| 3 | Feb | 3 | 25 | ||
| 4 | Mar | 5 | 33 | ||
| 5 | Apr | 4 | 28 | ||
| 6 | May | 6 | 38 | ||
| 7 | Jun | 7 | 40 | ||
| 8 | Jul | 8 | 46 | ||
| 9 | Aug | 5 | 31 |
0.99 — a very strong positive relationship: months with more advertising had higher sales.
Example 2 — Square it for R²
The square of the correlation is the share of variation explained by a straight line.
Returns about 0.985 — the same as RSQ.
Common mistakes
- Correlation isn't causation. A strong result shows the numbers move together, not that one causes the other. Both could be driven by a third factor, such as the season.
- Only straight lines. A strong curved relationship can still give a correlation near 0. Chart the data first.
- #DIV/0! One of the ranges has no spread (every value is the same), or there are fewer than two pairs.
- #N/A The two ranges are different sizes.
Notes
- PEARSON returns exactly the same result.
- Related functions: PEARSON, RSQ, COVARIANCE.S, SLOPE, FISHER.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.