Calculate Variance in Excel — Complete Guide (2026)

Calculate Variance in Excel — Complete Guide (2026)

Variance tells you how spread out your numbers are. A small variance means values cluster tight around the average. A large variance means they swing wide. That's it. Excel makes the math invisible — you point a function at a range, and the number drops into your cell.

Excel has four variance functions, and picking the wrong one will quietly give you the wrong answer. The two main choices are VAR.S (sample variance) and VAR.P (population variance). The other two — VARA and VARPA — handle text and logical values as zeros. Old workbooks may still use the legacy VAR function, which Excel keeps around for backward compatibility but maps to VAR.S behavior in Excel 2010 and later.

Short answer: if your data is a subset of something bigger (test scores from one classroom out of a district, last quarter's sales out of years of records), use VAR.S. If your data is the entire group you care about (every employee in the company, every defect on this production line today), use VAR.P. Pick wrong, and you'll either over- or under-estimate spread by a factor of n/(n-1).

The Excel syntax is dead simple. You write =VAR.S(A2:A100) for sample variance across that range, or =VAR.P(A2:A100) for population variance. No arguments to fiddle with, no array entry needed in Excel 365 — just a range. The function ignores empty cells, text, and logical values by default. If you want text and TRUE/FALSE counted as zeros, swap in VARA or VARPA. Most of the time you don't want that.

One more thing before we dig in: variance is the square of standard deviation. If you have variance and want standard deviation, wrap it in SQRT: =SQRT(VAR.S(A2:A100)). That gives you a spread measure in the same units as your data (dollars, points, inches) instead of squared units. Most reports show standard deviation for that reason — but variance is what you compute first, and it's what statistical tests like ANOVA actually use.

Why bother with variance at all when standard deviation is what people quote? Because variance plays nicely with math. When you add two independent variables, their variances add. Standard deviations don't. That additivity is why every regression model, every ANOVA, every portfolio risk calculation uses variance under the hood — even when the final report shows you a standard deviation. The function you're typing into Excel is the same one a quant analyst uses to model a $10 billion portfolio. Same math. Different stakes.

Excel 365 introduced dynamic arrays in 2018, and variance functions benefit even though their syntax didn't change. You can now pipe filtered data straight into VAR.S without Ctrl+Shift+Enter gymnastics. If your workbook is on Microsoft 365, you've got the upgrade automatically. Older standalone versions (Excel 2019, 2021) keep the legacy array behavior, which still works but requires the special key combo for IF-based filtering. Worth knowing which version you're on — it changes how clean your formulas can be.

Microsoft also renames variance functions in some Excel localizations. In German Excel it's VAR.S stays VAR.S in newer versions, but older European builds used VARIANZA or local equivalents. The English names always work on the engine side — Excel translates display names but stores formulas in a language-neutral format internally. So a workbook built in English Excel opens correctly in Spanish Excel, with the function name auto-translated for the viewer. That's why pasting English-language formulas from a website into a localized Excel usually still works.

How Variance Is Actually Calculated

Excel hides the math, but here it is. For sample variance with values x₁, x₂, … xₙ and sample mean x̄, the formula is Σ(xᵢ − x̄)² / (n − 1). For population variance with population mean μ, it's Σ(xᵢ − μ)² / n. Squared deviations, summed, divided by either n-1 or n. That's the whole story.

Walking Through the Math

Take a tiny sample: 4, 8, 6, 5, 3. Mean is (4+8+6+5+3)/5 = 5.2. Subtract the mean from each value: −1.2, 2.8, 0.8, −0.2, −2.2. Square those: 1.44, 7.84, 0.64, 0.04, 4.84. Sum the squares: 14.80. Divide by n-1 = 4 for sample variance: 14.80 / 4 = 3.70. Divide by n = 5 for population variance: 14.80 / 5 = 2.96. Standard deviation is the square root of each — about 1.92 (sample) or 1.72 (population).

You can reproduce this in Excel step-by-step using AVERAGE, SUMSQ, and a helper column for deviations. But there's no reason to. Type =VAR.S(A1:A5) and you get 3.70 instantly. The point of doing it by hand once is so you understand what the function is computing — not so you do it that way every time.

If you're learning Excel for a certification or job test, expect manual-calculation questions on the exam. They want to know you understand what n-1 is doing. Working through one or two by hand sticks the concept faster than a hundred function calls. Then trust the function on real data.

Why n-1 Instead of n?

Worth pausing on this. When you take a sample, you don't actually know the true population mean — you estimate it from the sample itself. That estimation eats up one "degree of freedom." The n-1 adjustment (Bessel's correction) compensates by making the sample variance a bit bigger, which turns out to be the unbiased estimator of the true population variance.

