How to Make a Correlation Matrix in Excel (2 Handy Approaches)

A Correlation Matrix is nothing but a table that represents the related coefficients for different variables. I will try to show some suitable ways to make a correlation matrix in Excel.

For more simplification, I am going to use a Dataset containing the scores in Fluid Mechanics, Solid Mechanics, and Electronics subjects and make a correlation matrix in Excel among them.

How to Make a Correlation Matrix in Excel


Download Practice Workbook


2 Handy Approaches to Make a Correlation Matrix in Excel

1. Applying Data Analysis Feature to Make a Correlation Matrix

Data Analysis Feature is one of the coolest ways to make a correlation matrix in Excel.

Steps:

  • First of all, get the Analysis Tool in Excel.
  • Go to the  File from the ribbon.

  • Then, choose Options.

How to Make a Correlation Matrix in Excel

An Excel Options box will appear.

  • Select Addins and then press on to Go…

An Addins box will come on the screen.

  • Choose Analysis ToolPak.
  • Hit OK.

How to Make a Correlation Matrix in Excel

Then, Click on the Data to check the Data Analysis feature.

  • Click on Data Analysis.

 

Then, The Data Analysis box will appear.

  • Pick Correlation and press OK.

How to Make a Correlation Matrix in Excel

Next, The Correlation box will come on screen.

  • Input the Range for Correlation. I have selected  B4:D9 area.
  • From the Grouped By options, select the pattern of your data. I have selected Columns according to my Dataset.
  • Click on the  Labels in first row box.
  • Choose your Output options. I have selected B11 cell on the same worksheet to have the output.
  • Finally, press OK.

 

We will have our desired output on the selected cell.

How to Make a Correlation Matrix in Excel

We can also change the Format  according to our choice.

Read More: How to Find Correlation between Two Variables in Excel


Similar Readings


2. Using CORREL and OFFSET Functions to Make a Correlation Matrix in Excel

Another way to make a correlation matrix in Excel is the use of the CORREL Function and OFFSET Function.
Steps:

  • Select a cell to make a correlation matrix after giving a suitable format. Here, I selected cell C12.

How to Make a Correlation Matrix in Excel

  • Input the following formula:
=CORREL(OFFSET($B$5:$B$9, 0, ROWS($4:4)-1), OFFSET($B$5:$B$9, 0, COLUMNS($B:B)-1))

Here, I used the OFFSET Function to specify the locations of the data and the CORREL Function to find out the correlation between them.

  • Press ENTER. We will have the correlated value.

How to Make a Correlation Matrix in Excel

Read More: How to Do Correlation in Excel (3 Easy Methods)


Practice Section

For further expertise, you can practice here.

How to Make a Correlation Matrix in Excel


Conclusion

I have tried to elaborate as simple as possible to show how to make a correlation matrix in Excel in two different ways. I hope it will be helpful for Excel users. For any more questions, comment below.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo