Calculate sample and population covariance from paired sales and cost observations. Follow a worked workbook example, check the arithmetic, and interpret the result without confusing scale with strength.
Covariance data analysis in Excel starts with two numeric columns whose rows describe the same periods, customers, or orders. Use COVARIANCE.S for sample covariance and COVARIANCE.P for population covariance. A positive result means the measures tend to sit above their means together and below their means together; the number is not a percentage.
The useful business question is usually narrower than whether two totals rose: did sales and costs move together across matched observations, and which periods explain that movement?
Take a US distributor's monthly sales and fulfillment cost export. Keep one row per month, with the same business scope, currency, and reporting cutoff in both columns. The general Excel analysis workflow covers cleaning and reconciling the source table before applying a statistical calculation.
For this illustration, columns B and C contain amounts in thousands of USD. Each row is one matched pair.
| Month | Sales (USD thousands) | Fulfillment cost (USD thousands) |
|---|---|---|
| May 2026 | 100 | 60 |
| June 2026 | 120 | 80 |
| July 2026 | 140 | 75 |
| August 2026 | 160 | 95 |
| September 2026 | 180 | 90 |
| Total | 700 | 400 |
Put the headers in row 1 and observations in rows 2 through 6. Exclude the total row from every covariance range. The average sales amount is 700 / 5 = 140; average fulfillment cost is 400 / 5 = 80.
If your export contains transactions, summarize both measures to the same month first. Pairing monthly revenue with individual shipment charges gives the calculation no consistent observation unit. For practice before using company records, use the layout in sample Excel data for analysis.
Microsoft documents COVARIANCE.S as the sample covariance function. Enter:
=COVARIANCE.S(B2:B6,C2:C6)
The result is 375, measured in squared thousands of USD. If these months are observations used to understand a broader operating process, this is the sample calculation.
Microsoft's COVARIANCE.P documentation describes the population version:
=COVARIANCE.P(B2:B6,C2:C6)
That returns 300. Use it when the question is explicitly about this complete set of five months, rather than a broader population of periods. Neither function is universally better; the intended scope determines the denominator.
Sample covariance = Sum of paired deviation products / (Number of pairs − 1)
Population covariance = Sum of paired deviation products / Number of pairs
The distinction is unrelated to whether a workbook contains every transaction available today. Those transactions may still represent only a sample of the business conditions you want to understand.
Compute each observation's deviation from its column mean, then multiply the two deviations on that row.
| Month | Sales minus 140 | Cost minus 80 | Product of deviations |
|---|---|---|---|
| May | −40 | −20 | 800 |
| June | −20 | 0 | 0 |
| July | 0 | −5 | 0 |
| August | 20 | 15 | 300 |
| September | 40 | 10 | 400 |
| Total | 0 | 0 | 1,500 |
Enter these helper formulas in D2, E2, and F2, then fill down:
=B2-AVERAGE($B$2:$B$6)
=C2-AVERAGE($C$2:$C$6)
=D2*E2
Both deviation columns must sum to zero, allowing for spreadsheet precision. Their product column sums to 1,500, so sample covariance is 1,500 / 4 = 375 and population covariance is 1,500 / 5 = 300. All figures here are exact; no rounding is required.
An independent worksheet check is:
=SUM(F2:F6)/(COUNT(B2:B6)-1)
=SUM(F2:F6)/COUNT(B2:B6)
These must match the built-in sample and population results respectively. Preserve the helper table when preparing an Excel analysis report, so a reviewer can inspect which months contribute most.
May contributes positively because sales and cost are both below their means. August and September contribute positively because both are above their means. June and July contribute zero because one measure equals its mean.
The positive total supports a description of joint movement in these observations. It does not establish causality, a cost rate, or a forecast.
Changing units changes the magnitude. Convert both source columns from thousands of dollars to dollars and sample covariance becomes 375 × 1,000 × 1,000 = 375,000,000 USD squared. Nothing about the underlying relationship became stronger.
For a standardized comparison, Microsoft's CORREL function returns a correlation coefficient:
=CORREL(B2:B6,C2:C6)
For these rows, it is 0.866, rounded to three decimals. That is a different measure from covariance. Covariance is also distinct from ANCOVA, which compares groups while adjusting for other variables. For category counts rather than paired numeric amounts, use the separate chi-square workflow.
Covirage is not affiliated with Microsoft.
Bring the workbook containing your paired sales and cost observations to Covirage. Covirage maps the columns; its tools compute and check the underlying business comparisons, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See Excel analysis with Covirage for the file-based workflow.
Use sample covariance when the observed periods are a sample used to understand a broader process. Use population covariance when the rows are the complete set you intend to describe. State that scope explicitly; a complete export is not automatically the entire population for every business question.
Covariance multiplies deviations in both measures. Multiplying one measure by a factor multiplies covariance by that factor; converting both measures from thousands of dollars to dollars multiplies covariance by one million. The direction of their relationship stays the same.
No. Positive covariance describes joint movement in the observed pairs. Seasonality, order volume, inflation, or another shared influence may move both measures. Use the result to investigate the underlying rows, then assess business explanations separately.