How to Calculate Variance in Excel: Functions, Formulas, and When to Use Each

Variance tells you how spread out your data is — specifically, how far individual values tend to deviate from the mean. A small variance means your data clusters tightly around the average; a large variance means it's scattered. In Excel, calculating variance is straightforward once you know which function matches your data situation, because Excel offers several variance functions and they don't all produce the same result.

What Variance Actually Measures

Before touching a formula, it helps to understand what you're calculating. Variance is the average of the squared differences from the mean. Squaring the differences ensures negative and positive deviations don't cancel each other out, and it amplifies larger deviations — making variance sensitive to outliers.

The formula in plain terms:

1. Find the mean of your dataset
2. Subtract the mean from each value
3. Square each of those differences
4. Average the squared differences

Excel handles all of this automatically. Your job is picking the right function.

The Core Excel Variance Functions

Excel provides six variance-related functions, split into two categories: population variance and sample variance.

For most users working with modern Excel, VAR.S and VAR.P are the two you'll use regularly. The legacy functions (VAR and VARP) still work but Microsoft recommends their successors.

Sample vs. Population: The Critical Distinction 📊

This is where most people get tripped up.

Population variance (VAR.P) assumes your dataset is the entire group you care about. If you have exam scores for every student in one specific class and that class is your complete subject of analysis, you're working with a population.

Sample variance (VAR.S) assumes your dataset is a subset drawn from a larger group. If those exam scores represent a random sample from a school district, you're working with a sample.

The mathematical difference: sample variance divides by (n − 1) instead of n (where n is the number of data points). This adjustment — called Bessel's correction — compensates for the tendency of a sample to underestimate the true population variance. The larger your dataset, the smaller the practical difference between the two. With small datasets, the choice matters considerably.

Rule of thumb: If you collected a sample to represent something bigger, use VAR.S. If your data is the whole population, use VAR.P. When in doubt, VAR.S is the safer default in most analytical contexts.

How to Enter a Variance Formula in Excel

The syntax is simple and consistent across all variance functions:

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

You can reference individual cells, a range, or multiple ranges:

=VAR.S(B2:B50) 

=VAR.P(A1:A20, C1:C20) 

=VAR.S(B2, B5, B9) 

Excel ignores empty cells and cells containing text when using VAR.S or VAR.P. If you need text values like "TRUE" or "FALSE" treated as 1 and 0, switch to VARA or VARPA.

Practical Step-by-Step Example

Say you have monthly sales figures in cells B2 through B13 (one year of data) and want to understand how much those numbers vary month to month.

1. Click an empty cell where you want the result
2. Type =VAR.S(B2:B13)
3. Press Enter

Excel returns a single number — the variance. A result of 12,500, for instance, means the squared average deviation from your mean monthly sales is 12,500 units. That number is harder to interpret intuitively, which is why many analysts follow up variance with standard deviation (the square root of variance) using STDEV.S — it returns the spread in the same units as your original data.

When You Might Use VARA or VARPA

The VARA and VARPA functions become relevant when your dataset includes logical values (TRUE/FALSE) or text representations of numbers stored in cells. VARA treats TRUE as 1, FALSE as 0, and text as 0. This matters in datasets built from survey responses, conditional flags, or imported data where values weren't entered as clean numbers.

If your data is purely numeric — which covers most spreadsheet work — VAR.S and VAR.P are cleaner and less likely to produce unexpected results from stray non-numeric entries.

Factors That Affect Which Function Fits Your Work 🎯

Several variables shape which variance approach makes sense:

• Dataset size: With hundreds of data points, sample vs. population variance produces nearly identical results. With 10–20 points, the difference is meaningful.
• Data source: Survey data and scientific samples call for VAR.S. Census-style or complete records call for VAR.P.
• Data cleanliness: Mixed data types (text in numeric columns) require VARA/VARPA or a cleanup step first.
• Downstream use: If you're feeding variance into further statistical calculations — regression, confidence intervals, hypothesis testing — the sample/population distinction carries forward and compounds.
• Excel version: Files shared with users on Excel 2007 or earlier may need the legacy VAR/VARP functions to avoid compatibility issues.

Variance Across Different User Scenarios

A small business owner tracking monthly expenses is almost certainly working with a complete dataset — all their expenses — making VAR.P technically appropriate. A data analyst pulling customer transaction records from a database as a random sample should reach for VAR.S. A student analyzing a lab experiment with five measurements is dealing with a sample, even if it doesn't feel like one.

The function that's "correct" changes entirely based on what the data represents and how the results will be used — not just on what the numbers look like.