The CORREL function gives you a single number showing how two sets of data move together

Excel's CORREL function calculates correlation coefficient in one line. The result is a number between −1 and 1 that tells you whether two columns of data move in the same direction, opposite directions, or have no relationship at all. A result near 1 means they move together closely. A result near −1 means they move in opposite directions. A result near 0 means there is no clear pattern.

The basic syntax is =CORREL(array1, array2). You pick two columns of numbers, and Excel returns the correlation coefficient. This is useful when you have paired data — like monthly sales and monthly advertising spend, or temperature and ice cream sales — and you want to know if they are related.

Key Takeaways

  • The CORREL function takes two columns of numbers and returns a single value between −1 and 1 that describes their relationship.
  • A correlation near 1 means the data moves together; near −1 means they move opposite; near 0 means no clear relationship.
  • Both columns must have the same number of rows, and both must contain only numbers — not text or blank cells.
  • Correlation does not mean one thing causes the other; it only measures whether they move together.

Setting up your data in Excel

Arrange your data in two columns with the same number of rows. Put one variable in column A and the other in column B. For example, if you are measuring the relationship between hours studied and test scores, put hours in column A (rows 2 through 31) and scores in column B (rows 2 through 31). Leave row 1 for headers if you want.

Make sure every cell in both columns contains a number. If any cell is blank or contains text, Excel will either skip that row or return an error. If you have missing data, delete the entire row or move it to a separate area so the two columns line up exactly.

Writing the CORREL formula

Click on an empty cell where you want the result to appear. Type =CORREL(A2:A31,B2:B31), replacing A2:A31 and B2:B31 with the actual ranges of your data. Press Enter. Excel calculates the correlation coefficient and displays it in that cell.

If your data is in different columns or rows, adjust the cell references. For instance, if one variable is in column C and the other in column E, use =CORREL(C2:C50,E2:E50). The order does not matter — CORREL(A,B) gives the same result as CORREL(B,A).

Understanding what the number means

A correlation of 0.85 means the two variables move together fairly closely. A correlation of −0.72 means they move in opposite directions fairly closely. A correlation of 0.15 means there is almost no relationship — the data points scatter randomly.

The strength of the relationship depends on your field and context. In social science, a correlation above 0.5 or below −0.5 is often considered moderate. In manufacturing or engineering, you might expect much higher correlations (0.9 or above) before concluding that two things are truly linked. A correlation of 0.3 might be meaningful in one context and noise in another.

Remember that correlation measures association, not causation. If ice cream sales and drowning deaths are correlated, it is because both rise in summer — not because ice cream causes drowning. Always think about whether a relationship makes logical sense before acting on it.

Common mistakes that break the formula

Mixing text and numbers in the same column causes errors. If column A has "Hours Studied" as a header and then numbers, include only the number cells in your range: A2:A31, not A1:A31. If you include the header, Excel either skips it or returns an error.

Unequal column lengths also break the calculation. If column A has 30 rows of data and column B has 25, Excel will either return an error or calculate based only on the overlapping rows. Count your rows carefully and make sure both columns have the same number of data points.

Blank cells in the middle of your data are treated as zero by some functions, but CORREL skips them entirely. If you have gaps, either delete the rows with missing data or move them elsewhere so your ranges are continuous.

Using PEARSON as an alternative

Excel also has a PEARSON function that does exactly the same thing as CORREL. The syntax is =PEARSON(array1, array2), and it returns the same result. PEARSON is the older name for the same calculation (Pearson's correlation coefficient). You can use either one — they are interchangeable.

Some people use PEARSON because it is the formal statistical name. Others use CORREL because it is shorter to type. Neither is more correct. If you see a spreadsheet using PEARSON, you now know it is calculating the same thing you would get from CORREL.

When correlation is not enough

A single correlation number tells you whether two variables move together, but it does not tell you how strong that relationship is in a practical sense or whether it is statistically meaningful. If you have only five data points, a correlation of 0.9 might be coincidence. If you have 500 data points, a correlation of 0.3 might be real.

For more rigorous analysis, you may want to calculate the R-squared value (which is correlation squared) or run a regression to see how much of one variable's change is explained by the other. Excel has tools for this — the Data Analysis Toolpak includes regression functions — but they are beyond what CORREL alone provides. For most basic questions about whether two things move together, CORREL is enough.

Frequently Asked Questions

What if my correlation result is exactly 0?

A correlation of exactly 0 means there is no linear relationship between the two variables. They do not move together or in opposite directions in any predictable way. This does not mean they are unrelated — there could be a curved or non-linear relationship that CORREL does not detect.

Can correlation be higher than 1 or lower than −1?

No. Correlation is always between −1 and 1. If you see a result outside this range, something is wrong with your data or formula. Check that both columns contain only numbers and that the ranges are correct.

Do I need to sort my data before calculating correlation?

No. CORREL works on the data in the order it appears. Sorting changes the order of the rows, which would break the pairing between your two variables. Keep the data in its original order — each row should represent one observation with both variables measured for that same observation.

What does a negative correlation mean?

A negative correlation means the two variables move in opposite directions. If one goes up, the other tends to go down. For example, the price of a product and the quantity sold often have a negative correlation — as price rises, fewer people buy it.

Can I use CORREL with more than two columns at once?

No. CORREL takes exactly two columns and returns one number. If you want to see how three or more variables relate to each other, you have to run CORREL multiple times — once for each pair. For a full picture of relationships among many variables, the Data Analysis Toolpak's correlation matrix tool is faster.