Written and maintained by CASRAI Editorial Board
Last updated
To find the correlation coefficient (the r value) for two columns in Excel, type =CORREL(array1, array2) into a blank cell, with each argument a column of numbers of the same length. For three or more variables, load the Analysis ToolPak and use its Correlation tool, which returns a matrix of coefficients. This guide covers both routes, a worked example you can check by hand, how to read the result, and what the error messages mean.
What the correlation coefficient is used for in research
The correlation coefficient summarises how closely two measured variables move together, on a scale where values closer to +1 or -1 indicate a stronger positive or negative relationship and values near 0 indicate no or weak correlation (Microsoft Support, CORREL function). Researchers use it to screen variables before modelling, to check agreement between two measurements, and to report effect size. It describes association only; see correlation vs causation before interpreting any value causally.
Access: functions and the Analysis ToolPak
CORREL is a built-in worksheet function, so nothing needs installing. The Correlation tool comes from the Analysis ToolPak add-in. Microsoft documents the add-in for Excel for Microsoft 365, Excel 2024 and Excel 2021 (Windows and Mac).
Load the Analysis ToolPak
- Windows: choose File → Options → Add-Ins. In the Manage box select Excel Add-ins and choose Go. Tick Analysis ToolPak and choose OK. If it is not listed, use Browse and accept any install prompt.
- Mac: choose Tools → Excel Add-ins, tick Analysis ToolPak and choose OK. Use Browse if it is absent, then restart Excel. Microsoft states that the Data Analysis command is then available on the Data tab.
How to find the correlation coefficient in Excel with CORREL
This answers “how do I calculate the correlation between two columns in Excel” and the many phrasings of it (calculate, compute, find, on Excel or in Excel): they all describe the same function.
- Put the two variables in two columns with one row per paired observation, for example A2:A7 and B2:B7.
- Click an empty cell and type
=CORREL(A2:A7,B2:B7), then press Enter. - Alternative route from the menus: Formulas tab → Insert Function (fx) → category Statistical → CORREL → OK, then fill in Array 1 and Array 2 and choose OK (UConn Research Basics).
The syntax is CORREL(array1, array2); both arguments are required and are ranges of cell values (Microsoft Support).
Worked example you can verify by hand
Illustrative data invented for this page; not from any study. Suppose A2:A7 holds 1, 2, 3, 4, 5, 6 and B2:B7 holds 2, 4, 5, 4, 5, 7.
- Mean of x = 3.5; mean of y = 4.5.
- Sum of products of deviations = 13.5; sum of squared x deviations = 17.5; sum of squared y deviations = 13.5.
- r = 13.5 / √(17.5 × 13.5) = 13.5 / 15.370 ≈ 0.878.
=CORREL(A2:A7,B2:B7) should display approximately 0.878 (Excel will show more decimals). If your cell shows something else, check that the ranges are aligned row for row.
Several variables: the Correlation tool (Data Analysis)
This is the route for “how to calculate correlation in Excel using Data Analysis” and for a correlation matrix. Microsoft describes the Correlation tool as particularly useful when there are more than two measurement variables; it produces an output table, a correlation matrix, showing the value of CORREL for each pair. After loading the ToolPak, open the Data Analysis command, choose Correlation, and follow the dialog to point it at your columns. To interpret a matrix, see how to read a correlation matrix.
| Situation | Use | Source basis |
|---|---|---|
| One pair of variables | CORREL function | Microsoft Support, CORREL |
| More than two variables | Correlation tool (matrix) | Microsoft Support, Analysis ToolPak |
Output and how to read it
How do I find the r value in Excel?
The number CORREL returns is the r value. It is the same quantity the Correlation tool shows in each cell of its matrix.
What is the formula to find the correlation coefficient?
Excel’s documentation states that the CORREL equation uses the sample means AVERAGE(array1) and AVERAGE(array2). In the worked example above, this is the computation shown: deviations from each mean, multiplied pairwise and summed, divided by the square root of the product of the two sums of squared deviations. For the symbolic formula and notation, see the formula for correlation analysis.
What is a 0.7 correlation coefficient?
0.7 is a positive correlation: closer to +1 than to 0, so the relationship is stronger than a value near 0 and weaker than 1. Microsoft’s guidance is only that values closer to +1 or -1 mean stronger correlation; any named cutoff such as “strong” or “moderate” is a convention that varies by field, so state which convention you follow. See strong positive correlation for how thresholds are described.
Chart the pair
Always plot the data as well. Select the two columns, then Insert tab → scatter icon → top-left option (UConn Research Basics). See how to make a scatter plot in Excel and scatterplot interpretation. A relationship that is curved or ranked rather than linear is better handled with Spearman correlation.
Troubleshooting
| Symptom | Cause per Microsoft Support |
|---|---|
| #N/A | The two arrays have a different number of data points. |
| #DIV/0! | An array is empty, or the standard deviation of the values equals zero (for example, every value in one column is identical). |
| Result seems to ignore some rows | Text, logical values and empty cells in an array are ignored; cells containing zero are included. |
Because text and blanks are ignored, check that your two columns still line up as pairs after any missing entries.
CORREL or PEARSON?
Excel also has a PEARSON function with the same two-argument form, PEARSON(array1, array2); Microsoft labels array1 “independent values” and array2 “dependent values”, and ignores text, logical values and empty cells while including zeros. Its support page returns #N/A for arrays of different sizes or empty arrays. This guide uses CORREL, which Microsoft’s own page documents with a worked example.
Alternatives
If you need more than a single coefficient, the site’s guide to Excel statistics functions covers related functions, and the linear regression calculator guide covers fitting a line through the same pair of columns.
FAQ
How do I calculate the correlation between two columns in Excel?
Use =CORREL(range1,range2) with two equal-length numeric ranges.
Do I need the Analysis ToolPak?
Not for one pair. It is useful when you have more than two variables and want a matrix.
Why does CORREL return #N/A?
The two ranges have different numbers of data points.
Sources
- Microsoft Support, CORREL function (read 2026-11-07).
- Microsoft Support, PEARSON function (read 2026-11-07).
- Microsoft Support, Load the Analysis ToolPak in Excel (read 2026-11-07).
- Microsoft Support, Use the Analysis ToolPak to perform complex data analysis (read 2026-11-07).
- UConn Research Basics, Using Excel to Calculate and Graph Correlation Data (read 2026-11-07).
