What Variance Measures and Why You Calculate It

Variance tells you how spread out your numbers are from their average. If you have a list of test scores, sales figures, or daily temperatures, variance shows whether they cluster tightly around the middle or scatter widely. A small variance means your data points are close to each other. A large variance means they are far apart.

You calculate variance in Excel because it answers a real question: Is this data consistent or unpredictable? A factory measuring widget weights wants low variance — all widgets should weigh nearly the same. A stock trader looking at price swings wants to understand variance — high variance means the price moves around a lot. Excel does the math automatically once you know which function to use and what data to feed it.

Excel offers two variance functions that look almost identical but calculate slightly different things. The difference matters only if you are working with a sample of data rather than an entire population, and most people work with samples. Understanding which one to use prevents you from getting a number that looks right but answers the wrong question.

Key Takeaways

  • Use VAR.S() for a sample of data (the most common case) and VAR.P() for an entire population.
  • Type the function name, then list your data range in parentheses: =VAR.S(A1:A10) calculates variance for cells A1 through A10.
  • Variance is measured in the square of your original units — if your data is in pounds, variance is in pounds squared, which is why standard deviation is often more useful.
  • Excel ignores empty cells and text automatically, so you can select a range that includes labels or blank rows without breaking the calculation.

The Difference Between VAR.S and VAR.P

VAR.S() calculates variance for a sample — a subset of a larger group. If you measured the height of 30 people from a city of 500,000, those 30 are your sample. VAR.S() divides by one less than the number of data points, which makes the result slightly larger. This adjustment exists because a sample tends to underestimate how spread out the full population really is, so the formula corrects for that.

VAR.P() calculates variance for a population — the entire group you care about. If you measured the height of all 30 employees in your office and you only care about those 30 people, not about all office workers everywhere, that is your population. VAR.P() divides by the actual number of data points with no adjustment.

In practice, you almost always use VAR.S(). Most data you work with is a sample from a larger group. Even if you think you have the whole population, treating it as a sample is the safer choice — it gives you a slightly more conservative estimate. The difference between the two functions shrinks as your data set gets larger, so with 100 or more data points, the results are nearly identical.

How to Enter the Variance Formula

Open your spreadsheet and click the cell where you want the variance result to appear. Type an equals sign to start a formula, then type VAR.S( and select the range of cells containing your data. For example, if your numbers are in cells A1 through A20, type =VAR.S(A1:A20) and press Enter. Excel calculates the variance and displays the result in that cell.

You can select the range by typing it directly, or by clicking and dragging. After you type VAR.S(, click the first cell in your data, hold down the mouse button, and drag to the last cell. Excel fills in the range for you. Then type the closing parenthesis and press Enter.

If your data is spread across non-adjacent cells — for example, column A and column C, but not column B — type the ranges separated by a semicolon (on Mac) or comma (on Windows). For example: =VAR.S(A1:A10,C1:C10). Excel treats this as one continuous list of numbers and calculates variance across all of them.

What to Do When Your Data Includes Text or Blank Cells

Excel automatically ignores text and empty cells when calculating variance. If you select a range that includes a header row like "Sales" or "Temperature", Excel skips it. If some cells in your range are blank, Excel skips those too. This means you can select a large range without worrying about cleaning it up first.

However, if a cell contains text that looks like a number — for example, the text "100" instead of the number 100 — Excel treats it as text and ignores it. If you suspect this is happening, click the cell and look at the formula bar at the top. If the value is left-aligned in the cell, it is text. If it is right-aligned, it is a number. You may need to convert text to numbers before calculating variance. The simplest way is to copy the column, right-click, choose Paste Special, select Values, and click OK — this often converts text numbers to real numbers.

Understanding What the Variance Number Means

Variance is measured in the square of your original units. If your data is in pounds, variance is in pounds squared. If your data is in dollars, variance is in dollars squared. This makes variance hard to interpret directly because nobody thinks in squared units.

This is why standard deviation exists. Standard deviation is the square root of variance, and it is measured in the same units as your original data. In Excel, use STDEV.S() for a sample or STDEV.P() for a population — the same logic as variance. Standard deviation is almost always more useful for understanding how spread out your data is, because the number means something you can picture.

If you need to compare variance across different data sets or report it formally, variance is the right number. If you need to understand whether your data is consistent or unpredictable, calculate standard deviation instead. Many people calculate both: variance for statistical tests and standard deviation for explanation.

Common Mistakes When Calculating Variance

The most common mistake is using VAR.P() when you should use VAR.S(). This gives you a result that is slightly too small and understates how spread out your data really is. If you are unsure whether your data is a sample or a population, use VAR.S() — it is the safer choice and the one most statistical work assumes.

Another mistake is selecting the wrong range. If you copy a formula from one cell to another, the range may shift. For example, if you type =VAR.S(A1:A10) in cell C1 and then copy it to cell C2, the formula becomes =VAR.S(A2:A11) — the range moved down one row. To prevent this, use an absolute reference by adding dollar signs: =VAR.S($A$1:$A$10). Now when you copy the formula, the range stays the same.

A third mistake is including the header row in your calculation if the header is text. Excel ignores it automatically, so this usually does not cause an error, but it is cleaner to select only the data cells. If your data starts in A2 (with a header in A1), select A2:A20 instead of A1:A20.

When to Use Variance Instead of Other Measures

Variance is useful when you need a single number that captures how spread out your data is, especially for statistical tests and comparisons. If you are comparing two data sets and want to know which one is more consistent, variance tells you — the smaller variance is more consistent.

However, variance is less useful for everyday explanation because the squared units are hard to interpret. For describing data to someone else, use standard deviation. For statistical modeling, hypothesis testing, or formal reporting, variance is often what the method requires. Check what your analysis or report calls for — if it asks for variance, calculate variance; if it asks for standard deviation, calculate that instead.

If you only want to know the range of your data (highest minus lowest), you do not need variance — just use MAX() and MIN(). If you want to know how many data points fall within a certain distance of the average, you need standard deviation, not variance. Variance is one tool among several for understanding data spread.

Frequently Asked Questions

Can I calculate variance for just part of my data?

Yes. Select only the cells you want to include in the calculation. For example, if your data is in A1:A100 but you only want variance for A1:A50, type =VAR.S(A1:A50). You can also select non-adjacent ranges by holding Ctrl (Windows) or Command (Mac) and clicking multiple ranges, then typing them separated by commas or semicolons.

What does a variance of zero mean?

Variance of zero means all your data points are identical — there is no spread at all. Every number in your range is the same. This is rare in real data but common in test cases or when you are checking whether a formula works correctly.

Should I use VAR or VAR.S?

VAR() is the older function name and works the same way as VAR.S(). Excel still supports it for backward compatibility, but VAR.S() is the current standard. Use VAR.S() for new work. If you see VAR() in an old spreadsheet, it is calculating sample variance the same way VAR.S() does.

Why is my variance so large?

Variance is measured in squared units, so it looks larger than you might expect. If your data ranges from 10 to 20, the variance might be 25 or 30 — larger than the range itself because it is in squared units. Calculate standard deviation instead to see the spread in the original units, which will be smaller and easier to understand.

Can I calculate variance for data in different columns?

Yes. Type =VAR.S(A1:A10,C1:C10) to include data from column A and column C. Excel treats all the numbers as one list and calculates variance across them. You can include as many separate ranges as you need, separated by commas (Windows) or semicolons (Mac).