## Excel Correlation Matrix

Correlation matrix in excelis 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.

### Explanation

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.

**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.

Analysis Toolpak is an add-in option available in Excel under the “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 the 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. The range (A1: C7) is the data for the variables and the range (A9: D12) is the result table for the correlation matrix.

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 the 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**

- 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?
- Correlation vs Covariance Differences

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