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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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 Addins and then press on to Go…

An Addins 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

### 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.

## 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

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

Advanced Excel Exercises with Solutions PDF