Calculate Variance In Excel

To calculate variance in Excel, use =VAR.S(range) for a sample or =VAR.P(range) when the range holds the whole population.

Why bother? Two columns with the same average can tell different stories, and variance is the number that shows it. One function does it. The only real decision: which of two. One for a sample, one for a complete group.

• VAR.S for a sample, partial data.
• VAR.P for the complete group.
• VARA and VARPA when text and TRUE or FALSE should count.
• VAR and VARP only in old workbooks.

Pick the wrong one, and we’d expect the result to be off. On small datasets, by a lot.

=VAR.S(A1:A10)
=VAR.P(A1:A10)

The first treats your numbers as a sample. The second treats them as the whole group you care about. Most business data comes as a sample, so the first one is the usual choice.

What variance tells you

It measures how far the numbers in a dataset spread from their average. A small value: the numbers cluster tightly. A large one: they swing widely.

Picture two salespeople at the same company. Both averaged the same monthly revenue last year. Identical on paper. Then you look closer. One stayed within a narrow band every single month. The other swung from terrible months to record months. Same average, completely different performers.

That’s what this number exposes. It helps when forecasting revenue, checking quality control data or comparing investment options. The average alone never gives the full picture.

What Excel does behind the function

1. It takes each value’s distance from the mean.
2. It squares that distance.
3. It adds the squares up.
4. It divides by the count, or by the count minus one.

The six functions to calculate variance in Excel

Six sounds excessive. There’s a reason, and the table makes it click.

The two names without a dot are the pre-2010 ones. Microsoft added the dotted names in Excel 2010, because the suffixes make the intent obvious. Both pairs still work today. Excel 2021, Excel 2024 and Excel for the web all take them.

The VAR.S function reference lists the old ones under compatibility functions.

Calculate variance in Excel: sample or population

This is where people slip. Get it wrong, and the result is mathematically incorrect for your question.

Population: you have all the data

Every single data point. Testing every widget that came off the line today? Population. Grading every student in your class? Population. An eight-person team with everyone’s score in the list counts too.

Sample: you have part of it

Feedback from 200 customers, out of 10,000 in total? Sample. Fifty transactions pulled out of a million for an audit? Also a sample.

Why there are two formulas

A sample tends to underestimate the true spread of the population it came from. So the sample function divides by n − 1, not by n. Statisticians call this Bessel’s correction, after the astronomer Friedrich Bessel.

Not sure which applies? Go with the sample version. Business data is nearly always a sample of something bigger, even when it doesn’t feel that way. We’d even count last year’s sales as a sample of how your shop performs.

How to calculate variance in Excel

The steps are the same for both functions. Only the name changes.

1. Click an empty cell.
2. Type an equals sign and the function name, then an opening bracket.
3. Select the range with your numbers.
4. Close the bracket and press Enter.

Excel ignores empty cells, text and logical values inside the range.

A faster way to type it: start with the equals sign and the first letters. Excel offers a list. Arrow down to the function you want and press Tab. It gets inserted with the opening bracket.

The status bar at the bottom shows average, count and sum for a selection. Variance isn’t among its options, so you need the function.

A sample: ten months of revenue

Monthly revenue sits in B2 through B11:

1200, 1350, 1180, 1420, 1290, 1380, 1150, 1310, 1400, 1260

Click any empty cell, D2 for example, and enter:

=VAR.S(B2:B11)

Excel returns 9071.11, rounded. That number looks odd, because it’s measured in squared units. Nobody thinks in squared dollars.

So take the square root:

=STDEV.S(B2:B11)

About 95.24. Back in dollars, and easy to read. A typical month lands roughly 95 dollars away from the average of 1294.

For reports, wrap the result in ROUND. The underlying calculation stays exact, and only the displayed value changes.

=ROUND(VAR.S(B2:B11),2)

A population: a team of eight

You manage eight people and tracked everyone’s scores in C2 through C9. Nobody is missing from the list.

82, 91, 78, 88, 95, 84, 89, 93

Enter this in an empty cell:

=VAR.P(C2:C9)

Excel returns 29.25. The sample function on the same data gives about 33.43. The gap is the divisor, 8 against 7.

