Excel VAR Function — Calculate Variance (with Examples)

What does it do?

VAR calculates the variance of a sample dataset — the average of the squared differences from the mean. Variance measures the spread of your data. It is closely related to standard deviation (VAR = STDEV²). VAR is used in statistics, quality control, financial analysis, and any field where understanding data spread matters.

Syntax

=VAR(number1, [number2], ...)

Argument	Description
=VAR(number1, [number2], ...)
number1 required	The first value or range. Usually a cell range.
[number2] ... optional	Additional ranges.

Real examples

Example 1

Basic variance of a sample

=VAR(B2:B100)

Returns the sample variance.

Example 2

Verify VAR equals STDEV squared

=VAR(B2:B100) = STDEV(B2:B100)^2

Should return TRUE.

Example 3

Portfolio variance (simplified)

=VAR(C2:C13)

Monthly returns variance — multiply by 12 to annualise.

Example 4

Compare variance across groups

=VAR(B2:B50) / VAR(C2:C50)

Ratio greater than 1 means group 1 is more variable.

Example 5

Flag high-variance items

=IF(VAR(B2:B13)>1000000,"High variance","Stable")

FAQ

What is the difference between VAR and VARP?

VAR calculates variance for a sample (most common — you have a subset of data). VARP calculates for the entire population. Use VAR unless you have every possible data point.

What is the relationship between VAR and STDEV?

Standard deviation = square root of variance. STDEV(range)² = VAR(range). STDEV is usually more interpretable because it is in the same units as the original data.

When should I use VAR instead of STDEV?

Use STDEV for most practical interpretation. Use VAR when you need the variance for further mathematical calculations — such as portfolio theory, ANOVA, or regression analysis.

VAR vs STDEV — and when to use each

Variance and standard deviation measure the same thing — spread — but in different units. Standard deviation (STDEV) is in the same units as your original data: if you are measuring height in centimetres, STDEV is in centimetres. Variance (VAR) is in squared units: square centimetres. Because squared units are hard to interpret intuitively, STDEV is usually preferred for communication.

VAR is preferred for mathematical calculations. Many statistical formulas — ANOVA, regression coefficients, portfolio optimisation — use variance in their calculations because squared values behave more conveniently mathematically. If you are building a statistical model in Excel, you will use VAR. If you are explaining data spread to a manager, use STDEV.

The relationship is always: VAR(range) = STDEV(range)^2. Or equivalently STDEV(range) = SQRT(VAR(range)). They are two presentations of the same measurement.

💡 Sample vs population

VAR uses n-1 in the denominator (sample variance). VARP uses n (population variance). Use VAR unless you have every possible data point — which is rarely the case in business analysis.

Practise this formula live

ExcelPro has exercises covering this formula across multiple tracks. Free to start.

Try exercises →

Related formulas

STDEV AVERAGE CORREL PERCENTILE LARGE SUMPRODUCT

The Excel VAR Functionexplained simply