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.
Step 1: On the top right corner of the data tab click data analysis.
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.
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
The result will be
- 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