## What Is Covariance Matrix on Excel?

The covariance matrix on Excel is a square matrix that contains the covariance between different variables. It helps evaluate the structured relationships among the variables in a given dataset.

Users can use the Excel covariance matrix in financial engineering, machine learning, and econometrics for applications such as principal component analysis.

For example, the below table contains the per-day stock prices of top tech US companies.

Suppose the requirement is to assess the relationships between the specified companies based on their per-day stock prices. Then, we can **calculate covariance matrix in Excel** for the above data using the **Data Analysis ToolPak** feature from the **Data** tab and perform the required analysis.

And **interpreting covariance matrix in Excel** is quite straightforward.

For example, in the above scenario, the variances of **Apple**, **Amazon**, **Alphabet**, and **Microsoft** per-day stock prices are **64.9589**, **69.53802**, **63.36238**, and **66.35788**.

On the other hand, the covariance between:

**Apple**and**Amazon**per-day stock prices-**42.22199****Apple**and**Alphabet**per-day stock prices-**41.74961****Apple**and**Microsoft**per-day stock prices-**12.86334****Amazon**and**Alphabet**per-day stock prices-**46.4082****Amazon**and**Microsoft**per-day stock prices-**36.81778****Alphabet**and**Microsoft**per-day stock prices-**30.14246**

Further, as all the matrix values are positive, the inference is that the specified companies’ per-day stock prices increase or decrease in tandem.

##### Table of contents

###### Key Takeaways

- The covariance matrix on Excel is a square matrix, which is symmetrical along its diagonal. It helps determine whether two variables vary linearly in the same direction or inversely. And while its diagonal shows the variances of the variables, the part below the diagonal contains the covariances between the variables.
- Users can use the covariance matrix in applications such as performing Cholesky decomposition in Stochastic Modelling.
- We can create a covariance matrix using the
**Data Analysis ToolPak**feature in the**Data**tab.

**Explanation And Uses**

Covariance statistically indicates whether variables vary in tandem or inversely with each other. And the formula to **calculate covariance matrix on Excel** is:

The covariance matrix values lie between (-∞,∞). And they are unit dependent, with their signs deciding whether two variables vary directly or inversely.

If the matrix value is positive, the two variables increase and decrease in the same direction. And a negative value implies that if one variable increases, the other decreases.

Thus, we can use the above expression as a **covariance matrix Excel formula** and create square covariance matrices, depending on the number of variables involved. For instance, if the source data includes two variables, we can build a 2×2 matrix. And for a three variables dataset, we can find a 3×3 covariance matrix.

Furthermore, the resulting matrix will be symmetrical towards the diagonal. In other words, the part above the diagonal is a mirror image of the part below the diagonal containing the covariance values. And so the output matrix has the section above the diagonal empty.

The covariance matrix uses are:

- Review the dependency behavior between two vectors in machine learning.
- Correlate variables while performing Stochastic Modeling in financial engineering.
- Determine the eigenvectors during principal component analysis.
- Estimate the returns on financial assets.

**How To Use Covariance Matrix In Excel?**

We can use the covariance matrix from **Data Analysis ToolPak** in the **Data** tab in Excel.

Click on a cell in the active worksheet and select the **Data** tab – click **Data Analysis**.

The **Data Analysis **window will open, where we must select **Covariance **from the **Analysis Tools **list and click **OK**.

The **Covariance **window will open. Update the highlighted fields and click **OK** to obtain the required square covariance matrix in the active worksheet.

But before we see an example of calculating the covariance matrix with the **Data Analysis ToolPak**, we must confirm that the **Data Analysis** feature is available in the **Data** tab.

And suppose the **Data Analysis** feature is not available in the **Data** tab (as shown below).

Then the steps to install it are as follows:

**Step 1:** First, click **File** tab – **Options** to open the **Excel Options **window.

**Step 2:** Next, select the **Excel Add-ins **category in the **Excel Options** window and then, click **Go**.

The **Add-ins** window will open.

