# Excel CORREL Function: Formula, Syntax and Examples

The Excel CORREL function returns the correlation coefficient between two cell ranges, a number between -1 and 1 that measures how strongly two variables move together. You write the correl formula as `=CORREL(array1, array2)`, where each array is a range of values with the same number of data points. This article covers the syntax, a worked example, error fixes, and the limits you should know before you trust a result.

## Quick Answer

- `=CORREL(array1, array2)` returns the Pearson correlation coefficient for two ranges [1].
- The result runs from -1 to 1. Values near +1 mean the two variables rise together, values near -1 mean one rises as the other falls, and values near 0 mean little or no linear relationship [1].
- Both arguments are required, and both ranges must contain the same number of data points or Excel returns a #N/A error [1].
- Text, logical values, and empty cells inside a range are ignored, but cells holding zero are counted [1].
- If either range is empty, or if the standard deviation of either range is zero, CORREL returns a #DIV/0! error [1].

## Syntax

The correl formula takes two arguments, and both are required.

| Argument | Required? | Meaning |
|---|---|---|
| array1 | Required | The first range of cell values [1] |
| array2 | Required | The second range of cell values [1] |

The two ranges must have the same number of data points. If they differ in size, CORREL returns a #N/A error [1].

## How It Works

CORREL computes the Pearson correlation coefficient, also called Pearson's r. That coefficient is the ratio between the covariance of two variables and the product of their standard deviations, which normalizes the result to a value between -1 and 1 [2]. Because the formula standardizes the variables, changing the scale or units of measurement does not change the result [3].

The equation Excel uses is:

$$r = \frac{\sum (x_i - \bar{x})(y_i - \bar{y})}{\sqrt{\sum (x_i - \bar{x})^2 \sum (y_i - \bar{y})^2}}$$

Here $\bar{x}$ and $\bar{y}$ are the sample means, which you can get with `AVERAGE(array1)` and `AVERAGE(array2)` [1]. The coefficient is symmetric, so `CORREL(X, Y)` gives the same answer as `CORREL(Y, X)` [2].

One useful follow-up is the square of the coefficient, $r^2$. It represents the fraction of the variation in one variable that can be explained by the other. A correlation of 0.8, for example, means a linear regression between the two variables accounts for 64% of the variability in the data [3]. If you go on to fit a line, the [residual sum of squares](/blog/research-skills/residual-sum-of-squares-formula-and-example) tells you how far the actual points sit from that line.

## Worked Example

The table below tracks eight students, their hours studied, and their exam scores. Column D holds the correl formula, and columns E and F show how the same number can be rebuilt from covariance and standard deviations.

| | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Student | Hours Studied | Exam Score | CORREL Result | Manual Covariance | Manual Correlation |
| 2 | Ana | 2 | 65 | `=CORREL(B2:B9,C2:C9)` -> displays 1.00 | `=COVARIANCE.P(B2:B9,C2:C9)` -> displays 20.12 | `=E2/(STDEV.P(B2:B9)*STDEV.P(C2:C9))` -> displays 1.00 |
| 3 | Ben | 3 | 70 | | | |
| 4 | Cara | 4 | 72 | | | |
| 5 | Dan | 5 | 78 | | | |
| 6 | Eve | 6 | 82 | | | |
| 7 | Finn | 7 | 85 | | | |
| 8 | Gus | 8 | 88 | | | |
| 9 | Hana | 9 | 92 | | | |

Three steps produce the result.

1. `=CORREL(B2:B9,C2:C9)` in cell D2 returns the Pearson correlation coefficient between hours studied and exam score.
2. `=COVARIANCE.P(B2:B9,C2:C9)` in cell E2 returns the population covariance between the two ranges, which is 20.12.
3. `=E2/(STDEV.P(B2:B9)*STDEV.P(C2:C9))` in cell F2 divides that covariance by the product of the population standard deviations and reproduces the CORREL result.

The correlation here is about 0.996, which displays as 1.00 at two decimal places, a very strong positive relationship. Students who studied more hours tended to score higher. The manual calculation in column F confirms that CORREL is doing the same arithmetic as the covariance-and-standard-deviation route.

## More Examples

**Correlation with a negative relationship.** Suppose you have daily temperature in one column and heating cost in another. As temperature rises, heating cost falls, so you would expect a negative coefficient. The formula is unchanged: `=CORREL(A2:A30, B2:B30)`. A result near -1 means the two move in opposite directions [3].

**Correlation across two sheets.** Ranges do not have to sit on the same sheet. You can reference another sheet by name, for example `=CORREL(Sheet1!A2:A50, Sheet2!B2:B50)`. The two ranges still need the same number of data points [1].

**Correlation with a helper column.** If your raw data needs cleaning first, you can build a cleaned column with functions like [SUM](/blog/data-analysis/excel-sum-function-examples) for totals or [SUBTOTAL](/blog/data-analysis/excel-subtotal-function-formula) for filtered rows, then point CORREL at the cleaned ranges. Keeping the inputs in one place makes the formula easier to audit.

