Variance (VAR.S vs VAR.P)

All versionsVAR

Variance measures spread — the average squared distance from the mean — and is the square of the standard deviation. Use the sample version for a sample, the population version for the whole set.

Quick formula: sample variance of B2:B100:

=VAR.S(B2:B100)

Use VAR.S (sample, divides by n−1) or VAR.P (population, divides by n). Older names: VAR and VARP.

Functions used (tap for the full reference guide):

VAR VARP

The example

Spread expressed as variance.

	A	B
1	Measure	Value
2	Variance (sample)	104.2
3	Std dev (√var)	10.2

The formula

The formula:

=VAR.S(B2:B100) // sample variance

How it works

How it works:

VAR.S (or legacy VAR) divides by n−1 — the sample estimate.
VAR.P (or VARP) divides by n — the true population variance.
Variance is in squared units; take its square root for the standard deviation in the original units.
Use sample for data that’s a subset; population when you have every value.

Variance vs std dev: they carry the same information, but std dev is in the data’s units (dollars, points) and is easier to interpret. Variance is what the math runs on; report the std dev.

Try it: interactive demo

Live demo

Values; sample variance.

Variance · StDev

Variations

Population variance

Divide by n:

=VAR.P(B2:B100)

Std dev from variance

Square root:

=SQRT(VAR.S(B2:B100))

Legacy names

Any version:

=VAR(rng) / =VARP(rng)

Pitfalls & errors

Sample vs population. Using VAR.P on a sample understates the spread; pick the right one.

Squared units. Variance isn’t in the data’s units — root it for std dev.

Dotted names need 2010+. Use VAR/VARP in older Excel.

Practice workbook

📊

Download the free Variance (VAR.S vs VAR.P) practice workbook

A variance sheet with population, std-dev, and legacy variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate variance in Excel?

Use =VAR.S(range) for a sample (divides by n-1) or =VAR.P(range) for a population (divides by n). Legacy names are VAR and VARP.

What's the difference between variance and standard deviation?

Standard deviation is the square root of variance, expressed in the data's units. Variance is in squared units.

Which variance should I use?

Use the sample version (VAR.S) when your data is a subset, and the population version (VAR.P) when you have every value.

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: Standard deviation · Coefficient of variation · Range & IQR

Function references: VAR · VARP