Written and maintained by CASRAI Editorial Board
Published
On this page
To build a correlation matrix in Excel, put each variable in its own column with a header, open Data › Data Analysis, choose Correlation, select the whole block of columns as the input range and pick an output location. Excel then returns a square table in which every cell is the Pearson correlation coefficient for one pair of variables, with values from −1 to +1. If Data Analysis is missing from the Data tab, load the Analysis ToolPak add-in first; this guide covers that step, the formula alternative, how to read the result and how to colour it as a heatmap.
What a correlation matrix is used for in research
A correlation matrix reports the pairwise association between every pair of numeric variables in a dataset at once. Researchers use it as an early screening step: to see which measures move together, to spot variables that are nearly duplicates of one another before fitting a regression, and to produce the correlation table that many papers report in their descriptive statistics. Microsoft describes the Analysis ToolPak Correlation tool as particularly useful when there are more than two measurement variables, and says its output is a correlation matrix that shows the value of CORREL (or PEARSON) applied to each possible pair of measurement variables.
That definition matters for interpretation. The matrix is not a new statistic: it is the same Pearson coefficient you would get from the CORREL function, repeated for every pair. If you only need one pair, the single-coefficient route in how to find a correlation coefficient in Excel is faster. The matrix route earns its keep from three variables upward.
Install or access the Analysis ToolPak
The Correlation tool lives in the Analysis ToolPak, an add-in that ships with desktop Excel but is not always switched on. Microsoft documents the tools as available from Data Analysis on the Data tab, and its guidance applies to Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel 2019 and Excel 2016. The ToolPak is not documented for Excel for the web, so use the formula method below if you work only in a browser. The add-in is covered in more depth in the Excel ToolPak guide.
Load the add-in on Windows
- Choose File › Options › Add-Ins.
- In the Manage box, select Excel Add-ins and choose Go.
- In the Add-ins available box, tick Analysis ToolPak and choose OK. If it is not listed, use Browse; if Excel says the add-in is not installed, choose Yes to install it.
Load the add-in on Mac
- Choose Tools › Excel Add-ins.
- Tick Analysis ToolPak and choose OK.
- Quit and restart Excel. Data Analysis then appears on the Data tab.
Build the correlation matrix with Data Analysis (step by step)
This is the fastest route for a research dataset with several variables. Lay the data out with one variable per column, one row per participant or observation, and a short header in the first row.
- Check that every column you want to include is numeric. Microsoft’s CORREL documentation notes that text, logical values and empty cells in a reference are ignored, while zeros are included, so a stray text entry or a placeholder zero can silently change a coefficient.
- Select Data › Data Analysis, choose Correlation and confirm.
- In the dialog, set the input range to the block of columns you want correlated, tell Excel that the variables are arranged in columns, and tick the labels option if your first row holds headings.
- Choose where the result goes: a cell range on the current sheet, a new sheet or a new workbook.
- Choose OK. Excel writes a table whose rows and columns are your variables.
The result is a static table of values, not a live formula. If you change the source data, run the tool again. For a result that updates, use the formula method in the next section.
Build the matrix with CORREL formulas
The CORREL(array1, array2) function takes two required arguments and returns the correlation coefficient of the two cell ranges. To build a matrix from it, put the variable names across the top row and down the left column of an empty block, then enter a CORREL formula in the first cell that points at the data column for that row and the data column for that header, using absolute references for the data ranges and a relative selector so that you can fill the formula across and down. Because the formula is live, the matrix refreshes when the data change, and it also works in Excel for the web, where the ToolPak is not documented.
Two conditions from Microsoft’s documentation apply to every cell of a hand-built matrix. If the two arrays have different numbers of data points, CORREL returns #N/A. If an array is empty, or the standard deviation of its values is zero (a constant column), it returns #DIV/0!. A single constant column therefore produces an error in its whole row and column of the matrix.
The covariance sibling
The same dialog list contains a Covariance tool. Microsoft states that it computes COVARIANCE.P for each pair of measurement variables, giving a matrix of the same shape; its diagonal holds each variable’s population variance. A covariance matrix is in the units of the variables, whereas the correlation matrix is unit-free and bounded. See what a covariance matrix is for how the two relate.
How to read the output
Every off-diagonal cell is the coefficient for the pair named by its row and its column. Because the correlation of A with B equals the correlation of B with A, the matrix is symmetric, and each distinct pair appears twice. The diagonal compares each variable with itself, so it is always 1.
What is a Pearson correlation matrix?
A Pearson correlation matrix is a table of Pearson product-moment coefficients, one for every pair of variables. Excel’s Correlation tool and the CORREL and PEARSON functions all produce Pearson coefficients, so the Excel matrix is a Pearson matrix. It measures the strength and direction of a linear association.
What is a negative correlation?
A negative correlation is a coefficient below zero: as one variable tends to increase, the other tends to decrease. Coefficients run from −1 to +1, with 0 meaning no linear association (Schober, Boer and Schwarte, 2018). A value near −1 is a strong inverse linear relationship; a value near 0 is no linear relationship, which is not the same as no relationship at all, as a curved relationship can give a coefficient near zero. A coefficient also never shows that one variable causes the other.
When to use Pearson vs Spearman
Use Pearson when both variables are measured on a numeric scale and the data are approximately bivariate normal; use Spearman when the data are ordinal, when the relationship is monotonic rather than linear, or when outliers are relevant to the question. That is the decision rule set out by Schober, Boer and Schwarte (2018, Anesthesia & Analgesia 126(5):1763-1768), who also note that both coefficients span −1 to +1.
Can I use both Spearman and Pearson?
Yes. Nothing prevents reporting both for the same variables, and a large gap between them is itself informative: it suggests outliers or a curved monotonic relationship are driving the Pearson value. State in your methods which one is the primary analysis before you look at the results, so the choice is not made after seeing the numbers.
When not to use Pearson correlation
Avoid Pearson as the primary measure when a variable is ordinal (for example a rating scale treated as ranks), when the plot of the pair is clearly curved, or when a few extreme values dominate the scatter. Excel’s Correlation tool has no Spearman option, so in those cases use the rank workaround below or a different tool.
How to get a Spearman matrix in Excel
A Spearman coefficient is the Pearson coefficient computed on ranks. To build a Spearman matrix, create a second block of columns in which each value is replaced by its rank within its variable, then run the Correlation tool or CORREL formulas on the rank block. Use RANK.AVG(number, ref, [order]), which Microsoft documents as returning the average rank when more than one value has the same rank; that matches the usual treatment of ties. The ranking direction (the optional order argument) must be the same for every column. The single-pair version is explained in how to calculate Spearman correlation.
How to show correlation in an Excel chart
The quickest way to chart a correlation matrix is to turn it into a colour-scale heatmap. Select the cells holding the coefficients, then on the Home tab go to Styles › Conditional Formatting › Color Scales and pick a two-colour or three-colour scale; the top colour in the preview is applied to the higher values. Microsoft documents the same path in Excel for the web. A three-colour scale reads best for correlations because it lets the middle colour mark values near zero and the two ends mark strong negative and strong positive coefficients.
Select only the coefficient cells, not the header row or column, so the scale is set from the coefficients. Because the diagonal is always 1, you may prefer to exclude it so it does not set the top of the scale for every other cell. For one pair, a scatter chart (Insert › Charts › Scatter) with a trendline shows what the coefficient summarises; check it before relying on any single value in the matrix.
Troubleshooting
- Data Analysis is not on the Data tab. Load the Analysis ToolPak (see above) and restart Excel on Mac. Excel for the web does not document the add-in; use CORREL formulas.
- #N/A in a cell. CORREL returns #N/A when the two arrays have different numbers of data points. Check that every selected range has the same number of rows.
- #DIV/0! in a row and column. An empty range or a column in which every value is identical has zero standard deviation. Remove that variable or confirm it is not constant by mistake.
- A coefficient looks wrong. Text, logical values and blanks in a range are ignored while zeros count. A missing value entered as 0 will pull coefficients toward the wrong value; leave missing values empty rather than typing zero.
- The matrix did not update. The ToolPak output is a fixed table. Re-run it after editing the data, or switch to CORREL formulas.
Free alternatives
Excel is licensed software. If you need a free route to the same matrix, these open-source tools produce one from a table of numeric columns; the data table at the end of this page compares the Excel features used above.
| Tool | Cost model | How to get the matrix | Rank-based option | More detail |
|---|---|---|---|---|
| R | Free, open source | Call cor() on a data frame of numeric columns |
Set the method argument to spearman | Correlation matrix in R |
| Python with pandas | Free, open source | Call DataFrame.corr() |
Set the method argument to spearman | pandas corr |
Frequently asked questions
How do I make a correlation matrix in Excel?
Put each variable in a column with a header, open Data › Data Analysis, choose Correlation, select the columns as the input range, choose an output location and confirm. If Data Analysis is not shown, load the Analysis ToolPak add-in first.
How do I show correlation in an Excel chart?
Format the matrix as a heatmap with Home › Styles › Conditional Formatting › Color Scales, or draw a scatter chart for a single pair of variables.
What is a negative correlation?
A coefficient below zero, meaning one variable tends to fall as the other rises. The scale runs from −1 to +1, and values close to −1 indicate a strong inverse linear association.
What is a Pearson correlation matrix?
A table of Pearson coefficients for every pair of variables. Excel’s Correlation tool returns this, as it applies CORREL (or PEARSON) to each pair.
When should I use Pearson vs Spearman?
Pearson for approximately bivariate normal numeric data; Spearman for ordinal data, monotonic relationships or when outliers matter.
Can I use both Spearman and Pearson correlation?
Yes. Report both if they answer different questions, and name the primary one in advance.
When not to use Pearson correlation?
When the data are ordinal, the relationship is curved, or outliers dominate. Use a rank-based coefficient instead.
Sources
- Microsoft Support, Use the Analysis ToolPak to perform complex data analysis, read 2026-11-14.
- Microsoft Support, Load the Analysis ToolPak in Excel, read 2026-11-14.
- Microsoft Support, CORREL function, read 2026-11-14.
- Microsoft Support, RANK.AVG function, read 2026-11-14.
- Microsoft Support, Use conditional formatting to highlight information, read 2026-11-14.
- Schober P, Boer C, Schwarte LA. Correlation coefficients: appropriate use and interpretation. Anesthesia & Analgesia 2018;126(5):1763-1768. doi:10.1213/ANE.0000000000002864.
Data: Excel features for building a correlation matrix, as documented by Microsoft
Compares the Excel tools and functions used to build and display a correlation matrix: what each returns, where it is found and the documented error or tie behaviour. Compiled from Microsoft Support pages for Excel.
| Excel feature | What it returns | Where it is found or its syntax | Documented behaviour to know |
|---|---|---|---|
| Correlation (Analysis ToolPak) | Matrix of CORREL (or PEARSON) values for every pair of variables | Data tab, Data Analysis | Recommended for more than two measurement variables |
| Covariance (Analysis ToolPak) | Matrix of COVARIANCE.P values for every pair of variables | Data tab, Data Analysis | Diagonal holds the population variance of each variable |
| CORREL function | Correlation coefficient of two ranges | CORREL(array1, array2) | #N/A if different number of data points; #DIV/0! if an array is empty or its standard deviation is zero; text, logical and empty cells ignored, zeros included |
| RANK.AVG function | Rank of a number in a list | RANK.AVG(number, ref, [order]) | Average rank returned when more than one value has the same rank; nonnumeric values in ref ignored |
| Color Scales (conditional formatting) | Colour-coded cells | Home tab, Styles, Conditional Formatting, Color Scales | Choose a two-colour or three-colour scale; the top colour applies to higher values |
Sources
- Use the Analysis ToolPak to perform complex data analysis (accessed 2026-11-14)
- CORREL function (accessed 2026-11-14)
- RANK.AVG function (accessed 2026-11-14)
- Use conditional formatting to highlight information (accessed 2026-11-14)
Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.







