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

Trying to calculate variance in Excel? Two formulas: =VAR.S(A1:A10) when you have sample data, =VAR.P(A1:A10) when you have the whole dataset. Most people need VAR.S. Pick wrong and your numbers are off — I’ll show you which to use.

Why Bother with Variance?

It shows how spread out your numbers are. Tight cluster around the average? Low variance. Numbers jumping all over? High variance.

Picture two salespeople at the same company. Both pulled in $50k average monthly last year. Looks identical on paper. Then you dig deeper. One stayed between $48k and $52k every single month. The other swung from $20k to $80k. Totally different performers, same average. That’s what variance exposes.

I use this formula when forecasting revenue, checking quality control data, or comparing investment options. The average alone never gives you the full picture.

Excel Has Six Functions for This

Six functions sounds excessive. Trust me, there’s a reason. Once you see the breakdown it clicks.

Numbers only:

• VAR.S and VAR handle sample data (they’re the same thing)
• VAR.P and VARP handle population data (also identical)

Data with text or TRUE/FALSE:

• VARA for samples
• VARPA for populations

VAR and VARP are the old versions from Excel 2007. Microsoft added VAR.S and VAR.P in 2010 because the names are clearer. Both work fine. The Microsoft documentation recommends the newer ones.

Sample vs Population: Pick the Right One

This is where people mess up. Get it wrong and your result is mathematically incorrect.

VAR.P (population) is for when you have ALL the data. Every single data point. Testing every widget that came off the line today? That’s a population. Grading every student in your class? Population.

VAR.S (sample) is for partial data. Grabbed feedback from 200 customers when you have 10,000 total? That’s a sample. Pulled 50 transactions to audit from a million? Also a sample.

Why two formulas? Samples naturally underestimate the true spread in a population. To fix that bias, VAR.S divides by (n-1) instead of n. Statisticians named this trick after a guy called Bessel.

Not sure which applies? Go with VAR.S. Most business data involves samples.

Calculate Sample Variance in Excel (VAR.S)

Got monthly revenue sitting in B2 through B11? Say the numbers are 1200, 1350, 1180, 1420, 1290, 1380, 1150, 1310, 1400, 1260.

Click any empty cell. Type =VAR.S(B2:B11) and hit Enter.

You’ll get 8177.78. That’s the variance.

8177.78 looks weird because variance is measured in squared units. Nobody thinks in squared dollars. Take the square root with =STDEV.S(B2:B11) and you get about 90.4. Now it’s back in regular dollars and actually makes sense.

Calculate Population Variance in Excel (VAR.P)

Same deal, swap the function. You manage 8 people and tracked everyone’s scores: 82, 91, 78, 88, 95, 84, 89, 93. Nobody missing from the list.

Punch in =VAR.P(C2:C9) and Excel spits out 28.5.

Curious what VAR.S gives on that same data? 32.57. The population version always runs lower — it divides by n, not (n-1). The VAR.P documentation breaks down why.

Multiple Ranges in One Formula

Data spread across different columns? No problem. Just separate ranges with commas.

=VAR.S(A2:A10,C2:C10,E2:E10)

You can mix ranges, individual cells, and even typed numbers:

=VAR.S(A2:A10,55,78,D5)

Excel combines everything into one dataset before calculating.

Text and TRUE/FALSE Values

VAR.S and VAR.P skip anything that isn’t a number. Text? Ignored. TRUE/FALSE? Ignored. Empty cells? Ignored.

Want those non-numbers included? VARA and VARPA handle that. They read TRUE as 1, FALSE as 0, and any text gets treated as 0.

Looks like this: =VARA(D2:D15)

One gotcha: typing text straight into the formula — something like =VARA("test",5,10) — breaks it. Excel throws #VALUE. The text needs to live in a cell, not in the formula itself.

Real Example: Quality Control

Two production lines at a factory. Both hit 93 average quality score over five days. Sounds equal until you check the daily breakdown:

Line A daily: 94, 92, 95, 93, 91
Line B daily: 88, 99, 78, 97, 93

Run the formula on both:

=VAR.S(A2:A6) gives 2.5 for Line A
=VAR.S(B2:B6) gives 64.5 for Line B

Line B has 25x more spread. Same average, but Line A is consistent while Line B is unpredictable. For quality control, Line A wins easily.

Common Variance Mistakes in Excel

Using VAR.P on sample data. Your number comes out too low. If you polled 100 people in a city of half a million, that’s not the whole population — use VAR.S.

Cells that look empty but aren’t. A space character or a formula returning “” isn’t truly blank. Excel won’t skip it. If your numbers seem off, check for hidden characters.

Outliers wrecking everything. One wild number can blow up your results. Before running the formula, scan for typos or genuine anomalies.

Expecting intuitive results. The result is in squared units. Revenue variance is in dollars-squared, which means nothing to most people. Take the square root (standard deviation) for something easier to explain in meetings.

Quick Reference Table

Function	Data Type	When to Use	Divides By
VAR.S	Sample	Partial data, Excel 2010+	n-1
VAR.P	Population	Complete data, Excel 2010+	n
VAR	Sample	Legacy spreadsheets	n-1
VARP	Population	Legacy spreadsheets	n
VARA	Sample	Has text or TRUE/FALSE	n-1
VARPA	Population	Has text or TRUE/FALSE	n

Pair This with Other Functions

The result by itself only tells part of the story. I usually combine it with:

AVERAGE to see the center point
STDEV.S or STDEV.P to convert variance into something readable
MIN and MAX to spot the range
QUARTILE to see distribution

For budget tracking, I use conditional formatting to flag any month where actual spending is more than one standard deviation from plan. Makes outliers obvious at a glance.

Working with large datasets? A licensed copy of Excel handles them better than the web version. We sell Microsoft Office keys if you need one.

FAQ

What is the difference between VAR.S and VAR.P in Excel?

VAR.S assumes you’re working with a slice of the data, so it uses (n-1) to compensate. VAR.P assumes you have literally everything, so it uses plain n. Partial data means VAR.S. Complete data means VAR.P.

Which variance function should I use in Excel?

Probably VAR.S. Business data almost never includes the entire population. VAR.P only makes sense when you literally measured every single thing, like scoring all 8 employees on a small team or testing every product in a batch.

Why does Excel show different results for VAR and VAR.S?

It doesn’t — they’re identical. Microsoft just renamed VAR to VAR.S back in 2010 so people would stop confusing it with VAR.P. Under the hood, same exact calculation.

How do I calculate variance for data with text values?

VARA handles samples, VARPA handles populations. Both turn text into 0, TRUE into 1, FALSE into 0. The standard VAR functions pretend non-numbers don’t exist.

Can I calculate variance across multiple ranges in Excel?

Sure. Drop commas between each range: =VAR.S(A1:A10,C1:C10,E1:E10). Excel lumps everything together and runs the calculation on the combined set.

Bottom Line

To calculate variance in Excel, use =VAR.S(range) when you have partial data, =VAR.P(range) when you have everything. 99% of the time it’s VAR.S. Data has text or TRUE/FALSE mixed in? Switch to VARA or VARPA. Result looks like nonsense? That’s because it’s in squared units. Run STDEV.S instead and you’ll get a number that actually makes sense to humans.