Excel VAR/VAR.P/VAR.S Functions

last modified October 5, 2026

The VAR family of functions measures how far numbers spread out from their average. Excel offers three variants: the modern VAR.S for samples, VAR.P for populations, and the legacy VAR. This tutorial provides a comprehensive guide with detailed examples. You'll learn when to use each function, how they differ, and practical applications to analyze data variability effectively in your spreadsheets.

Variance Functions Basics

Variance is a statistical measure of dispersion. It is the average of the squared deviations from the mean, so it is expressed in squared units. Excel provides three variance functions that share the same syntax and differ only in the denominator they use.

Component	Description
Function Name	VAR, VAR.S, VAR.P
Syntax	=VAR.S(number1, [number2], ...)
Arguments	1-255 values
Return Value	Variance of the arguments

This table breaks down the essential components of the variance functions. The syntax shown for VAR.S applies equally to VAR.P and the legacy VAR.

Argument	Meaning
number1	Required. The first number, cell reference, or range to analyze.
number2, ...	Optional. Additional numbers, cell references, or ranges, up to a total of 255 arguments.

Inside a range, these functions ignore empty cells, text, and logical values, but they include cells that hold the number zero. Logical values and text that looks like a number, when typed directly into the argument list, are counted instead. The key decision is not how to pass the data but whether the data is a sample or a population.

Sample vs Population Variance

VAR.S divides the squared deviations by n-1 and VAR.P divides them by n. The n-1 divisor, called Bessel's correction, makes the sample result an unbiased estimate of the population spread. For large samples the two values converge.

Feature	VAR.S (legacy VAR)	VAR.P (legacy VARP)
Data assumed to be	A sample of the population	The entire population
Denominator	n-1	n
Purpose	Estimate spread from partial data	Describe the exact spread of all data
Single numeric value	#DIV/0! error	Returns 0
Companion function	STDEV.S	STDEV.P

Use VAR.S whenever your data is a subset, such as polled users or sampled products, and VAR.P only when every member of the group is present, such as all students in one class.

=VAR.S({1,2,3,4,5})          ' 2.5
=VAR.P({1,2,3,4,5})          ' 2
=VAR.S({2,4,4,4,5,5,7,9})    ' 4.5714
=VAR.P({2,4,4,4,5,5,7,9})    ' 4

The same data produces a larger sample variance because dividing by n-1 gives more weight to each squared deviation. The difference shrinks as the dataset grows, so for a large sample the two results are nearly identical.

Legacy Names: VAR and VARP

Before Excel 2010 the two functions were named VAR and VARP. VAR is the legacy sample function, identical to VAR.S, and VARP is the legacy population function, identical to VAR.P. Both are kept for backward compatibility and return the same results as their modern counterparts.

=VAR(A1:A5)     ' same result as =VAR.S(A1:A5)
=VARP(A1:A5)    ' same result as =VAR.P(A1:A5)

The legacy names still work in every new workbook, but Microsoft recommends VAR.S and VAR.P for new formulas because the meaning is spelled out in the name.

Basic VAR.S Example

This example demonstrates calculating sample variance using VAR.S with a small dataset.

A	B
10
12
14
16
18
	=VAR.S(A1:A5)

The table shows a simple dataset in column A and the VAR.S formula in cell B6. The mean of these five values is 14.

=VAR.S(A1:A5)

This formula returns exactly 10. The squared deviations from the mean are 16, 4, 0, 4, and 16, which total 40; dividing by n-1 (4) gives 10. This is the sample variance, appropriate when your data represents a sample of a larger population.

VAR.P for Population Variance

This example shows how to calculate population variance using VAR.P when you have data for the entire population.

A	B
5
7
9
11
	=VAR.P(A1:A4)

The table contains a complete population dataset in column A. The mean of these values is 8.

=VAR.P(A1:A4)

This formula returns exactly 5. The squared deviations from the mean are 9, 1, 1, and 9, which total 20; dividing by n (4) gives 5. VAR.P uses n in the denominator, so use it when your data includes all members of the population you're studying.

Comparing VAR and VAR.S

This example demonstrates that VAR and VAR.S produce identical results, as they both calculate sample variance.

A	B	C
15
20
25
30
	=VAR(A1:A4)	=VAR.S(A1:A4)

The table shows the same dataset being analyzed with both VAR and VAR.S functions. Cells B5 and C5 contain these formulas respectively.

=VAR(A1:A4)
=VAR.S(A1:A4)

Both formulas return the same result, approximately 41.6667. VAR is the legacy function, while VAR.S is the newer recommended version. Microsoft recommends using VAR.S for clarity in new workbooks.

