The CORREL function gives you the correlation coefficient in one line

Excel calculates correlation coefficient using the CORREL function, which takes two ranges of numbers and returns a single value between -1 and 1. That value tells you how strongly two sets of data move together — whether they rise and fall in sync, move in opposite directions, or have no relationship at all.

The basic syntax is =CORREL(array1, array2). If you have monthly sales in cells B2:B13 and advertising spend in cells C2:C13, you would type =CORREL(B2:B13, C2:C13) into any empty cell, press Enter, and Excel returns the correlation coefficient.

The result ranges from -1 (perfect negative correlation — as one goes up, the other always goes down) through 0 (no relationship) to +1 (perfect positive correlation — they always move together). Most real-world data falls somewhere in between, like 0.67 or -0.42.

Key Takeaways

  • Use =CORREL(range1, range2) to find the correlation coefficient between two columns of numbers in Excel.
  • The result ranges from -1 to +1, where positive numbers mean the data moves together and negative numbers mean it moves in opposite directions.
  • Both ranges must have the same number of cells, and Excel ignores any cells that contain text or are empty.
  • Correlation measures relationship strength but does not prove that one thing causes another.

Setting up your data correctly

Correlation only works with numbers. If your columns contain headers, dates formatted as text, or blank cells, Excel either skips those cells or returns an error. The safest approach is to select only the cells with actual numeric values.

Both ranges must be the same length. If one column has 12 data points and the other has 11, Excel returns a #N/A error. Count your rows before you write the formula, or select carefully to make sure the ranges match.

If your data includes a header row — like "Month" in A1 and "Sales" in B1 — start your range at row 2, not row 1. For example, =CORREL(B2:B13, C2:C13) skips the headers and uses only the numbers below them.

Using CORREL with different data layouts

CORREL works the same way whether your data runs down columns or across rows. If you have months in row 1 and sales figures in row 2, you would write =CORREL(B2:M2, B3:M3) to correlate the two rows. The function does not care about direction — only that both ranges contain the same number of cells.

You can also calculate multiple correlations in a single spreadsheet. If you want to see how sales correlate with advertising, website traffic, and email campaigns, put each CORREL formula in its own cell. This lets you compare which factor has the strongest relationship with sales.

Understanding what the result means

A correlation of 0.85 means the two variables move together fairly consistently — when one goes up, the other usually goes up too. A correlation of -0.72 means they move in opposite directions most of the time. A correlation near 0, like 0.12, means there is little to no linear relationship.

The strength of the relationship depends on your field and context. In finance, a correlation above 0.7 is often considered strong. In social science, 0.5 might be considered moderate. There is no universal "good" correlation — it depends on what you are measuring and why.

One critical point: correlation does not mean causation. If website traffic and ice cream sales are highly correlated, it does not mean one causes the other. Both might rise during summer months. Always think about whether a relationship makes logical sense before drawing conclusions.

Common mistakes when calculating correlation

The most common error is including text or empty cells in your range. If column B has "Sales" as a header in B1, and you write =CORREL(B1:B13, C1:C13), Excel either ignores the text or returns an error depending on the version. Always start at the first data row, not the header row.

Another mistake is using ranges of different lengths. If you select B2:B15 (14 cells) and C2:C13 (12 cells), Excel returns #N/A. Count carefully or use the mouse to select both ranges at once — Excel will highlight them so you can see if they match.

A third error is misinterpreting a weak correlation as no relationship. A correlation of 0.3 is weak, but it is not zero. It means there is some relationship, just not a strong one. Do not assume weak correlation means the variables are unrelated.

Alternative: PEARSON function does the same thing

Excel also has a PEARSON function that calculates the Pearson correlation coefficient. The syntax is identical: =PEARSON(array1, array2). PEARSON and CORREL return the exact same result — they are functionally interchangeable. Use whichever name feels more natural to you.

Some older versions of Excel or other spreadsheet programs may recognize one name but not the other, so knowing both is useful if you share files across different systems or versions.

Frequently Asked Questions

What if my data has missing values or blanks?

Excel skips empty cells and cells containing text when calculating correlation. If you have blanks scattered through your data, CORREL still works — it just uses only the rows where both columns have numbers. If you want to exclude rows with any missing data, delete those rows first or use a different range that avoids them.

Can I calculate correlation for more than two columns at once?

CORREL only compares two ranges at a time. To see how three or more variables correlate with each other, use the Data Analysis Toolpak (in the Data menu under Analysis) and select Correlation. This creates a correlation matrix showing all pairs at once. If the Toolpak is not visible, go to File > Options > Add-ins and enable it.

Does correlation work with dates or time values?

Excel stores dates and times as numbers internally, so CORREL treats them as numbers. If you want to correlate dates with another variable, the formula works fine. However, make sure the dates are actually formatted as dates in Excel, not stored as text — text dates will cause errors.

What does a correlation of exactly 0 mean?

A correlation of 0 means there is no linear relationship between the two variables. They do not move together in any predictable way. This does not mean they are unrelated — they could have a non-linear relationship (like a curve instead of a straight line) that CORREL would not detect.

Should I use correlation to predict future values?

Correlation shows relationship strength, but it is not a prediction tool. If sales and advertising are correlated at 0.8, you know they move together historically — but correlation alone does not tell you what sales will be next month. For prediction, use regression analysis or other forecasting methods that build on correlation but go further.