Excel CORREL Function

last modified October 5, 2026

The CORREL function calculates the Pearson correlation coefficient between two data sets. It measures the strength and direction of the linear relationship between two variables. This tutorial provides a comprehensive guide to using the CORREL function with detailed examples. You'll learn basic syntax, practical applications, and how to interpret the results.

CORREL Function Basics

The CORREL function returns the Pearson correlation coefficient, usually written r. The value always lies between -1 and 1. A result near 1 means the two variables rise and fall together, a result near -1 means one variable rises while the other falls, and a result near 0 means there is little or no linear relationship.

Component	Description
Function Name	CORREL
Syntax	=CORREL(array1, array2)
Arguments	Two required ranges of equal size
Return Value	Correlation coefficient (-1 to 1)

This table breaks down the essential components of the CORREL function. It shows the function name, basic syntax format, argument requirements, and return value characteristics.

Argument	Meaning
array1	The first set of numeric values. It can be a range, an array constant, or a reference to a range.
array2	The second set of numeric values. It must contain the same number of data points as array1.

Text, logical values, and empty cells are ignored, and the matching value in the other array is dropped with them. The calculation therefore uses only the pairs in which both values are numbers. If the two ranges contain a different number of data points, CORREL returns the #N/A error.

Basic CORREL Example

This example demonstrates the simplest use of CORREL with two small data sets. We examine the relationship between study hours and test scores.

A	B	C
Study Hours	Test Scores
2	65
4	80
6	95
		=CORREL(A2:A4,B2:B4)

Basic CORREL formula

=CORREL(A2:A4,B2:B4)

This formula calculates the correlation between study hours (A2:A4) and test scores (B2:B4). The result is 1, indicating a perfect positive correlation. As study hours increase, test scores increase proportionally.

CORREL with Real-World Data

This example uses CORREL with larger data sets to analyze the relationship between temperature and ice cream sales over 12 months.

A	B	C
Month	Temperature (°F)	Ice Cream Sales
Jan	32	150
Feb	35	170
Mar	45	210
Apr	58	300
May	68	360
Jun	78	430
Jul	85	480
Aug	83	460
Sep	74	380
Oct	62	290
Nov	48	200
Dec	36	160
		=CORREL(B2:B13,C2:C13)

CORREL with monthly data

=CORREL(B2:B13,C2:C13)

This formula calculates the correlation between temperature (B2:B13) and ice cream sales (C2:C13). The result is about 0.9897, a very strong positive correlation. Higher temperatures generally go together with increased ice cream sales.

CORREL with Negative Correlation

This example demonstrates negative correlation by examining the relationship between outdoor temperature and heating costs.

A	B	C
Month	Temperature (°F)	Heating Cost ($)
Jan	25	180
Feb	30	150
Mar	40	120
Apr	52	90
May	63	70
Jun	72	55
Jul	78	45
Aug	76	50
Sep	67	65
Oct	55	85
Nov	42	130
Dec	28	165
		=CORREL(B2:B13,C2:C13)

CORREL showing negative relationship

=CORREL(B2:B13,C2:C13)

This formula calculates the correlation between temperature (B2:B13) and heating costs (C2:C13). The result is about -0.9859, indicating a very strong negative correlation. As temperatures rise, heating costs typically decrease.

CORREL with No Correlation

This example shows two data sets with no linear relationship, demonstrating how CORREL identifies a lack of correlation.

A	B	C
Day	Cats Seen	Stock Index
1	3	10494
2	5	10525
3	2	10509
4	4	10480
5	3	10496
6	6	10480
7	1	10492
8	4	10522
		=CORREL(B2:B9,C2:C9)

CORREL with unrelated data

=CORREL(B2:B9,C2:C9)

This formula calculates the correlation between cats seen (B2:B9) and the stock market index (C2:C9). The result is essentially 0, indicating no linear relationship. The two variables move independently of each other.

CORREL and Non-Linear Relationships

A coefficient near 0 means there is no linear relationship, not that the variables are unrelated. The next example correlates a number with its square over a symmetric set of values.

A	B
x	x squared
-3	9
-2	4
-1	1
0	0
1	1
2	4
3	9

CORREL misses a curved relationship

=CORREL(A2:A8,B2:B8)

The result is 0 even though the two columns are perfectly related: each value in column B is exactly the square of the value in column A. Because the pattern is a symmetric U-shape rather than a straight line, the positive and negative movements cancel out. Always plot the data before trusting a correlation coefficient.

CORREL with Different Sized Ranges