**Correlation inside a larger model.** When you build a dashboard, you may want to feed a correlation result into other calculations. Functions such as [OFFSET](/blog/data-analysis/excel-offset-function) and [INDIRECT](/blog/data-analysis/excel-indirect-function) let you build dynamic ranges, so the correl formula can adapt as your data grows. The [Excel formulas cheat sheet](/blog/data-analysis/excel-formulas-cheat-sheet) is a handy reference when you combine several functions in one cell.

## Errors and How to Fix Them

| Error | Cause | Fix |
|---|---|---|
| #N/A | array1 and array2 have a different number of data points [1] | Make both ranges the same size, or trim the longer one |
| #DIV/0! | Either range is empty, or the standard deviation of the values in a range equals zero [1] | Check that both ranges contain numbers and that the values actually vary |
| #VALUE! | An argument is not a valid range or reference | Pass cell ranges or arrays, not text strings |

The #DIV/0! case is the one people miss most often. If every value in one range is identical, the standard deviation is zero, and the coefficient is undefined. That is a mathematical fact, not a bug. A column of the same number repeated has no variation to correlate with anything.

## Common Mistakes

- **Mismatched range sizes.** If one range covers 20 rows and the other covers 19, you get #N/A [1]. Fix it by selecting both ranges with the same row count, or by using a table so the ranges resize together.
- **Forgetting that incomplete pairs are dropped.** If array1 has a blank or text where array2 has a number, CORREL drops that whole pair, so the coefficient is based on fewer observations than you might expect. Check how many complete pairs you have before you report the result.
- **Reading a high coefficient as proof of cause.** Correlation measures linear association only. A strong result does not mean one variable causes the other [2]. Fix it by treating the coefficient as a starting point and testing the relationship with other evidence.
- **Assuming the coefficient captures every relationship.** Pearson's r reflects linear correlation and ignores many other types of relationships [2]. Fix it by plotting the data before you trust the number.
- **Mixing up population and sample statistics.** `COVARIANCE.P` and `STDEV.P` pair with each other, and `COVARIANCE.S` and `STDEV.S` pair with each other. Mixing the two families gives a slightly different answer than CORREL. Fix it by keeping the .P or .S suffix consistent across the whole calculation.
- **Correlating two columns that both trend over time.** Two variables that both rise across the years will show a high coefficient even when they are unrelated. Fix it by checking whether a shared trend explains the result.

## Limitations

CORREL measures linear association only. It ignores curved relationships, so a perfect U-shaped pattern can return a coefficient near zero even though the two variables are strongly related [2]. It is also sensitive to outliers, since a single extreme pair of values can pull the coefficient up or down by a large amount. The coefficient has no units, which makes it useful for comparing relationships across different scales, but it also means the number tells you nothing about the size of the effect in practical terms [2].

A correlation coefficient is not evidence of causation. Two variables can move together because a third factor drives both, or by coincidence. The square of the coefficient, $r^2$, describes how much variation one variable explains in the other, but it does not tell you which variable influences which, or whether either influences the other at all [3].

## Frequently Asked Questions

### What is the difference between CORREL and PEARSON?

They return the same Pearson correlation coefficient. CORREL is the function most users reach for, and PEARSON is an older equivalent that takes the same two range arguments. If you see a workbook using PEARSON, you can swap in CORREL without changing the result.

### Why does CORREL return #DIV/0!?

Either one of your ranges is empty, or the standard deviation of the values in one range is zero [1]. The second case usually means every value in that range is identical. Check for a column that repeats the same number, or for a range that accidentally points at blank cells.

### Can CORREL handle text or blank cells?

Text, logical values, and empty cells inside a range are ignored, but cells containing zero are included [1]. That behavior is convenient when your data has labels mixed in, but it can also hide a problem. If a blank in one range lines up with a value in the other, CORREL drops that pair, so the result rests on fewer observations.

### Does the order of the two ranges matter?

No. The Pearson correlation coefficient is symmetric, so `CORREL(X, Y)` equals `CORREL(Y, X)` [2]. What does matter is that both ranges cover the same rows in the same order, so each pair of values belongs to the same observation.

### What correlation value is considered strong?

There is no universal cutoff, but a common rule of thumb treats values above about 0.7 or below about -0.7 as strong, values around 0.3 to 0.7 as moderate, and values near 0 as weak [1]. Always plot the data alongside the number, because the coefficient alone can mislead when the relationship is not linear.

## References

1. [CORREL function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/correl-function)
2. [Pearson correlation coefficient - Wikipedia](https://en.wikipedia.org/wiki/Pearson_correlation_coefficient)
3. [Correlation](http://www.stat.yale.edu/Courses/1997-98/101/correl.htm)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)

## Related Articles

- [Excel OFFSET Function: Syntax, Examples and How to Use It](/blog/data-analysis/excel-offset-function)
- [Excel RIGHT Function: Syntax, Examples and Uses](/blog/data-analysis/excel-right-function)
- [Excel INDIRECT Function: Syntax and Examples](/blog/data-analysis/excel-indirect-function)
- [Excel SUM Function: Syntax, Examples and Tips](/blog/data-analysis/excel-sum-function-examples)
- [Excel SUBTOTAL Function: Syntax, Formulas and Examples](/blog/data-analysis/excel-subtotal-function-formula)
- [Residual Sum of Squares: Formula and Example](/blog/research-skills/residual-sum-of-squares-formula-and-example)