What variance is and why Excel calculates it for you

Variance measures how spread out your numbers are from their average. If you have a list of test scores, variance tells you whether most students scored close to the class average or whether some did much better and others much worse. Excel does this math automatically — you type a formula, and it returns a single number that describes the spread.

You need variance when you're trying to understand whether your data is consistent or volatile. A factory measuring widget weights wants low variance (all widgets nearly identical). A stock portfolio wants to understand variance to know how much the price bounces around. A teacher looking at test scores uses variance to see whether the class understood the material evenly or whether a few students pulled the average up or down.

Excel offers two variance formulas: one for a complete set of data (the whole population) and one for a sample of data (part of a larger group). Most of the time you'll use the sample version, because you're usually working with a subset rather than every single data point that exists.

Key Takeaways

  • Excel's VAR.S function calculates variance for a sample of data, and VAR.P calculates variance for an entire population — use VAR.S unless you have every single data point.
  • The formula syntax is =VAR.S(first cell:last cell), where you replace the cell references with the actual range holding your numbers.
  • Variance is always a positive number, and a higher number means your data is more spread out from the average.
  • You can calculate variance for any column of numbers in seconds once you know which function to use and where to type it.

Setting up your data in Excel

Open Excel and enter your numbers in a single column. For example, if you're measuring how many hours five employees worked each week, put the numbers in cells A1 through A5. You can also use a row instead of a column — Excel reads both the same way.

Make sure every cell in your range holds only a number. If a cell contains text, a blank space, or a formula error, Excel will either skip it or return an error message. If you have headers (like "Hours Worked" in cell A1), start your data in A2 instead and adjust your formula range accordingly.

You don't need to sort the numbers or arrange them in any particular order. Variance looks at all the numbers together regardless of their sequence.

Using VAR.S for sample data

Click on an empty cell where you want the variance result to appear — usually somewhere below or to the right of your data. Type the formula exactly as shown: =VAR.S(A1:A5), but replace A1:A5 with the actual range of your numbers.

If your data is in cells B2 through B10, you would type =VAR.S(B2:B10). If your data is in a row instead of a column — say C1 through H1 — you would type =VAR.S(C1:H1). The colon between the first and last cell tells Excel to include everything in between.

Press Enter. Excel calculates the variance and displays the result in that cell. The number you see is the variance. It will usually be a decimal, and it's always positive or zero (zero only if every number in your data is identical).

Using VAR.P for complete population data

Use VAR.P only when you have every single data point in the group you're measuring. If you're calculating variance for all 50 employees in your company, use VAR.P. If you're calculating variance for a sample of 10 employees to estimate what the whole company looks like, use VAR.S instead.

The formula works the same way: =VAR.P(A1:A5). Type it in an empty cell, press Enter, and Excel returns the variance. The result will be slightly smaller than VAR.S would give you for the same data, because VAR.P assumes you have the complete picture.

Most people use VAR.S because most datasets are samples, not complete populations. Use VAR.P only if you're certain you have every single value.

Understanding what your variance number means

Variance is measured in the square of your original units. If you're measuring hours, variance is in "hours squared." If you're measuring dollars, variance is in "dollars squared." This makes variance hard to interpret directly, which is why many people calculate standard deviation instead — it's the square root of variance and uses the same units as your original data.

A higher variance number means your data is more spread out. A variance of 2 means your numbers cluster closer to the average than a variance of 20 would. You can compare variance numbers from two different datasets to see which one is more consistent, as long as both datasets measure the same thing in the same units.

If you want to convert variance to standard deviation, use the formula =SQRT(variance cell). For example, if your variance is in cell C1, type =SQRT(C1) in another cell to see the standard deviation.

Common mistakes when calculating variance

The most common error is using the wrong function. VAR.S and VAR.P give different results, and using the wrong one changes your answer. Remember: VAR.S for samples (most cases), VAR.P for complete populations (rare).

Another mistake is including text or blank cells in your range. If you type =VAR.S(A1:A10) but cell A5 contains the word "error" or is empty, Excel will either skip that cell or return an error message. Check that every cell in your range holds a number before you press Enter.

A third mistake is forgetting to use the colon. If you type =VAR.S(A1 A5) instead of =VAR.S(A1:A5), Excel won't recognize the formula. The colon is required to tell Excel you want the range from the first cell to the last cell.

Calculating variance for multiple columns at once

If you have several columns of data and want variance for each one, you can copy the formula across. Type =VAR.S(A1:A5) in cell C1. Click on C1, then drag the small square in the bottom-right corner of the cell to the right across cells D1 and E1. Excel automatically adjusts the formula for each column, so D1 becomes =VAR.S(B1:B5) and E1 becomes =VAR.S(C1:C5).

This saves time when you're working with many columns. Make sure your data is organized so each column is separate and complete — if columns are mixed together or have different numbers of rows, the copied formulas won't work correctly.

Frequently Asked Questions

Can variance be negative?

No. Variance is always zero or positive. Zero means every number in your data is identical. A positive number means your data varies. If Excel returns a negative number, something went wrong with your formula or data.

What's the difference between variance and standard deviation?

Variance is the average of squared differences from the mean. Standard deviation is the square root of variance and is easier to interpret because it uses the same units as your original data. If variance is 16, standard deviation is 4. Most people report standard deviation instead of variance because it's more intuitive.

Should I use VAR.S or VAR.P?

Use VAR.S unless you have every single data point in the group you're measuring. VAR.S is for samples; VAR.P is for complete populations. Most real-world datasets are samples, so VAR.S is the right choice in nearly all cases.

Why does my variance formula return an error?

Check that your range includes only numbers and no text or blank cells. Also check that you used a colon between your first and last cell reference — =VAR.S(A1:A5) is correct, but =VAR.S(A1 A5) will error. If you have fewer than two numbers in your range, VAR.S will also return an error.

Can I calculate variance for data in different sheets?

Yes. Type the sheet name before the cell reference, like =VAR.S(Sheet2.A1:A5). Replace Sheet2 with your actual sheet name and adjust the cell range to match your data. Excel treats data from different sheets the same way as data in the same sheet.