Correlation Matrix in Excel

Correlation Matrix in Excel

The correlation matrix in excel summarizes the correlation data in a tabular form. It displays the correlation coefficients which measure the relationship between two or more variables. The “correlation” option of the “data analysis” tab helps create a correlation matrix.

Note: A matrix is a set of numbers arranged in rows and columnsRows And ColumnsA cell is the intersection of rows and columns. Rows and columns make the software that is called excel. The area of excel worksheet is divided into rows and columns and at any point in time, if we want to refer a particular location of this area, we need to refer a cell.read more.

The Explanation of Correlation

Correlation assesses the dependency of one variable on the other. It shows how the impact of an increase or a decrease in one variable affects the other. In multiple correlation, more than two variables are studied at the same time.

The correlation coefficient can be positive (+1), negative (-1), or zero (0).

The Characteristics of Correlation

The features of correlation are stated as follows:

Note: The correlation coefficient is calculated with the help of the CORREL function of Excel.

Correlation matrix in excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Correlation Matrix in Excel (wallstreetmojo.com)

How to Create a Correlation Matrix in Excel?

Let us consider some examples to understand the creation of a correlation matrix in Excel.

You can download this Correlation Matrix Excel Template here – Correlation Matrix Excel Template

Correlation Matrix With Analysis Toolpak

Below are the steps to add Analysis Toolpak in MS Excel

The Analysis Toolpak is an add-in option available under the Data tab of the Excel ribbon. The steps to add this option are listed as follows:

  • Click on “file” and select “options.”
enable macros in excel example 1.1
  • In “options,” select the “add-ins” button. Click on “go” displayed next to the dropdown of “manage.”
correleation matrix in excel example 4.2
  • Select the check box for Analysis Toolpak and click “Ok.”
correleation matrix in excel example 4.3
  • The Toolpak gets added to the Data tab as “data analysis” (under the “analysis” section).
correleation matrix in excel example 4.4

Steps to Create Correlation Matrix using Analysis Toolpak

Time needed: 3 minutes.

The steps to create a correlation matrix are listed as follows:

  1. Click on “data analysis” and select “correlation” in the pop-up window. Click “Ok.”

    correleation-matrix-in-excel-example-4.5

  2. The pop-up window titled “correlation” appears, as shown in the following image. Select the data range of the two variables in the “input range” field.

    correleation-matrix-in-excel-example-4.6

  3. Select the check box for “labels in first row.” This is selected if the first row contains the labels of the two variables.

    correleation-matrix-in-excel-example-4.7

  4. In “output range,” enter the cell number where you want the resulting table. Click “Ok.”

    correleation-matrix-in-excel-example-4.8

  5. The table showing correlation coefficients for variables A and B appears, as shown in the following image.

    correleation-matrix-in-excel-example-4.9

Correlation Matrix for Multiple Variables

Let us consider another example.

The steps to create a correlation matrix for multiple variables are listed as follows:

  • In an Excel sheet, enter the data for multiple variables, as shown in the following image.
correleation matrix in excel example 5.1
  • Click on “data analysis.”
Example 4.4
  • Select “correlation” in the “data analysis” pop-up window. Click “Ok.”
Example 4.5
  • The “correlation” pop-up window appears. In this, perform the following tasks:
    • Select the data range (A1:C7) of the three variables in the “input range” field.
    • Select the check box for “labels in first row” because the first row contains labels.
    • In “output range,” enter the cell number where you want the resulting table.
    • Click “Ok.”
Example 5.2
  • For the three variables A, B, and C, the correlation matrix appears in the range A9:D12.
Example 5.3

The Interpretation of the Correlation Matrix

The correlation matrix consists of the variable label in the first column (or row) and the correlation coefficients in the subsequent columns (or rows). To understand the matrix, the correlation coefficient corresponding to the intersection of the row and column must be read.

The findings of the table (in the previous example) are listed as follows:

  • The correlation coefficient for variables A and B is 0.97. This implies that these variables are positively correlated.
  • The correlation coefficient for variables B and C is -0.6. This implies that these variables are negatively correlated.
  • The correlation coefficient for variables A and C is -0.43. This implies that these variables are not correlated.

The relation between the variables A, B, and C is shown in the following graph.

correleation matrix in excel example 5.4

Frequently Asked Questions

What is a correlation matrix in Excel?

A correlation matrix helps study the interrelations between two or more variables. It shows the correlation coefficient between all possible pairs of variables. Every cell of the matrix consists of a correlation coefficient.

A correlation matrix is used in the analysis of multiple linear regression models. It is also used in combination with other statistical tools. The Excel correlation matrix can be created with the help of the Analysis ToolPak add-in.

How to read and interpret the correlation matrix in Excel?

The correlation coefficient at the intersection of a row and column shows the relationship between the corresponding variables.

The correlation matrix is interpreted in the following way:
• The positive correlation coefficient shows a direct relationship between the two variables. This implies that an increase in one variable is characterized by a proportional increase in the other.
• The negative correlation coefficient shows an inverse relationship between the two variables. This implies that an increase in one variable is characterized by a decrease in the other.
• If the row and column coordinates are the same, the output is 1. This implies that every variable perfectly correlates with itself.

Describe the significance of the correlation matrix in Excel.

A correlation matrix summarizes a large amount of data. The matrix is important where the purpose is to observe patterns in correlation coefficients of different variables.

The correlation matrix is a necessary input for performing advanced analyses like structural equation models, confirmatory factor analysis, linear regression, and exploratory factor analysis.

Key Takeaways
  • The correlation matrix of Excel displays the correlation coefficients in a tabular form.
  • The correlation assesses the dependency of one variable on the other.
  • If the correlation coefficient is “+1,” the two variables move in the same direction.           
  • If the correlation coefficient is “-1,” the two variables move in opposite directions.
  • If the correlation coefficient is “0,” the two variables are not dependent on each other.
  • The closer the correlation coefficient is to “+1” or “-1,” the stronger the relationship between the two variables.
  • To understand the correlation matrix, the correlation coefficient corresponding to the intersection of the row and column must be read.

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>