Home / Statistical Tools / Analysis Tools / Correlation and Covariance / How-To / 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¶
Press Copy for Excel, then paste the table into a blank worksheet.
| 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¶
-
Put the data in Excel
Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. The header row lands in row 1 and the 10 data rows in rows 2 through 11.
-
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.