Excel Has Two Variance Functions—Use VAR.S for Most Data

To calculate variance in Excel, type =VAR.S() into an empty cell, put your data range inside the parentheses, and press Enter. For example, if your numbers are in cells A1 through A10, you would type =VAR.S(A1:A10). Excel will return a single number—that is your variance.

Variance measures how spread out your data is. A small variance means your numbers cluster close together; a large variance means they are scattered far apart. VAR.S calculates sample variance, which is what you use when your data is a sample from a larger population—the most common situation in real work.

If you are working with an entire population (not a sample), use =VAR.P() instead. The difference between the two functions is small when you have many data points, but it matters with smaller datasets. Most of the time, VAR.S is the right choice.

Key Takeaways

  • Type =VAR.S(A1:A10) into any empty cell to calculate sample variance; replace A1:A10 with your actual data range.
  • VAR.S is for sample data; VAR.P is for complete populations, though VAR.S is the standard choice in most situations.
  • You can select non-adjacent cells by holding Ctrl and clicking each range, then typing them separated by commas inside the parentheses.
  • Variance is always a positive number or zero; if Excel shows a negative result, check that your data contains only numbers, not text or blank cells.

Selecting Your Data Range Correctly

Click on the first cell of your data, then drag to the last cell you want to include. As you drag, Excel highlights the range in blue. When you release the mouse, the range appears in your formula. If your data is scattered in different columns or rows, hold down Ctrl on your keyboard and click each separate range one at a time—Excel will add them all to your formula, separated by commas.

Make sure your range includes only numbers. If a cell contains text, a space, or is completely blank, Excel will skip it automatically. However, if you have a header row with labels like "Sales" or "Temperature," include that row in your selection—Excel recognizes headers and ignores them.

Where to Put Your Formula and What the Result Means

Click any empty cell—it does not have to be near your data. Type your formula, press Enter, and the variance appears when ready. You can put the formula in the same spreadsheet as your data or in a different sheet entirely.

The number Excel returns is in squared units. If your data is measured in dollars, variance is in dollars squared. If your data is in pounds, variance is in pounds squared. This is why variance is harder to interpret than standard deviation (which is the square root of variance). Many people calculate variance first, then take its square root to get standard deviation, which is easier to understand.

Common Mistakes That Cause Errors

The most frequent error is including text in your range. If you see #VALUE! in your result cell, scan your data for any cells that contain words, dates formatted as text, or currency symbols. Delete or move those cells outside your range, then recalculate.

Another mistake is using the wrong function. VAR.S and VAR.P give different answers because they use different formulas—VAR.S divides by (n-1) while VAR.P divides by n, where n is the number of data points. If your variance seems too high or too low compared to what you expected, double-check that you chose the right function for your situation.

Blank cells are not a problem—Excel skips them. However, cells that look blank but contain a space character will cause an error. Click any suspicious cell and look at the formula bar at the top; if you see anything there, delete it.

Calculating Variance for Multiple Groups at Once

If you have data in columns and want to calculate variance for each column separately, click the first empty cell to the right of your data. Type =VAR.S(A:A) to calculate variance for the entire column A. Copy this formula across to the next column by clicking the cell, copying it (Ctrl+C), then selecting the cells to the right and pasting (Ctrl+V). Excel automatically adjusts the column letter for each new formula.

You can also use a range like =VAR.S(A1:A100) if you want to exclude headers or empty rows at the bottom. The formula works the same way—just be specific about which cells to include.

When to Use Variance Instead of Other Measures

Variance is useful when you need to compare how much two datasets spread out, or when you are doing statistical tests that require variance as an input. If you just want a quick sense of whether your data is clustered or scattered, standard deviation (the square root of variance) is usually easier to understand because it is in the same units as your original data.

Variance also appears in formulas for correlation, regression, and probability calculations. If you are building a statistical model or comparing the consistency of different processes, variance is often the right metric to calculate first.

Frequently Asked Questions

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

VAR.S is for sample data—a subset of a larger population. VAR.P is for complete populations. In practice, use VAR.S unless you are certain your data represents every single item in the group you are studying. VAR.S gives a slightly higher number because it accounts for the fact that a sample may not perfectly represent the whole population.

Why does my variance calculation show an error?

The most common cause is text or non-numeric data in your range. Check each cell for words, dates stored as text, or currency symbols. Also verify that you typed the formula correctly—it should be =VAR.S(range), not =VARS(range) or =Var.s(range). Excel is not case-sensitive, but it is strict about parentheses and spelling.

Can I calculate variance for data in different sheets?

Yes. Type the sheet name followed by an exclamation point before the cell range. For example, =VAR.S(Sheet2!A1:A10) calculates variance for cells A1 through A10 on Sheet2. You can mix ranges from multiple sheets in one formula by separating them with commas.

How do I convert variance to standard deviation?

Take the square root of the variance. In Excel, type =SQRT(VAR.S(A1:A10)) or calculate variance first, then use =SQRT(B1) if your variance is in cell B1. Standard deviation is easier to interpret because it is in the same units as your original data.

What if I have negative numbers in my data?

Variance works with negative numbers just fine. The formula treats them like any other number. Your result will still be positive because variance squares the differences from the average, and squaring any number—positive or negative—gives a positive result.