How to Find Correlation Coefficient in Excel
Use =CORREL(array1, array2) to get the Pearson correlation coefficient between two columns in Excel, or the Analysis ToolPak's Correlation tool when you have more than two variables.
Type =CORREL(A2:A20, B2:B20) into an empty cell, swapping in the two ranges you want to compare, and press Enter. Excel returns the Pearson correlation coefficient, a number between -1 and 1. Values near 1 or -1 mean the two columns move together closely; values near 0 mean they barely relate at all.
Working out how to find correlation coefficient in Excel takes one formula, and most people only ever need that one. The complication is not the maths — it is knowing which of Excel's three routes fits the data in front of you, and reading the answer correctly once you have it.
Before you start, make sure both columns contain the same number of numeric data points. Excel ignores text, logical values and blank cells in the ranges, but it counts zeros. If the two ranges end up with different counts, you get a #N/A error rather than a wrong answer, which is the behaviour you want.
Find the correlation coefficient in Excel with CORREL
1. Lay your two variables out in columns, one per variable, with the paired observations on the same rows. Row 5 of column A has to belong to the same subject as row 5 of column B, or the result is meaningless.
2. Click an empty cell outside the data — somewhere in column D, say.
3. Type =CORREL(, drag to select the first column of numbers, type a comma, drag to select the second column, then type ) and press Enter. The finished formula looks like =CORREL(A2:A20, B2:B20).
4. Read the result. Microsoft's own documentation describes the output as the correlation coefficient of the two ranges, where values close to +1 or -1 indicate a stronger relationship and values near 0 indicate little or none.
There is a second function, PEARSON, with identical syntax: =PEARSON(A2:A20, B2:B20). It returns the same Pearson product moment coefficient. Microsoft documents them as separate functions and describes PEARSON's arguments as the independent and dependent values, but for a given pair of ranges you can use either. CORREL is the more commonly used name.
The Analysis ToolPak route for three or more variables
CORREL compares exactly two ranges. If you have five columns and want every pairing at once, writing ten separate formulas is the slow way round. The Analysis ToolPak builds the whole matrix in one pass.
1. Load the add-in. On Windows, go to File > Options > Add-ins, choose Excel Add-ins in the Manage box, click Go, tick Analysis ToolPak, and click OK. On a Mac, open the Tools menu, choose Excel Add-ins, tick Analysis ToolPak, and click OK.
2. Go to the Data tab. A Data Analysis button now sits in the Analysis group at the right-hand end of the ribbon.
3. Click it, select Correlation from the list of analysis tools, and click OK.
4. Set Input Range to all the columns together, including the header row. Set Grouped By to Columns. Tick Labels in first row so the output is readable. Pick an output location, then click OK.
Excel writes out a matrix giving the coefficient for every pair of variables. Microsoft describes this tool as most useful when you have more than two measurement variables recorded on the same subjects.
One thing to know: the matrix is a block of static values, not formulas. Change the underlying data and the matrix does not update. You have to run the tool again.
What the correlation coefficient does not tell you
It does not measure causation. A coefficient of 0.9 says two columns rise and fall together; it says nothing about one causing the other, and the pairing may be driven by a third variable neither column records.
It only measures linear relationship. A variable that rises and then falls in a clean arc is strongly related to its pair, but Pearson's r can come out close to zero because no straight line fits it. Plotting the data as a scatter chart before you trust the number is worth the thirty seconds — if you want to chart the two series instead, the same selection logic applies as when you make a line graph in Excel.
It is also not the same thing as the R² value Excel shows on a chart trendline. For a straight-line trendline, R² is r squared, so taking its square root recovers the size of the coefficient but loses the sign — you cannot tell a strong positive relationship from a strong negative one that way. If you need the direction, use CORREL.
Common questions
Why am I getting #DIV/0! instead of a number? Microsoft's documentation gives two causes: one of the arrays is empty, or the standard deviation of its values is zero. The second case usually means a column where every value is identical, which gives Excel no variation to correlate against.
Which versions have CORREL? Microsoft lists it for Excel for Microsoft 365 on Windows and Mac, Excel 2024 on both, Excel 2021 on both, Excel 2019 and Excel 2016. The same version list applies to PEARSON.
Can I do this in the browser? The CORREL formula itself works the same way wherever you type it. The Analysis ToolPak is a different matter — Microsoft's instructions for enabling it describe only the desktop route through the Options dialog, so if you are working in a browser tab, write the formula rather than hunting for the Data Analysis button.
If the Data Analysis button isn't there
Check the Data tab first, at the far right of the ribbon, before assuming the add-in failed to load — it is easy to miss next to the larger buttons. If it genuinely is not there, reopen the Add-ins dialog and confirm Analysis ToolPak is still ticked; the setting can come unticked after an Office repair or a profile change.
If Analysis ToolPak is not listed in the dialog at all, Microsoft's instructions say to click Browse to locate it, and to accept the prompt to install it if Excel reports it is not currently installed. On a managed work machine that install may be blocked by your administrator, in which case the CORREL formula gets you the same coefficient without the add-in.