**Step 3:** Then, check the **Analysis ToolPak** option from the **Add-ins** list in the **Add-ins** window.

And once we click **OK** in the **Add-ins** window, the **Data Analysis** feature will be visible in the **Data** tab.

**#Basic Example**

The following table contains scores secured by five students in three subjects.

Suppose the requirement is to perform the covariance analysis in excel based on the scores each student secured in the three subjects. Then, we can create a covariance matrix in the current sheet for the above data to perform the required analysis.

**Step 1:** Click on a cell in the active worksheet and follow the path **Data** – **Data Analysis** to open the **Data Analysis** window.

**Step 2:** Pick **Covariance** from the **Analysis Tools** list and click **OK** to close the **Data Analysis** window.

**Step 3:** The **Covariance **window opens, where we need to update the fields as depicted below.

When selecting the **Input range**, include the cells containing the subject names in the selection. And check the Labels option in the **Covariance** window to view the subject names in the resulting covariance matrix.

Also, grouping is by column in the source data. So, the **Grouped By** option is **Columns**.

And as we require the covariance matrix in the current worksheet, pick the first option under **Output options** and enter the reference to the required target cell.

Click **OK**. It will close the **Covariance **window, and we will obtain the following covariance matrix.

Thus, we can obtain a square matrix with the part above the diagonal empty, as the covariance matrix is symmetric along the diagonal.

We shall first list the variances and covariances required for **interpreting covariance matrix in Excel**.

The variances of the subjects’ scores are on the matrix diagonal:

**History**score:**58.96****Geography**score:**72.8****Mathematics**score:**84.4**

The covariances between the specified subjects are in the section below the diagonal:

**History**and**Geography**scores:**57.2****History**and**Mathematics**scores:**-23****Geography**and**Mathematics**scores:**-12.4**

The above covariance matrix shows positive covariance between **History** and **Geography**. It implies that students who score high in **History **also score well in **Geography**. And those who score low in **History **also score low in **Geography**.

On the other hand, the covariances between **History** and **Mathematics** and **Geography** and **Mathematics **are negative. The two negative values imply that those who score well in **History** and **Geography** perform poorly in **Mathematics**. Likewise, those who score well in **Mathematics **perform poorly in **History** and **Geography**.

**Examples**

Check out the following Excel covariance matrix examples to use the matrix in the best ways possible.

**Example #1**

The table below shows a list of athletes, their ages, heights, and weights.

Suppose the requirement is to perform the covariance analysis of the athletes’ statistics. Then, calculating the covariance matrix in the current worksheet based on the above data is the best solution.

**Step 1:** Click on a cell in the current worksheet and follow the path **Data** – **Data Analysis** to open the **Data Analysis** window.

**Step 2: **Pick **Covariance** in the **Data Analysis** window, and click **OK** to open the **Covariance** window.

**Step 3:** Update the highlighted fields in the **Covariance** window.

The input range includes the **Age**, **Height**, and **Weight** columns, starting from the column headers in the cell range B3:D3. And we must check the labels option to include them in the matrix.

On the other hand, grouping is by column in the source data. So, the **Grouped By** option is **Columns**.

And as we require the covariance matrix in the active worksheet, we select the first option under **Output options** and provide the target cell reference in excel.

**Step 4:** Click **OK **in the **Covariance** window to view the required covariance matrix.

Thus, the above covariance matrix shows that the athletes’ **Age**, **Height**, and **Weight** variances are **13.05**, **21.65**, and **17.85**. And the covariances between **Age **and **Height**, **Age** and **Weight**, and **Height** and **Weight** are **7.95**, **7.85**, and **19.05**.

Furthermore, the covariance values in the section below the diagonal are positive. So, the inference is that the **Age **and **Height**, **Age** and **Weight**, and **Height** and **Weight** vary linearly in the same direction.

**Example #2**

The following table shows the percentage returns from two stocks, **ABC** and **LCD**, during 2015-20.

Suppose the requirement is to determine the polarity of the linear relationship between the stocks based on the yearly returns.

