Correlation matrix in excel is a way of summarizing the correlation data showing the relationship between two variables and each table in the correlation matrix shows us the relationship between two variables, to make a correlation matrix we can do it from the data analysis tab and from the correlation section.

**Correlation Matrix in Excel (Table of Contents)**

## Excel Correlation Matrix

A matrix is a set of numbers arranged in rows and columns in a structured format. Correlation is finding or measuring the dependency or the relationships between the variables. It shows how one variable is dependent on the other and the impact of rising or decline in one variable affects the other. More than two variables also can be used to measure the correlation and it is called multiple variable correlations. The resulting coefficients could be positive, negative or zero i.e. -1, +1 or 0.

- Positive Correlation is the one where the resulting coefficient is +1, which means both the variables are moving in the same direction.
- Negative Correlation is the one where the resulting coefficient is -1, which means the variables tend to move in opposite directions.
- Zero Correlation is where the resulting coefficient is 0 and the variables are not dependent on each other.

We can create Correlation Matrix in Excel using the Analysis Tookpak Excel Add-in which is readily available in the Excel.

**How to Create a Correlation Matrix in Excel?**

Let’s see some examples to understand how to create a correlation matrix in excel.

#### Example #1

Now let’s see how to find the correlation matrix in excel using the Analysis Toolpak in Excel.

4.9 (1,353 ratings)

Analysis Toolpak is an add-in option available in Excel under “DATA” tab in the ribbon.

If this option is not available, then add it from the add-ins list. To add,

- Click on “File” and select the “Options”.

- Under Options, select the “Add-Ins” tab then click on the “Go” button next to the Dropdown in Manage field.

- Check the box for Analysis Toolpak and hit OK.

The Toolpak will get added in the “Data” tab under “Analysis” section as the “Data Analysis”

- Now, to create a correlation matrix and to use the correlation excel function, click on the Data Analysis and select the Correlation in the Analysis tools pop-up window and hit OK.

A pop-up window appears asking for the input range.

- Select the data range of the variables in the input range field.

- Check the box for Labels in First Row (if you have the variables labels in the first row)

- Select the Output Range option and select/enter the cell number where you want to get the result table. Click OK.

- This is the result table of Correlation for Variable A & B.

#### Example #2

Let’s see an example of a correlation matrix in excel for multiple variables.

- Enter the data for multiple variables.

- Now, to use the correlation function, click on the Data Analysis and select the Correlation in the Analysis tools pop-up window and hit OK.

A pop-up window appears asking for the input range.

- Select the data range of the variables in the input range field.
- Check the box for Labels in First Row (if you have the variables labels in the first row)
- Select the Output Range option and select/enter the cell number where you want to get the result table
- Click OK.

- In this example, we have used three variables to find out the correlation matrix in excel. The range (A1: C7) is the data for the variables and the range (A9: D12) is the result table for correlation matrix in excel.

Here the variables are shown in the rows and columns. The result for the correlation between the variables should be read by checking the variable in the row and the variable in column adjacent to that row.

Findings of the table:

- The result for Variables A & B is 0.97 which means they are positively correlated.
- The result for Variables B & C is -0.6 which means they are negatively correlated.
- Variables A & C are not correlated as the result is -0.4

The relation between the variables can be seen clearly in the graph as follows.

**Things to Remember about Correlation Matrix in Excel**

- Data for any number of variables can be added to the existing table and range should be adjusted to find out the correlation
- The correlation shows the cause and effect relationship between the factors
- The results close to the correlations range will determine the dependency/relationship of the variables.
- The correlation coefficient is calculated using mathematic calculation, but it always doesn’t indicate that there is a relation between the variables in reality though the result shows it has.

### Recommended Articles

This has been a guide to Excel Correlation Matrix. Here we discuss how to create a correlation matrix in excel with examples and downloadable excel templates. You may also look at these useful functions in excel –

- Excel Inverse Matrix
- Examples of Population Variance Formula
- Coefficient of Variation Formula | Explanation
- Regression Formula
- What is Covariance Formula?
- Enable Macros in Excel
- Correlation vs Covariance Differences
- Use Descriptive Statistics in Excel
- Create Error Bars in Excel
- Regression vs ANOVA Differences

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion