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.

** X_{i}** = Data value of the first category

** Y_{i }**= Data value of the second category

** X̄ **= 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
tab.*Data* - From the
**Analysis**group, select thecommand.*Data Analysis*

### Step 2: Select Covariance Option from Analysis Tool

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

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

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

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

**Similar Readings**

**How to Multiply 3 Matrices in Excel (2 Easy Methods)****Create Traceability Matrix in Excel****How to Create a Risk Matrix in Excel (With Easy Steps)****Make an Eisenhower Matrix Template in Excel (With Easy Steps)**

## 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
with its mean is*Math*.**137.654321** - The variance of
is*Science*.**95.1111** - Finally, the variance of
is*History***51.5555.**

### 2. Covariance for Multiple Variables

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

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

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

**is positive (**

*Science***), implying that students who perform well in**

*45.85185***also perform well in**

*Math***.**

*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

**in our example covariance is negative (**

*History***), indicating that students who score higher in**

*-27.3703***will score lower in**

*Math***.**

*History*** Notes: **

If you cannot find the ** Data Analysis** tool in your

**tab, you may need to activate the**

*Data***first. To do so, follow the instructions below.**

*Data Analysis ToolPak***Steps:**

- Firstly, go to
.*Home* - Then, click on
.*Options*

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

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

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

- Consequently, you will find the
command in your*Data Analysis*tab.*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.