On large datasets the two results get very close. On small ones, like this team, the choice matters. The population function always returns the smaller value on the same data. The VAR.P function reference shows the formula behind it.

Reading the number you get

A variance says little by itself. 9071 could be tiny or huge, depending on the size of the values behind it. Two habits make it readable.

• Compare it with another variance in the same unit. Two months, two teams, two machines.
• Turn it into a standard deviation and set it next to the average. We’d call 95 dollars around an average of 1294 easy to judge.

Zero is the one value that needs no context. Every number in the range is the same.

The calculation by hand

Doing it once by hand removes the mystery. Take five daily scores: 94, 92, 95, 93, 91.

1. The mean: 465 divided by 5 is 93.
2. The deviations from the mean: 1, −1, 2, 0, −2.
3. The squared deviations: 1, 1, 4, 0, 4. Their sum is 10.
4. Sample variance: 10 divided by 4 is 2.5. Population variance: 10 divided by 5 is 2.

You can rebuild the same thing in Excel with two functions. DEVSQ returns the sum of squared deviations, the 10 from step 3. COUNT returns n.

=DEVSQ(A2:A6)
=DEVSQ(A2:A6)/(COUNT(A2:A6)-1)

The second line gives 2.5, exactly what the sample function returns for A2 through A6. Handy when a colleague asks where the number comes from.

Old workbooks with the old names

You’ll meet VAR and VARP in files that have been around for years. They aren’t wrong. They return exactly what the dotted versions return.

We’d leave them alone in a workbook that other people still open in very old Excel. In anything new, use the dotted names. The next reader sees at a glance whether you meant a sample or a population.

Several ranges at once

Both functions accept up to 254 arguments. Each can be a range, a single cell or a typed number. Separate them with commas. Excel combines every value into one dataset first, then returns a single variance for the whole set.

=VAR.S(A2:A10,C2:C10,E2:E10)
=VAR.S(A2:A10,55,78,D5)
=VAR.S(Jan!B2:B32,Feb!B2:B29)

Variance with a condition

Excel has AVERAGEIF and COUNTIF. It has no VARIF. FILTER fills the gap: feed a filtered list into the sample function.

=VAR.S(FILTER(B2:B100,A2:A100="North"))

FILTER came with the dynamic array engine in Office 2021, together with XLOOKUP and LET. So the Office 2021 and 2024 desktop releases have it. Microsoft 365 and Excel for the web have it too.

Two conditions? Multiply them inside FILTER:

=VAR.S(FILTER(C2:C100,(A2:A100="North")*(B2:B100=2026)))

On older Excel there’s an array formula for the same job. Confirm it with Ctrl+Shift+Enter:

=VAR.S(IF(A2:A100="North",B2:B100))

Text and TRUE or FALSE in the range

The two main functions ignore text, logical values and empty cells inside a range. VARA and VARPA count them. TRUE becomes 1. FALSE and text become 0. Empty cells are ignored by all four.

Use the A versions only when those zeros and ones really belong in the calculation. A pass or fail column, for example.

=VARA(D2:D15)

One gotcha. Text that isn’t a number, typed straight into the formula, breaks it:

=VARA("test",5,10)

Excel answers with #VALUE! there. Text has to live in a cell to be counted as 0. Text that looks like a number, typed into the argument list in quotes, gets converted and counted.

Calculate variance in Excel for two production lines

Two lines, five days, the same average of 93 points.

Run the sample function on both columns:

=VAR.S(A2:A6)
=VAR.S(B2:B6)

The first returns 2.5. The second returns 56. That’s more than 22 times higher, on the same average.

The first line is consistent, and the second is unpredictable. For quality control we’d take the first without thinking. As standard deviations the gap gets intuitive: about 1.6 points against about 7.5.

Where it earns its keep

Forecasting

Two products sell the same on average. One has a low variance month to month, the other a high one. The steady product can be planned to the unit. The jumpy one needs a buffer in stock and in cash.

Quality control

A filling machine is supposed to put the same amount in every bottle. The average can be perfect while the variance creeps up. That creep is the early warning, long before the average moves.

Comparing options

Two investments with the same average return aren’t the same investment. The one with the higher variance swings harder in both directions. Whether that’s acceptable is your call, and the number at least makes it visible.

Common mistakes when you calculate variance in Excel

