How to Calculate Variance in Excel With VAR.S and VAR.P

To calculate variance in Excel, type =VAR.S(range) if your numbers are a sample, or =VAR.P(range) if they are the whole group you care about. Variance measures how spread out values are around their average. This guide shows which function to choose, how to work the number out by hand so you understand it, and how variance relates to standard deviation.

What is variance?#

Variance is the average of the squared distances between each value and the mean (the average). A small variance means the values sit close together. A large one means they are spread out.

Squaring does two jobs: it stops values below the mean from cancelling out values above it, and it gives big gaps more weight than small ones. The cost is that the result is in squared units. If you measure tickets, variance is in "tickets squared", which is hard to picture. That's why people often report the standard deviation, the square root of variance, which is back in the original units.

All the examples use six days of support tickets in B2:B7:

The average is 13 tickets a day. The AVERAGE function guide covers that first step.

How to calculate variance with VAR.S and VAR.P#

Select an empty cell and type the function with your range:

=VAR.S(B2:B7)
=VAR.P(B2:B7)

Both functions ignore empty cells, text and TRUE/FALSE values inside a range. VAR.S needs at least two numbers, or it returns #DIV/0!.

VAR.S or VAR.P: which should you use?#

Use VAR.S unless your data truly contains every member of the group you're describing. These six days are probably a sample of a longer period, so VAR.S is the right choice for them.

• VAR.S (sample): six days out of a year, 50 survey replies out of 10,000 customers, a batch of products tested from a production run.
• VAR.P (population): every employee on one team, all 12 months of a finished year when you only care about that year, every exam score in a class when you're describing only that class.

Why divide by n − 1 for a sample? A sample tends to miss the most extreme values in the full group, so its spread comes out a little too small. Dividing by one less than the count makes the result slightly bigger and corrects for that. The gap matters for small samples and almost disappears for large ones. The same choice applies to standard deviation; see STDEV.S vs STDEV.P for a side-by-side comparison.

How to calculate variance by hand#

Working through the steps once shows exactly what VAR.S does. Add helper columns next to the data:

1. Find the mean: =AVERAGE(B2:B7) gives 13.
2. Subtract the mean from each value: in C2, =B2-AVERAGE($B$2:$B$7), filled down.
3. Square each difference: in D2, =C2^2, filled down.
4. Add the squares: =SUM(D2:D7) gives 56.
5. Divide: by n − 1 for a sample (56 ÷ 5 = 11.2) or by n for a population (56 ÷ 6 = 9.33).

You can do all five steps in one cell with SUMPRODUCT, which multiplies and adds arrays without helper columns:

=SUMPRODUCT((B2:B7-AVERAGE(B2:B7))^2)/(COUNT(B2:B7)-1)

The result is 11.2, the same as VAR.S. Replace COUNT(B2:B7)-1 with COUNT(B2:B7) to match VAR.P. Excel also has DEVSQ, which returns the sum of squared differences directly: =DEVSQ(B2:B7) gives 56.

Variance and standard deviation#

Standard deviation is simply the square root of variance, so you can always get one from the other:

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

The result is about 3.35 tickets, the same as =STDEV.S(B2:B7). Going the other way, =STDEV.S(B2:B7)^2 gives 11.2. Report standard deviation when you want people to understand the spread ("about 3 tickets either side of 13"). Use variance when a formula needs it, for example when you combine the spread of independent parts, because variances add up and standard deviations don't. For more, read how to calculate standard deviation in Excel.

To see how one extreme value affects the centre of the data, compare the mean with the median; the guide to MEDIAN and MODE shows how.

Other variance functions and versions#

Excel has a few related functions you may see in older files:

VAR.S and VAR.P work in Excel 2010 and later, including Microsoft 365 and Excel for Mac. For the variance of one group only, such as North sales, Microsoft 365 users can write =VAR.S(FILTER(C2:C50,B2:B50="North")).

Common variance mistakes#

These slips cause most wrong answers:

1. Using VAR.P for a sample. It gives a smaller number that understates the real spread. When in doubt, use VAR.S.
2. Numbers stored as text. VAR.S skips them in a range, so the result is based on fewer values than you think. Check with COUNT.
3. Mixing up the two meanings of variance. In budgeting, "variance" means actual minus budget, such as 5,200 − 5,000 = 200 over budget. That's a simple subtraction, not VAR.S. See budget vs actual variance.
4. Reading variance as if it were in normal units. A variance of 11.2 tickets squared doesn't mean "about 11 tickets either side". Take the square root first.
5. Averaging values that have different weights. If each value stands for a different number of items, start with a weighted average instead of a plain one.

Practise it#

Try the exercise on this page: you rebuild VAR.S from AVERAGE, SUMPRODUCT and COUNT, so the formula stops being a black box. Then practise AVERAGE, MIN and MAX in a live spreadsheet and move on to the MEDIAN and MODE lesson, where each answer is checked instantly. For a wider summary toolkit, see AVERAGE, MIN and MAX explained.