How to Create a Covariance Matrix in Excel?

Last Updated : 28 Jan, 2024

Covariance is a statistical term that signifies the direction of the linear relationship between two variables. The direction usually refers to whether the variables vary directly or inversely to each other. It should be remembered that covariance only measures how two variables change together, it does not explain the dependency of one variable over another variable. When the two variables vary in the same direction (directly) then the covariance is said to be positive covariance. Conversely, when the two variables vary in the opposite direction (inversely) then the covariance is said to be negative covariance.

The mathematical formula for Covariance of a population is given as:

$Cov(x,y) = \frac{\sum_{i=1}^{n} (x_i-\overline{x})(y_i-\overline{y})}{n}$

Where, x,y is the array of first and second variable respectively, $\overline{x}$ and $\overline{y}$ are the mean values of x and y respectively and n is the no. of elements in the array.

On the other hand, the mathematical formula for Covariance of a sample is given as:

$Cov(x,y) = \frac{\sum_{i=1}^{n} (x_i-\overline{x})(y_i-\overline{y})}{n-1}$

The value of covariance lies in the range $(-\infty ,\infty)$ . The crux of the matter is the numerical value of significance holds no value since it is unit dependent, hence only the sign/polarity associated with the numerical value matters. If the sign is positive, both the variables vary in the same direction else if the sign is negative, we can infer both the variables vary inversely with one another.

What is a covariance matrix?

A covariance matrix is typically a square matrix representing covariance between each pair of elements in a random array. The covariance matrix is symmetrical along the diagonals.

Creating a covariance matrix in Excel

We can create a covariance matrix in Excel using the Covariance function present inside the data analysis tool available under the data analysis the toolpak add-in package.

Suppose, we have a group of students and we want to create a covariance matrix for finding the covariance between marks obtained by each student in various subjects. The marks obtained by each student in the different subject is given as :

Step 1: Click the Data ribbon in the excel menu and select the Data Analysis tool option.

Step 2: A data analysis tool dialog box will appear on the screen. From all the available options in the dialog box, select the Covariance option and click OK.

Step 3: A Covariance dialog box will pop up on the screen. Inside the dialog box, in the input range field pass the data array. Here, We want to compare the marks, hence, the cell range from B1 to D7 is passed. Now, since our data is grouped by columns, therefore, we select the Columns radio button under the Grouped by field and our data has labels in the first row, therefore, we click the appropriate checkbox. Now, we want to place the covariance matrix in the same worksheet, we will select the cell in which we want to place the covariance matrix and give the cell address in the output range field here cell A10 is passed. Then click the OK button.