The CORREL function
Returns the correlation coefficient of two ranges, from -1 to 1. Close to 1 means the two rise together, close to -1 that one falls as the other rises, and close to 0 no straight-line link.
How to use CORREL
CORREL tells you whether two columns of numbers move together, and how tightly. Think of ice cream sales and temperature: on hot days both go up. CORREL turns that feeling into a single number between -1 and 1.
Use it on pairs of numbers where row 2 in one column belongs with row 2 in the other: ad spend and sales per month, temperature and sales per day, hours of training and test score per employee. With =CORREL(A2:A6,B2:B6), a result near 1 means the two rise together, near -1 means one falls as the other rises, and near 0 means there is no straight-line link. The ranges must be the same size, and the order does not matter: swap them and you get the same number.
CORREL only finds straight lines. A curve or a single odd value can give a result near 0 even when the link is clear, so draw a scatter chart before you trust the number. A high result also says nothing about cause. To describe one column on its own, use AVERAGE or MEDIAN.
Syntax
CORREL(array1, array2)- array1
- The first range of numbers.
- array2
- The second range, the same size as the first.
Examples
=CORREL(1A2:A6,2B2:B6)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ad spend | Sales | 0.98644 | |
| 2 | 10 | 120 | ||
| 3 | 20 | 150 | ||
| 4 | 30 | 200 | ||
| 5 | 40 | 210 | ||
| 6 | 50 | 260 |
In plain words: How closely 1the ad spend in A2:A6 and 2the sales in B2:B6 move together.
Common CORREL mistakes
CORREL returns #N/A
The two ranges have different sizes. Use the same number of cells in both, like
=CORREL(A2:A13,B2:B13).CORREL returns #DIV/0!
One of the ranges is empty, has no numbers, or has the same value in every cell. CORREL needs numbers that vary in both columns.
The result is near 0 but the data clearly belong together
CORREL measures straight-line links only. If the numbers follow a curve, or one extreme value pulls the result, plot the two columns in a scatter chart to see the real pattern.
The result ignores some of my numbers
Numbers stored as text are skipped. They usually sit on the left side of the cell. Convert them to real numbers, for example with VALUE in a helper column, and run CORREL again.
Questions
Does a high correlation mean one thing causes the other?
No. CORREL only measures how closely two columns move together in a straight line. Both may be driven by something else, like the season.
What counts as a strong correlation?
A result above 0.7 or below -0.7 is often called strong, but it depends on your field and on how many data points you have. A few rows can give a high result by chance.
What is the difference between CORREL and PEARSON?
None. Both return the same correlation coefficient, so you can use either.