This example demonstrates what happens when the input ranges have different sizes, which causes an error in Excel.

A	B	C
X Values	Y Values
10	20
15	25
20
		=CORREL(A2:A4,B2:B3)

CORREL with mismatched ranges

=CORREL(A2:A4,B2:B3)

This formula attempts to correlate range A2:A4 (3 values) with B2:B3 (2 values). Excel returns a #N/A error because the ranges must be the same size. Always verify range sizes when using CORREL.

CORREL with Text, Logical, and Empty Cells

This example shows how CORREL handles ranges containing text values or empty cells.

A	B	C
X Values	Y Values
5	10
Text	15
	20
12	25
20	30
		=CORREL(A2:A6,B2:B6)

CORREL with mixed data types

=CORREL(A2:A6,B2:B6)

This formula correlates ranges that contain a text value (A3) and an empty cell (A4). CORREL works on pairs, so a pair is used only when both of its values are numbers. The pairs (Text, 15) and (empty, 20) are dropped entirely, even though 15 and 20 are valid numbers, and the result is based on (5, 10), (12, 25), and (20, 30). That gives about 0.9494. Logical values such as TRUE and FALSE are ignored in the same way.

Interpreting CORREL Results

Understanding the correlation coefficient is crucial for proper analysis. The sign shows the direction of the relationship and the magnitude shows its strength.

Correlation Value	Interpretation
1.0	Perfect positive correlation
0.7 to 0.9	Strong positive correlation
0.4 to 0.6	Moderate positive correlation
0.1 to 0.3	Weak positive correlation
0	No linear correlation
-0.1 to -0.3	Weak negative correlation
-0.4 to -0.6	Moderate negative correlation
-0.7 to -0.9	Strong negative correlation
-1.0	Perfect negative correlation

These ranges are guidelines, not strict rules; the boundaries are a matter of convention. Remember that correlation does not imply causation. Two variables can move together because a third factor drives both of them. Always consider the context before drawing conclusions from a coefficient.

CORREL and Related Functions

Several other Excel functions build on the same idea. PEARSON returns exactly the same value as CORREL. RSQ returns the square of the coefficient, also called the coefficient of determination, which is the share of the variation in one variable explained by the other. SLOPE returns the slope of the best-fit line and relates to the coefficient through SLOPE = CORREL * STDEV.S(ys) / STDEV.S(xs). Covariance is the unstandardized version of correlation, so CORREL = COVARIANCE.P(array1, array2) / (STDEV.P(array1) * STDEV.P(array2)).

A	B
X	Y
1	2
2	3
3	5
4	6
5	9

Related functions side by side

=CORREL(A2:A6,B2:B6)                                        ' 0.9815
=PEARSON(A2:A6,B2:B6)                                       ' 0.9815
=RSQ(B2:B6,A2:A6)                                           ' 0.9633
=COVARIANCE.P(A2:A6,B2:B6)/(STDEV.P(A2:A6)*STDEV.P(B2:B6))  ' 0.9815
=SLOPE(B2:B6,A2:A6)                                         ' 1.7

All five formulas describe the same data. The coefficient is 0.9815, so RSQ reports 0.9815 squared, or about 0.9633. The covariance is 3.4, and dividing it by the product of the population standard deviations (1.4142 * 2.4495) returns the coefficient. The slope of 1.7 means that Y increases by about 1.7 units for every one-unit increase in X. Note that RSQ and SLOPE expect the Y range first, while CORREL and PEARSON treat their two ranges symmetrically.

CORREL Errors and Troubleshooting

Cause	Symptom	Fix
Ranges have a different number of data points	#N/A error	Make both ranges the same size
An array is empty or has no numeric values	#DIV/0! error	Include at least two numeric pairs
One array has zero variance (all values identical)	#DIV/0! error	Ensure the values actually vary
Non-numeric or blank cells leave fewer than two valid pairs	#DIV/0! error	Clean the data or use a helper column
Numbers stored as text	Pairs silently dropped, misleading result	Convert with VALUE or Text to Columns
A referenced cell already holds an error	That same error is returned	Fix the source error first

The CORREL function is a powerful tool for statistical analysis in Excel. It reveals whether two variables move together, in opposite directions, or independently, and it expresses that relationship on a single, unit-free scale from -1 to 1. Because it ignores text, logical values, and empty cells, clean data gives the most reliable results, and because it measures only linear association, it should always be paired with a chart. Used with care, CORREL turns raw columns of numbers into an immediate, quantitative answer about how strongly they are related.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.

Ebooks