Create Correlation matrix in Excel or correlation table in Excel

Correlation is used to measure strength of the relationship between two variables. It can be positive, negative or zero. The correlation coefficient may take on any value between +1 and -1. A correlation matrix is a table showing correlation coefficients between sets of variables. Each random variable (Xi) in the table is correlated with each of the other values in the table (Xj). In this tutorial we will learn how to create correlation table or correlation matrix in Excel.

Let’s use the marks of three subjects (which is shown below) to compute Correlation matrix in excel.

Correlation matrix in Excel 1

 

Step 1: On the top right corner of the data tab click data analysis.

Correlation matrix in Excel 2

Note: if you can’t able to find the Data Analysis button? Click here to load the Analysis ToolPak add-in.

 

Step 2: Select Correlation and click ok.

Correlation matrix in Excel 3

 

Step 3: Click in the Input Range box and select the range A1:C10, select the “Labels in first row” tick box and output range, as shown below and click ok

Correlation matrix in Excel 4

The result will be

Correlation matrix in Excel 5

Which says,

  • subject A and Subject B are positively correlated and correlation coefficient is 0.2267.
  • subject B and Subject C are positively correlated and correlation coefficient is 0.4506
  • subject A and Subject C are negatively correlated and correlation coefficient is -0.6016

We have successfully created correlation matrix in excel

previous Correlation matrix in Excel                                                                                                                next small correlation matrix in Excel