• The population function on sample data. The number comes out too low. A poll of 100 people in a city of half a million isn’t the whole population.
• Numbers stored as text. Imported CSV data often arrives as text, with a small green triangle in the cell corner. Those cells get skipped without a warning. We’d run COUNT on the range. A result below the row count gives it away.
• Cells that look empty but aren’t. A space character, or a formula that returns an empty string, isn’t truly blank. The main functions ignore them. VARA counts them as 0.
• Outliers. Deviations are squared, so one wild number inflates the result a lot. Scan for typos and real anomalies with MIN and MAX first.
• Squared units in a meeting. Revenue variance is in dollars squared. That tells most people nothing. Take the square root.

The sample function needs at least two numeric values. With one value or none it returns #DIV/0!, because n − 1 is zero.

Quick checks before you trust the result

• COUNT on the range matches the number of rows you expect.
• MIN and MAX look plausible. No extra zero, no missing decimal point.
• The range covers the data and nothing else. No header, no total row at the bottom.
• You picked sample or population on purpose, not by habit.
• The cell with the result is formatted as a number, not as a date or a percentage.

A total row is the classic case. Include it by accident, and one giant value wrecks the variance of everything above it.

Calculate variance in Excel between two numbers

A different “variance”, same word. In budgets and sales reports it means the gap between two figures. Plan against actual. Last year against this year. No statistics function involved.

Plan in B2, actual in C2. The gap in money:

=C2-B2

The gap as a percentage of the plan:

=(C2-B2)/B2

Format that cell as Percentage. Plan 1200, actual 1350, and Excel shows 12.5%.

Two traps here.

• A zero plan. Division by zero, so #DIV/0!. Wrap it: =IFERROR((C2-B2)/B2,"").
• A negative plan. A loss budget flips the sign of the result. Divide by the absolute value instead: =(C2-B2)/ABS(B2).

Costs read the other way round. Spending above plan is the bad direction, so some teams swap the order to =B2-C2 for cost lines. Pick one rule per report and say which in the header.

Calculate variance in Excel with a pivot table

A PivotTable has both statistical variances built in. No formula needed.

1. Drag the number field into Values. It starts as Sum.
2. Right-click any value in that column.
3. Summarize Values By, then More Options.
4. Var for a sample, Varp for the full population. OK.

Var behaves like VAR.S and Varp like VAR.P, one result per row of the pivot. Region by region, month by month.

Want the sum and the variance side by side? Drag the same field into Values a second time and set only the copy to Var.

The budget-style gap? Not there. Show Values As, then Difference From or % Difference From, plus the base item to compare against.

Every option is in changing the summary function in a PivotTable, on Microsoft Support. One catch. OLAP-based pivots don’t get these functions.

Functions that pair well with it

On its own the result tells part of the story. It usually pairs with these four.

For budget tracking, conditional formatting can flag any month where spending is more than one standard deviation from plan. Outliers become obvious at a glance. We’d add a column of checkboxes in Excel to mark the flagged months as reviewed.

A full statistics table

Turn on the Analysis ToolPak first.

1. Open File, then Options, then Add-ins.
2. Choose Excel Add-ins and click Go.
3. Tick Analysis ToolPak.
4. Go to Data, Data Analysis, Descriptive Statistics.

It lists the mean, standard deviation, sample variance, range, minimum, maximum and more for the selected column.

Excel versions where the variance functions work

The example values were checked by recalculating them.

On an older Excel and missing FILTER, XLOOKUP and LET? Our overview of Microsoft Office versions shows which release added what.

Need a key? We sell Microsoft Office Home and Business 2024 as a genuine one-time license for PC or Mac. The current Excel is in it. The key is emailed instantly after payment. Other editions are in the Office 2024 category. Office 2021 Professional Plus works too, with the older Excel.

The bottom line on how to calculate variance in Excel

Partial data takes the sample function. Complete data takes the population one. In most cases it’s the first. Text or TRUE and FALSE you want counted? Switch to the A versions. A result nobody can read? Take the standard deviation.

And keep the DEVSQ check in your back pocket. When someone questions a figure, rebuilding it takes one cell and settles the argument.

Related guides

• How to add a watermark in Excel
• Calculator keyboard shortcuts