# How To Insert Variance-Covariance Matrix

The variance-covariance matrix is a way to describe patterns of data. This statistic can be used as an overview for many multivariate statistical models.

Variance refers to how widely data sets are scattered around their mean value, while covariance considers both sides and finds out whether there is an obvious trend.

Covariance is a statistical measure of the directional relationship between two random variables. It can be used to determine if there is an association or correlation between them, which would mean they move together somewhat consistently over time.

Before generating a matrix, you will need to have the data listed within a table. The table must have labels clearly identifying the random variables as Excel uses these headings when generating the matrix.

For the purposes of this guide, test data has been created for you to use to calculate a sample variance/covariance matrix. The sample data is of price observations from four companies.

## Steps to create a Variance-Covariance Matrix

The steps below will take you through the process using some sample data. A quick reference guide is available at the end of this document.

## Adding the Analysis ToolPak to the Data tab

If the Analysis section on the Data tab is visible, you can skip theses steps.

If the Analysis section is not activated, follow these steps to activate the Analysis ToolPak. In Excel, click File. Click Options. In the Excel Options window click Add-ins, in Manage select Excel Add-ins, click Go. The Add-ins window opens, select Analysis ToolPak and click OK. The Analysis section is now active on the Data tab. ## Generating the Variance-Covariance Matrix

Open the Variance-Covariance Matrix sample data spreadsheet.

On the Data tab, click Data Analysis. In the Data Analysis window, select Covariance, click OK. The Covariance window opens, click the Input Range arrow. The Covariance window contracts to the Input Range field, select the data excluding the Dates column, be sure to include the labels at the top of the columns. Click the Input Range arrow again, the window expands. Click Labels in First Row. In Output options click the Output Range arrow. The Covariance window contracts to the Output Range field. Click in cell I5, this identifies where Excel will display the matrix. Click the Output Range arrow again, the Covariance window expands. Click OK. Excel creates the Covariance matrix and displays it in cell I5. The column headings selected earlier are included in the matrix. ## Quick reference guide

### Steps to add the Analysis ToolPak

1. In Excel, click File.
2. Click Options.
3. In the Excel Options window click Add-ins, in Manage select Excel Add-ins, click Go.
4. The Add-ins window opens, select Analysis ToolPak and click OK.
5. The Analysis section is now active on the Data tab.

### Generating the Variance-Covariance Matrix

1. Open the spreadsheet with the data you wish to analyse.
2. On the Data tab, click Data Analysis. In the Data Analysis window, select Covariance, click OK.
3. The Covariance window opens, click the Input Range arrow.
4. The Covariance window contracts to the Input Range field, select the data excluding the Dates column, be sure to include the labels at the top of the columns.
5. Click the Input Range arrow again, the window expands. Click Labels in First Row.
6. In Output options click the Output Range arrow.
7. The Covariance window contracts to the Output Range field.
8. Click in a cell where you want Excel to display the results.
9. Click the Output Range arrow again, the Covariance window expands.  Click OK.
10. Excel creates the Covariance matrix and displays it in cell selected above. The column headings selected earlier are included in the matrix.
```Further reading:
Variance in Excel
Covariance in Excel
How to insert a Variance graph
How to calculate a variance estimator
Matrix multiplication
How to make BCG Matrix```