Skip that correction and your sample variance is systematically too small. With n=2 the difference is huge (dividing by 1 versus 2). With n=200 it's tiny. The fix is always the same: subtract 1 from the divisor whenever you're working with a sample.

Combining Variance with AVERAGE

Variance by itself is hard to interpret. Pair it with AVERAGE and you get context. Imagine two sets of sales figures: both average $50K per month, but Set A has variance of 4 and Set B has variance of 400. Set A is stable. Set B is wildly inconsistent. Same average, totally different business reality.

That's why dashboards almost always show mean and a spread measure side by side. In Excel: =AVERAGE(A2:A100) next to =VAR.S(A2:A100), or more often =STDEV.S(A2:A100) since standard deviation reads in the same units. For deeper data wrangling, excel pivot tables let you compute variance per group (per region, per product) with a single drag — much faster than writing a formula per slice.

Advanced Variance Techniques in Excel

Once you've nailed the basics, the next step is filtering data on the fly. Real spreadsheets rarely give you one clean column — you're slicing by region, by quarter, by product line. Excel has three ways to compute variance on a subset, and which one you pick depends on your version and how readable you want the formula to be.

Conditional Variance with IF

Sometimes you need the variance of only certain rows — say, sales variance for stores in the West region only. There's no built-in VARIF function. The workaround: array formula with VAR.S and IF. In Excel 365 with dynamic arrays it's clean: =VAR.S(IF(B2:B100="West", A2:A100)). In older Excel, press Ctrl+Shift+Enter after typing the formula to confirm it as an array.

For multi-criteria, nest IFs: =VAR.S(IF((B2:B100="West")*(C2:C100=2024), A2:A100)). Asterisk acts as AND between conditions. This is the same logic countifs excel uses for conditional counting — just applied to variance instead. Wrap the result in ROUND if downstream cells display only two decimals — otherwise you'll see floating-point noise in the final digits.

Dynamic Array Filtering in Excel 365

If you're on Microsoft 365, the FILTER function makes conditional variance even cleaner. Type =VAR.S(FILTER(A2:A100, B2:B100="West")). No array entry needed, no IF wrapper. FILTER spills the matching rows into a virtual range and VAR.S consumes them like any other range. Same result as the IF trick, much less typing, much easier for a coworker to read six months later.

You can chain FILTER conditions with multiplication: =VAR.S(FILTER(A2:A100, (B2:B100="West")*(C2:C100>=2024))). The multiplication acts as AND. For OR conditions, add instead: (B2:B100="West")+(B2:B100="East"). Wrap the whole thing in IFERROR if the filter might return zero rows, otherwise Excel returns #CALC! when nothing matches.

Variance in Pivot Tables

For a clean per-group breakdown without writing a formula per group, drop your value column into a pivot table's Values area, click the small dropdown arrow on it, choose Value Field Settings, and change "Summarize Values By" to Var (sample) or Varp (population). Pivot variance respects all your existing slicers and filters automatically.

The downside: the displayed format defaults to plain number — apply number formatting separately, otherwise you'll stare at "237.5421" when you wanted "237.5". Right-click the value column header, choose Number Format, and set decimals to 2 or 3. This sticks for the whole pivot, even after refresh.