Handling Text and Logical Values

Variance functions automatically ignore text and logical values found inside a range. This example demonstrates this behavior.

A	B
10
15
Text
TRUE
20
	=VAR.S(A1:A5)

The table contains a mix of numbers, text, and logical values. The VAR.S formula in B6 calculates variance using only the numeric values.

=VAR.S(A1:A5)

This formula calculates variance using only the numeric values 10, 15, and 20. The text and logical values are ignored, so the result is approximately 25. This behavior makes variance functions robust with real-world datasets. Note that the rule applies to values inside a reference: if you type TRUE or a number stored as text directly into the argument list, Excel counts it instead of ignoring it.

Variance with Blank Cells

Variance functions treat blank cells as missing data and exclude them from calculations. This example shows this behavior.

A	B
8
12
16
	=VAR.S(A1:A4)

The table contains numbers and blank cells to demonstrate how VAR.S handles empty cells. The blank cells are excluded from the variance calculation.

=VAR.S(A1:A4)

This formula calculates variance using only the non-blank cells 8, 12, and 16. The result is approximately 16. Blank cells don't affect the calculation as they're treated as missing data rather than zeros. A cell that literally contains 0, by contrast, is a valid value and is included.

VAR vs VARA

The companion function VARA estimates the sample variance but, unlike VAR.S, it counts text and logical values found in references: TRUE is read as 1, while FALSE and any text are read as 0. Empty cells are still ignored. The population counterpart is VARPA.

A	B
10
TRUE
20
	=VAR.S(A1:A3)
	=VARA(A1:A3)

For the same range, VAR.S returns 25 because it sees only the numbers 10 and 20. VARA returns approximately 90.33 because it counts TRUE as the value 1 and computes the spread of 10, 1, and 20.

=VAR.S(A1:A3)  ' 25, TRUE is ignored
=VARA(A1:A3)   ' 90.33, TRUE is counted as 1
=VAR.P(A1:A3)  ' 16.67, population variance
=VARPA(A1:A3)  ' 60.22, population variance counting TRUE as 1

Relationship to STDEV Functions

Variance is the square of standard deviation, so each variance function has a matching standard deviation function: VAR.S equals STDEV.S(...) squared and VAR.P equals STDEV.P(...) squared, equivalently STDEV.S is SQRT(VAR.S(...)). Variance expresses spread in squared units, while standard deviation is back in the original units, which is why it is usually easier to interpret.

=VAR.S(A1:A3)        ' 25, variance of 10, 15, 20
=STDEV.S(A1:A3)      ' 5, the square root of 25
=VAR.P(A1:A3)        ' 16.67, population variance
=STDEV.P(A1:A3)      ' 4.08, the square root of 16.67

The relationship holds exactly: STDEV.S(A1:A3)^2 returns 25, the same value as VAR.S(A1:A3). Because the standard deviation is in the same units as the data, it is often the more intuitive number to report.

Choosing Between VAR.S and VAR.P

The key difference between VAR.S and VAR.P is in their denominators. VAR.S uses n-1 (sample variance) while VAR.P uses n (population variance).

Use VAR.S when your data is a sample of a larger population. This gives an unbiased estimate of the population variance. Use VAR.P when your data includes all members of the population you're studying.

For example, if measuring heights of all students in a class, use VAR.P. If measuring heights of 30 randomly selected students from a school of 300, use VAR.S.

Variance Errors and Troubleshooting

Cause	Symptom	Fix
Fewer than two numeric values for a sample	#DIV/0! error	Supply more values, or use VAR.P for a complete dataset
Text or logical cells inside the range	Result uses fewer values than expected	Use VARA or VARPA, or convert numbers stored as text with VALUE
Range contains an error value	The same error is returned	Remove the error in the source cells first
Sample function used on a full population	VAR.S slightly larger than VAR.P	Expected behavior of the n-1 divisor; pick the function by data scope
Unconvertible text typed directly as an argument	#VALUE! error	Pass numbers or cell references instead
Logical value or numeric text typed directly as an argument	It is counted, unlike inside a range	Wrap the values in a range reference if they should be ignored

The variance functions are essential for measuring data dispersion in Excel. Choose VAR.S (or the legacy VAR) when your data is a sample and VAR.P (or the legacy VARP) when you have the entire population; the n-1 versus n denominator is the only mathematical difference between them. Both ignore empty cells, text, and logical values inside ranges but include zeros, and both propagate any error found in the referenced cells. When text and logical values must count, switch to VARA and VARPA, and when you need spread in the original units, take the square root through STDEV.S and STDEV.P. Picking the right variant keeps your variability measurements accurate and easy to explain.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.