Then, we can create a covariance matrix for the above dataset, where the data grouping is by rows.

**Step 1:** Click on a cell in the active worksheet and follow the path **Data** – **Data Analysis** to open the **Data Analysis **window.

**Step 2:** In the **Data Analysis **window, pick the **Covariance** analysis tool, and click **OK**.

**Step 3:** The **Covariance** window will open. Here, we need to update the below-highlighted fields.

The input range includes the yearly returns from each stock and the stock names. And we check the labels option to view the stock names in the resulting matrix.

However, as the grouping is by rows in the source data, the **Grouped By** option should be **Rows**.

And we provide the target cell reference K2 to view the covariance matrix in the current sheet.

**Step 4:** Click **OK** in the **Covariance **window to close it and obtain the following covariance matrix.

The stock **ABC** and **LCD** returns’ variances are on the matrix diagonal, **0.0000962547 **and** 0.000167611**, respectively. And the covariance between stock **ABC** and **LCD** returns **0.0000674936111111111**.

Further, as the covariance between the two stocks’ returns is positive, they vary linearly in random or in the same direction.

**Example #3**

The below image shows two tables.

The first table contains three variables’ datasets, **X**, **Y**, and **Z**.

Suppose the requirement is to create a covariance matrix for analyzing the relationships between the specified variables in Excel.

Then, here is how we can use the **Data Analysis ToolPak** and achieve the matrix in the target cell A11.

Also, we shall use the second table and **covariance matrix excel formula** to calculate the covariance matrix manually and display the result in cell G11. The variable **n** has the value **5** assigned as it denotes the total number of data points in each data series.

**Step 1: **Click on a cell in the active worksheet and choose **Data** – **Data Analysis** to open the **Data Analysis** window.

**Step 2: **Pick **Covariance **from the **Analysis Tools** list in the **Data Analysis** window and click **OK**.

The **Covariance **window opens, where we must update the highlighted fields and then, click **OK**.

The input range is A2:C7, which includes the three variables’ datasets and the variable names. And the grouping is by **Columns**, with the labels option enabled to view the variable names in the resulting covariance matrix.

Also, the target cell is A11. So, it is the output range.

Clicking **OK** in the **Covariance** window will result in the below-highlighted covariance matrix.

We shall now see the manual covariance matrix calculations.

**Step 3:** Select cell G2, enter the **AVERAGE excel function** to obtain the mean value of variable **X** and press **Enter**.

*=AVERAGE(A3:A7)*

Next, select cell G3, enter the **AVERAGE()**to obtain the mean value of variable **Y** and press **Enter**.

*=AVERAGE(B3:B7)*

Select cell G4, enter the **AVERAGE()**to obtain the mean value of variable **Z** and press **Enter**.

*=AVERAGE(C3:C7)*

Next, we must find the difference between each data point in a dataset and the dataset mean value for the three variables.

**Step 4:** Select cell H3, enter the below formula, and press **Enter**.

*=A3-$G$2*

Using the fill handle in excel, enter the formula in cell range H4:H7.

**Step 5:** Select cell I3, enter the below formula, and press **Enter**.

*=B3-$G$3*

Using the fill handle, enter the formula in cell range I4:I7.

**Step 6:** Select cell J3, enter the below formula, and press **Enter**.

*=C3-$G$4*

Using the fill handle, implement the formula in cell range J4:J7.

**Step 7: **Select cells from H12 to J14, one at a time. And enter the expression specified in the Formula Bar to determine the corresponding covariance matrix elements using the covariance matrix formula.

Thus, the output shows that the two methods result in the same covariance matrix.

Further, the manual method proves that the covariance matrix is symmetric along the diagonal.

For example, cells H13 and I12 show the covariance between **X **and **Y**. And both the cells contain the same value, **30.6**.

And, as the covariance between **X** and **Y** is positive, the two variables vary linearly in the same direction. On the other hand, the covariances between **X **and **Z** and **Y** and **Z **are negative, implying the variable pairs **X **and **Z** and **Y **and **Z **vary inversely.

