How to Calculate Covariance Matrix in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

In statistical analysis, covariance is an analysis of the relationship between changes in one variable and changes in another. It’s a metric for determining how close two variables are to one another. We perform the analysis in Excel by creating a matrix in the columns and computing the covariances. In this tutorial, we will show you how to calculate the covariance matrix in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Steps to Calculate Covariance Matrix in Excel

Covariance refers to the measurement of how one variable defers to another. Clearly, it’s a necessary evaluation of the deviation between two variables. Furthermore, the variables do not have to be dependent on one another. The formula for calculating covariance is represented in the following image.

Steps to Calculate the Covariance Matrix in Excel

Xi = Data value of the first category

Yi = Data value of the second category

= Mean data Value of the first category

Ȳ = Mean data Value of the second category

n = Total number of data values

In the steps that follow, we’ll create two matrices with two categories each and use the covariance command in Excel to calculate the deviations. We’ll use the Data Analysis ribbon from the Data tab to do this.

Step 1: Apply Data Analysis Command in Excel

  • Click on the Data tab.
  • From the Analysis group, select the Data Analysis command.

Steps to Calculate the Covariance Matrix in Excel


Step 2: Select Covariance Option from Analysis Tool

  • From the Analysis Tools list, select the Covariance option.
  • Then, click OK.

Steps to Calculate the Covariance Matrix in Excel


Step 3: Select Range to Calculate Covariance Matrix in Excel

  • To calculate variance with Math, Science, and History, select the Input Range B4:D13 with the Header.
  • Select the Labels in first row box.

Steps to Calculate the Covariance Matrix in Excel

  • For Output Range, select any cell (B15).
  • Finally, click OK.

Steps to Calculate the Covariance Matrix in Excel

  • As a result, the covariances will appear as in the image shown below.

Steps to Calculate the Covariance Matrix in Excel


Similar Readings


How to Interpret Covariance Matrix in Excel

You can interpret the relationships between single and multiple variables once you’ve created the covariance matrix.

1. Covariance for a Single Variable

In the following image, we have highlighted the variances for each subject:

  • The variance of Math with its mean is 137.654321.
  • The variance of Science is 95.1111.
  • Finally, the variance of History is 51.5555.

Steps to Calculate the Covariance Matrix in Excel

2. Covariance for Multiple Variables

We have highlighted the following image with the values of the variances between two variables.

  • The variance value between Math and Science is 45.85185.
  • The variance value between Math and History is -27.3703.
  • And, the variance value between Science and History is 86.66667.

Steps to Calculate the Covariance Matrix in Excel

Positive Covariance

The presence of positive covariance indicates that the two variables are proportionate. When one variable rises, the other tends to rise with it. As in our example, the covariance between Math and Science is positive (45.85185), implying that students who perform well in Math also perform well in Science.

Negative Covariance

Negative covariance, in contrast to positive covariance, means that when one variable wants to increase, the other wants to decrease. The covariance between Math and History in our example covariance is negative (-27.3703), indicating that students who score higher in Math will score lower in History.


 Notes: 

If you cannot find the Data Analysis tool in your Data tab, you may need to activate the Data Analysis ToolPak first. To do so, follow the instructions below.

Steps:

  • Firstly, go to Home.
  • Then, click on Options.

Steps to Calculate the Covariance Matrix in Excel

  • From the Excel Options, select the Add-ins options.
  • Then, click the Analysis ToolPak option.
  • Finally, click OK.

Sample Data

  • Go to Developer tab.
  • After that, from the Add-ins, click on Excel Add-ins command.

Sample Data

  • Select the Analysis ToolPak from the list.
  • Then, click OK to add the Add-ins.

Sample Data

  • Consequently, you will find the Data Analysis command in your Data tab.

Sample Data

Conclusion

I hope this article has given you a tutorial about how to calculate the covariance matrix in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us and keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo