Covariance Between Two Variables

Excel Formulas › Statistics

All versionsCOVAR

Do two variables move together? Covariance measures the direction of their joint variation — positive when they rise together, negative when one falls as the other rises. COVAR computes it directly.

Quick formula: for paired data in B2:B20 and C2:C20:
=COVAR(B2:B20, C2:C20)
Positive means they tend to move the same way; negative, opposite ways; near zero, little linear relationship.

Functions used (tap for the full reference guide):

COVARCORREL

The example

Ad spend vs sales — they rise together, so covariance is positive.

ABC
1WeekAd $Sales
211042
321455
43940
5Covariance+positive

The formula

Covariance of the two columns:

=COVAR(B2:B20, C2:C20) // positive → spend & sales move together

How it works

Covariance averages how the two variables deviate together:

  1. For each pair, it multiplies how far each value is from its own mean, then averages those products.
  2. A positive result means when one variable is above its mean, the other tends to be too. A negative result means they move oppositely.
  3. The magnitude depends on the units, so covariance alone is hard to interpret — it’s the sign that’s most readable.
  4. COVAR (and COVARIANCE.P) use the population formula; COVARIANCE.S (365/2010+) uses the sample formula.

Want a comparable number? Divide covariance by the product of the two standard deviations and you get correlation — a unit-free −1 to +1 score. CORREL does it directly and is usually easier to interpret than raw covariance.

Try it: interactive demo

Live demo

Enter pairs as “x,y”, one per line.

Covariance:   Correlation:

Variations

Sample covariance

Divides by n−1:

=COVARIANCE.S(B2:B20, C2:C20)

Correlation instead

Unit-free −1 to +1:

=CORREL(B2:B20, C2:C20)

As a percentage of variance

R-squared:

=CORREL(B2:B20, C2:C20)^2

Pitfalls & errors

Magnitude is unit-dependent. Covariance in dollars×units isn’t comparable across datasets — use correlation for that. Read covariance mainly for its sign.

Equal-length ranges. The two ranges must have the same number of paired values, or you get a #N/A.

Correlation ≠ causation. A positive covariance shows association, not that one variable causes the other.

Practice workbook

📊
Download the free Covariance Between Two Variables practice workbook
A covariance sheet with the sample, correlation, and R-squared variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate covariance in Excel?
Use =COVAR(range1, range2) for population covariance, or =COVARIANCE.S(range1, range2) for the sample version. The sign shows whether the variables move together or oppositely.
What's the difference between covariance and correlation?
Correlation is covariance divided by the product of the two standard deviations, giving a unit-free score from -1 to +1. Use =CORREL() when you want a comparable strength measure.
Why do I get a #N/A error?
The two ranges must contain the same number of paired values. Mismatched lengths cause #N/A.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Correlation · Standard deviation · Linear forecast

Function references: COVAR · CORREL