Naimul Hasan Arif
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.
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.
An Excel Options box will appear.
- Select Add–ins and then press on to Go…
An Add–ins box will come on the screen.
- Choose Analysis ToolPak.
- Hit OK.
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.
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.
We can also change the Format according to our choice.
Read More: How to Find Correlation between Two Variables in Excel
Similar Readings
- How to Calculate Cross Correlation in Excel (2 Quick Ways)
- Create a Training Matrix in Excel (3 Easy Methods)
- How to Calculate Autocorrelation in Excel (2 Ways)
- Do Matrix Multiplication in Excel (5 Examples)
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.
- 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.
- Use Fill Handle to AutoFill the formula to the left and right to make a correlation matrix.
Read More: How to Do Correlation in Excel (3 Easy Methods)
Practice Section
For further expertise, you can practice here.
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
- How to Create Traceability Matrix in Excel
- Interpret Correlation Table in Excel (A Complete Guideline)
- How to Multiply 3 Matrices in Excel (2 Easy Methods)
- Make Correlation Graph in Excel (with Easy Steps)
- How to Create a Risk Matrix in Excel (With Easy Steps)
- Calculate Covariance Matrix in Excel (with Easy Steps)