Variance Across Multiple Sheets

If your data lives on several tabs (one tab per month, one tab per branch), you can compute variance across all of them with a 3D reference: =VAR.S(Jan:Dec!A2:A31). That tells Excel "every sheet from Jan to Dec, in the range A2:A31 on each." Works for any function that accepts a range. The tabs must be physically adjacent in the workbook order — Excel reads them in tab-bar sequence. Drag tabs into the right order before writing the 3D formula, or it won't pick up the sheets you expect.

Combining VAR.S with Named Ranges

For dashboards you'll reuse, define a named range for your data column (Formulas → Name Manager → New, then point it at the column). Now your variance formula becomes =VAR.S(SalesData) instead of =VAR.S('Sheet3'!$A$2:$A$10000). Easier to read, easier to audit, less likely to break when someone inserts a new column. Named ranges also auto-expand if you use Excel Tables (Insert → Table), so adding new rows updates the variance automatically — no formula edit needed.

When Excel Returns Weird Variance Numbers

If VAR.S returns 0, every value in the range is identical — no spread, zero variance, which is correct but worth a sanity check. If it returns #VALUE!, you've passed an entirely text argument by accident, or one of your cells contains a formula that errors. If you get #DIV/0!, you've got fewer than two numeric cells in the range — sample variance needs at least two data points. Each of these errors is Excel telling you something honest about your data, not a bug.

Common Variance Mistakes (and How to Avoid Them)

Most variance errors come from picking the wrong function or feeding it dirty data. Here's what trips people up.

Using VAR.P on a Sample

This is the big one. You took 30 product reviews out of thousands and computed VAR.P on the 30. The result understates the real population variance because the divisor is n=30 instead of n-1=29. With small samples that's a noticeable difference; with large samples it's tiny but still technically wrong. Default to VAR.S unless you can prove your data is the whole population.

Mixing Text and Numbers

If your column has both numeric scores and the word "N/A" for missing rows, VAR.S quietly skips the text — which is usually what you want. But if you used VARA, those "N/A" entries get counted as 0 and tank the average, inflating variance. Always check whether your data has hidden text before reaching for VARA or VARPA. Use =COUNT(A2:A100) vs =COUNTA(A2:A100) — if they differ, you have non-numeric values.

Ignoring Outliers

Variance is sensitive to extreme values because deviations get squared. One typo where a salary of $55,000 became $550,000 will balloon the variance for that column. Before computing variance on real-world data, sanity-check the range with =MIN(A2:A100) and =MAX(A2:A100). If the max is 10× the median, investigate before trusting the variance number.

Forgetting to Filter Before Computing

You ran VAR.S on the whole sales table when you only wanted variance for one region. The result includes every region's noise. Either filter the table first, use the IF array formula trick from earlier, or pivot the data. excel pivot tables can compute variance per group natively under Value Field Settings → Summarize Values By → Var or Varp.

Using Variance When You Want Standard Deviation

Variance reads in squared units. If your data is dollars, variance is in "dollars squared" — which isn't a real-world thing. For reports going to non-statisticians, show standard deviation instead. Save variance for statistical tests (ANOVA, F-tests, regression) where the math actually needs the squared form.

Practice Drills Before Your Exam

If you're prepping for a job-application Excel test or a certification, drill these functions until they're automatic. Try the free excel formulas questions and answers for fast review, or work through full excel practice scenarios to see variance questions in context with related statistical functions. Most exams pair variance with AVERAGE, MEDIAN, MODE, and standard deviation — knowing the family together helps more than memorizing one in isolation.

A final note for interviews: be ready to explain VAR.S versus VAR.P out loud, not just type the formula. Hiring managers love asking why the divisor changes. Short answer that wins points: "VAR.S uses n-1 because we're estimating the population mean from the sample, and that estimate costs one degree of freedom. VAR.P uses n because we already have the population mean — nothing's being estimated." Practice saying that until it's natural.