### Important Things To Note

- Ensure the data series do not have non-numeric values. Otherwise, we will get a warning message, and we cannot create a covariance matrix in Excel.
- Suppose we calculate a covariance matrix. Then, any changes made to the source data will not update the matrix automatically. Also, clicking
**Ctrl**+**Z**will not undo the covariance matrix calculations. For any changes in the matrix, we must delete the existing one and repeat the steps to create a new matrix. - The covariance matrix obtained using the
**Data Analysis ToolPak**contains population variances in its diagonal, not the sample variances.

**Frequently Asked Questions**

**1. How to calculate the variance-covariance matrix in Excel?**

We can calculate the variance-covariance matrix in Excel in the following way. Let us see the steps with an example.

The table below contains the expected daily returns of three firms.

Suppose the total number of data points in each firm’s data series is **5**.

And the requirement is to determine the sample variance-covariance matrix for the above data. Then, we can create a covariance matrix using the Data Analysis ToolPak feature in the Data tab. And then, we can derive the sample variance-covariance matrix using the calculated matrix and the highlighted cell B11 formula.**Step 1: **Click a cell in the active worksheet and choose **Data** – **Data Analysis** to open the **Data Analysis** window.

Pick **Covariance** from the **Analysis Tools** list in the **Data Analysis** window. And click **OK**.

The **Covariance** window will open, where we must update the highlighted fields, as shown below.

Click **OK** to close the **Covariance **window and view the covariance matrix in the specified target cell.

The following steps will complete the matrix as it is symmetric along the diagonal.**Step 2:** Select cell range G5:G6, press **Ctrl **+ **C **to copy the values, and click cell H4. And then, press **Alt** + **E **+ **S **to open the **Paste Special **Window and check the **Transpose** option. Click **OK** to execute the action.

Then two values will get pasted row-wise in cell range H4:I4.

Next, select cell H6, press **Ctrl** + **C** to copy the cell value, and click cell I5. And press **Ctrl** + **V** to paste the value in cell I5.

The calculated covariance matrix contains the population variances of the three firms’ daily expected returns in the diagonal.

But, assuming the given data set is massive. Then, ideally, we must calculate the sample variance instead of the population variance. So, in the above scenario, we can modify the covariance matrix to obtain a matrix with sample variances of the three firms’ daily expected returns in its diagonal.**Step 3:** Select the cell range G11:I13 and enter the below formula.*=B11*G4:I6*

And press **Ctrl** + **Shift** + **Enter** to apply the above expression as an array formula to achieve the required variance-covariance matrix.

Cells G11, H12, and I13 show the sample variances of each firm’s daily expected returns. And we can verify the values using the **VAR.S()**.

We shall introduce a new table below the variance-covariance matrix to show the sample variances of the three firms’ daily expected returns using the **VAR.S()**.**Step 4:** Select cell G16, enter **VAR.S()**, and press **Enter**.*=VAR.S(B4:B8)*

Select cell H16, enter **VAR.S()**, and press **Enter**.*=VAR.S(C4:C8)*

Select cell I16, enter **VAR.S()**, and press **Enter**.*=VAR.S(D4:D8)*

Thus, we see that the sample variances in the variance-covariance matrix and the **VAR.S()** outputs in the sample variance table are the same for each firm’s daily expected returns.

**2. What is the use of covariance matrix in Excel?**

The use of covariance matrix in Excel is that it helps understand the relationships in a matrix of specified variables. We can use it to decorrelate the given variables or transform them into other variables.

**3. Why is covariance matrix in Excel not working?**

The covariance matrix in Excel is not working because the source data contains non-numeric values or invalid numbers.

**Download Template**

This article must be helpful to understand the **Covariance Matrix In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to **Covariance Matrix on Excel**. Here we explain how to use covariance matrix in excel with examples and downloadable excel template. You may learn more from the following articles –

## Leave a Reply