How to Use the COVARIANCE.S and COVARIANCE.P Functions in Microsoft Excel for Statistical Analysis

When analyzing pairs of financial assets, scientific variables, or business performance indicators in Microsoft Excel, calculating the variance of a single metric only reveals its individual volatility. To understand how two variables move in relation to one another—such as how interest rates influence bond prices, or how marketing expenditures correlate with product sign-ups—analysts rely on covariance. In Excel, calculating covariance is performed through two dedicated worksheet functions: COVARIANCE.S (for sample data) and COVARIANCE.P (for entire population datasets).

Covariance evaluates the direction of linear relationships between datasets: positive values indicate that both variables tend to rise and fall together, while negative values indicate that one variable increases as the other decreases. In this guide, you will learn the statistical distinction between sample and population covariance, master formula syntax, construct an investment risk matrix, and understand how covariance connects to correlation.

Sample vs. Population: Choosing COVARIANCE.S or COVARIANCE.P

Selecting the correct covariance function depends entirely on the scope of your data:

• COVARIANCE.S (Sample Covariance): Divides the sum of squared differences by n - 1 (Bessel’s correction). Use this function whenever your data represents a sample drawn from a larger group (e.g. testing 50 random retail customers or analyzing 36 months of stock market returns). This is the standard choice for financial portfolio modeling.
• COVARIANCE.P (Population Covariance): Divides by the total population count n. Use this function only when your dataset comprises the complete, exhaustive universe of observations (e.g. all 12 fiscal months of a completed annual audit or test scores for every enrolled student in a private school).
• Legacy Note: The legacy =COVARIANCE() function calculates population covariance (identical to COVARIANCE.P), but Microsoft recommends using the newer dotted syntax for mathematical precision.

Syntax and Argument Rules for Covariance Functions

Both functions share identical syntax structure:

=COVARIANCE.S(array1, array2)
=COVARIANCE.P(array1, array2)

• array1: Required. The first cell range, column array, or series of numeric values.
• array2: Required. The second cell range or series of numeric values of equal dimension.

Dimensional Requirement: array1 and array2 must contain an identical number of data points. If one array spans 10 rows and the other spans 12 rows, Excel immediately returns a #N/A error.

Step 1: Calculating Sample Covariance Step-by-Step

Consider an investment portfolio example comparing monthly percentage returns between two corporate assets over 5 months:

Month	Column A (Stock X Return %)	Column B (Stock Y Return %)
Month 1	0.05 (5%)	0.08 (8%)
Month 2	-0.02 (-2%)	-0.01 (-1%)
Month 3	0.04 (4%)	0.06 (6%)
Month 4	0.01 (1%)	0.03 (3%)
Month 5	-0.03 (-3%)	-0.04 (-4%)

To calculate the sample covariance in cell D2, enter:

=COVARIANCE.S(A2:A6, B2:B6)

Excel returns 0.0019. Because the result is positive, it confirms that Stock X and Stock Y exhibit positive co-movement: when Stock X yields positive returns, Stock Y tends to gain as well.

Step 2: Connecting Covariance to the Correlation Coefficient

A fundamental limitation of covariance is that its raw numeric magnitude depends on the scale of the original data units (e.g. comparing meters with kilograms produces a massive covariance value that is difficult to interpret). To normalize covariance into a dimensionless scale from -1.0 to +1.0, statisticians divide covariance by the product of both variables’ standard deviations, deriving the correlation coefficient:

=COVARIANCE.S(A2:A6, B2:B6) / (STDEV.S(A2:A6) * STDEV.S(B2:B6))

Verifying this formula in Excel yields the exact same result as using Excel’s dedicated function: =CORREL(A2:A6, B2:B6).

Step 3: Building a Variance-Covariance Matrix for Portfolio Risk

In modern portfolio theory (MPT), constructing a multi-asset variance-covariance matrix is essential for calculating overall portfolio volatility:

1. Set up a square grid listing Asset A, Asset B, and Asset C across both rows and columns.
2. In the diagonal intersection cells (where an asset meets itself, such as Cell A/A), calculate the asset’s individual sample variance:

   =VAR.S(AssetA_Returns)

   Note: The covariance of a variable with itself equals its variance: COVARIANCE.S(A, A) = VAR.S(A).
3. In the off-diagonal cells, calculate pairwise covariance:

   =COVARIANCE.S(AssetA_Returns, AssetB_Returns)

4. Use matrix multiplication (MMULT) to combine the covariance matrix with asset weights to evaluate minimum variance portfolios.

Troubleshooting Common Covariance Errors in Excel

• #N/A Error: Indicates mismatched range sizes. Ensure both arrays encompass the exact same number of cells (e.g. A2:A50 and B2:B50).
• #DIV/0! Error: Occurs when either array is empty or contains fewer than two numeric data points (for COVARIANCE.S, at least 2 points are required to compute n - 1).
• Text and Empty Cells: Excel automatically skips empty cells, text entries, and logical TRUE/FALSE values within ranges, computing covariance strictly on valid numerical pairs.