Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How To Make A Correlation Matrix In Excel

A matrix is a set of numbers arranged in rows and columns in a specific format. We can construct a correlation matrix to measure dependency or relationships between two or more variables. In this tutorial, we will learn how to create a correlation matrix for two variables as well as multiple variable scenarios. 

Figure 1 – How to create a correlation matrix in excel

Excel Correlation Matrix

The correlation matrix will show how one variable is dependent on the other variable. The resulting coefficients can be positive, negative, or zero. i.e., -1, +1 or 0. This means;

  • “+1” – Positive correlation coefficient resulting when both variables are moving in the same direction
  • “-1” – Negative Correlation coefficient resulting when the variables are moving in oppositive directions
  • “0” or blank cell – Zero Correlation Coefficient when variables are not dependent on each other. 

Correlation Matrix using Data Analysis

We need to enable the Analysis ToolPak to do the Correlation Matrix. We will go to File and select Options.  

Figure 2 – Making Excel Correlation Matrix. 

  • In the Add-Ins window, we will choose Excel Add-Ins from the Manage field and click Go

Figure 3 – Activating the Analysis ToolPak for creating a correlation matrix in excel

  • Next, we will mark the option for Analysis ToolPak and click OK. 

Figure 4 – Activating Analysis ToolPak for correlation in excel 2016

Create Correlation Matrix using two variables

  • We will prepare our data table. 

Figure 5 – How to make a correlation matrix in excel

  • We will go to the Data Tab and select Data Analysis.

v Figure 6 – Create Correlation Matrix in Excel 

  • In the Data Analysis pop-up window, we will select Correlation and select OK.

Figure 7 – Excel Correlation Matrix

    • In the Correlation dialog box, 
      • we will select our data range 

 

  • We will mark label in first row

 

    • Next, we will select the Output range and finally select OK. 

Figure 8 – How to do a correlation matrix in excel 

  • We will have this result

Figure 9 – How to Calculate a correlation matrix in excel 

Correlation Matrix using multiple variables

  • We will prepare our data table. 

Figure 10 – How to do a correlation test in excel

  • We will go to the Data Tab and select Data Analysis.

Figure 11 – Enable Data analysis for excel correlation matrix

  • In the Data Analysis pop-up window, we will select Correlation and select OK.

Figure 12 – Enable Correlation matrix in Excel

    • In the Correlation dialog box, 
      • We will select our data range 

 

  • We will mark label in first row

 

  •  
  • Next, we will select the Output range and finally select OK.

Figure 13 – How to calculate the correlation matrix in excel

  • We will have this result

Figure 14 – How to make a correlation matrix in excel 

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc