StatPakHow to make a covariance matrix in Google Sheets
Covariance measures how two variables move together. A covariance matrix shows it for every pair of columns, with each column's variance on the diagonal. Example: the ad spend, price and sales data from the regression guide, 15 months.
| A | B | C | |
|---|---|---|---|
| 1 | Ad spend | Price | Sales |
| 2 | 12 | 8.2 | 37.8 |
| 3 | 15 | 9.6 | 45.2 |
| 4 | 9 | 7.4 | 39.8 |
| 5 | 22 | 9.1 | 66 |
| 6 | 18 | 10.3 | 52.5 |
| 7 | 25 | 7.9 | 88.3 |
| 8 | 30 | 9.4 | 90.5 |
| 9 | 14 | 8.8 | 47.4 |
| 10 | 20 | 7.1 | 77.2 |
| 11 | 27 | 10 | 79.7 |
| 12 | 33 | 8.5 | 108.6 |
| 13 | 11 | 9.9 | 27.5 |
| 14 | 24 | 7.6 | 81.5 |
| 15 | 29 | 8.9 | 95.5 |
| 16 | 17 | 10.4 | 45 |
With the Covariance tool
- Open Extensions, StatPak, Data Analysis and choose Covariance.
- Input range:
A1:C16, Grouped by: Columns. - Tick Labels in first row or column and click Run.
| Ad spend | Price | Sales | |
| Ad spend | 52.10667 | ||
| Price | 0.05066667 | 1.068622 | |
| Sales | 166.84 | -6.339333 | 579.864 |
How to read it
- The diagonal holds each variable's variance. Ad spend's is 52.10667.
- Below the diagonal are the covariances. The matrix is symmetric, so the upper half would repeat the lower half and is left blank.
- A positive covariance means the two tend to rise together: ad spend and sales, 166.84. A negative one means one tends to fall as the other rises: price and sales, -6.339333.
Population or sample covariance?
Excel's Covariance tool computes COVARIANCE.P, the population covariance, which divides by n. Microsoft's ToolPak documentation states this, and StatPak matches it, so the diagonal equals VAR.P. Descriptive Statistics reports the sample variance, VAR.S, which is slightly larger.
If your data is a sample and you want the sample covariance (dividing by n - 1), multiply each cell by n / (n - 1). With 15 months that is 15/14: the ad spend and sales covariance becomes 178.7571. In Sheets, =COVARIANCE.S(A2:A16, C2:C16) gives the sample value directly and =COVARIANCE.P(A2:A16, C2:C16) gives the tool's value. The older COVAR function is the population version.
Covariance or correlation?
Covariance is in the units of the two variables multiplied together, so its size depends on scale: measure ad spend in cents and the covariance grows 100 times. That makes it hard to say whether 166.84 is large. Correlation divides the covariance by both standard deviations, which gives a unitless number between -1 and 1:
166.84 / SQRT(52.10667 * 579.864) = 0.9598215
That is the same value as in the correlation matrix. Use correlation to describe how strongly two variables are related. Use covariance when a formula calls for it, for example portfolio variance in finance or a principal component analysis.