Home / Statistical Tools / Analysis Tools / Correlation and Covariance / Examples / Covariance
Covariance¶
Covariance measures how two variables change together in an unstandardized way. Unlike correlation coefficients (which are always between -1 and 1), covariance values depend on the units of your data — larger values mean stronger joint variation, but the magnitudes aren't directly comparable across different datasets.
Goal¶
Calculate the covariance matrix for three asset returns to see how they move together.
Sample Data¶
Download CorrelationAndCovariance_Covariance.xlsx
Excel Protected View
When you open downloaded files, Excel displays a Protected View warning. You must click Enable Editing before you can use Quantum XL with the file.
Alternatively, you can copy the sample data from the table below and paste it directly into a new Excel workbook.
| Stock A | Stock B | Bond Fund |
|---|---|---|
| 2.5 | 3.1 | 0.8 |
| -1.2 | -0.8 | 1.2 |
| 4.1 | 3.5 | 0.5 |
| 1.8 | 2.0 | 0.9 |
| -0.5 | 0.3 | 1.1 |
| 3.2 | 2.8 | 0.7 |
| 0.9 | 1.5 | 1.0 |
| -2.1 | -1.5 | 1.3 |
| 5.0 | 4.2 | 0.4 |
| 1.5 | 1.8 | 1.0 |
Each row represents the monthly return percentage for three investments. Stock A and Stock B should move together (positive covariance), while Bond Fund likely moves oppositely to the stocks (negative covariance with each stock).
Steps¶
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Correlation and Covariance → Covariance.
-
Select your data
Select cells A1:C11 (the header row plus all 10 data rows across all three columns).
-
Configure the analysis
In the Covariance dialog:
- Data Columns: "Stock A", "Stock B", and "Bond Fund" should all be checked
Click Finish to generate the covariance matrix.
Result¶
Quantum XL creates a 3×3 covariance matrix showing:
- Diagonal cells — Show the variance of each variable with itself. For example, the Stock A cell in the Stock A row shows the variance of Stock A returns.
- Off-diagonal cells — Show the covariance between pairs of variables. Positive values mean the variables tend to move in the same direction; negative values mean they move in opposite directions.
Variance on the Diagonal
The covariance of a variable with itself is mathematically equal to its variance. Quantum XL displays these diagonal cells with a distinct background to emphasize this.
Unlike correlation, covariance does not produce p-values because the magnitudes are unstandardized. To assess statistical significance of relationships, use Pearson or Spearman correlation instead.