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

Taught hands-on in Data Analytics · Relationships & Trends module

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

=CORREL(array1, array2) Explain

Arguments

ArgumentRequired?What it does
array1YesThe first range of numbers.
array2YesThe 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.

E2fx=CORREL(B2:B9,C2:C9)
ABCDE
1MonthAd spend ($k)Sales ($k)MeasureResult
2Jan221Correlation0.9925
3Feb325
4Mar533
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=CORREL(B2:B9,C2:C9) Explain

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.

=CORREL(B2:B9,C2:C9)^2 Explain

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

Kyle Stott

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.