Excel has two variance formulas that give different answers

Variance measures how spread out your numbers are from the average. Excel gives you two formulas: VAR.S (sample variance) and VAR.P (population variance). The difference matters because they produce different results from the same data.

Use VAR.S when your numbers are a sample from a larger group — like test scores from 30 students in a class of 500. Use VAR.P when your numbers represent the entire group you care about — like test scores from all 30 students in that class. Most of the time, you want VAR.S.

Both formulas work the same way in Excel: you type the formula, point it at your data, and it calculates the result in one cell. The steps are identical; only the formula name changes.

Key Takeaways

  • VAR.S calculates sample variance and is the formula you use most often when your data is a subset of a larger group.
  • VAR.P calculates population variance and is used when your data represents the entire group you are measuring.
  • Both formulas work by typing =VAR.S(range) or =VAR.P(range) in any empty cell, where range is the cells containing your numbers.
  • The result is a single number that appears in the cell where you typed the formula, and you can copy that formula down to calculate variance for different groups of data.

How to calculate sample variance with VAR.S

Sample variance is what you need most of the time. Follow these steps:

  1. Click on an empty cell where you want the variance result to appear.
  2. Type =VAR.S( and do not press Enter yet.
  3. Click on the first cell containing a number, then hold Shift and click on the last cell containing a number. This selects the entire range. You can also type the range directly — for example, =VAR.S(A2:A20) — if you know the cell addresses.
  4. Type a closing parenthesis ) and press Enter.
  5. The variance appears in that cell as a single number. If the number has many decimal places, you can widen the column or format the cell to show fewer decimals.

The formula works with any range of numbers, whether they are in a single column, a single row, or scattered across multiple cells. Excel ignores empty cells and text automatically.

How to calculate population variance with VAR.P

Population variance uses the same steps as sample variance, but with a different formula name:

  1. Click on an empty cell where you want the result.
  2. Type =VAR.P( and do not press Enter.
  3. Select your data range by clicking the first cell, holding Shift, and clicking the last cell. Or type the range directly — for example, =VAR.P(B1:B50).
  4. Type a closing parenthesis ) and press Enter.
  5. The population variance appears in that cell.

VAR.P will always give you a smaller number than VAR.S when you use the same data, because population variance assumes you have measured everything you care about. Sample variance is larger because it accounts for the fact that your sample might not perfectly represent the full population.

Why VAR.S and VAR.P give different numbers

Both formulas measure the same thing — how spread out your data is — but they calculate it differently. VAR.S divides by one less than the number of data points you have. VAR.P divides by the exact number of data points. This difference gets smaller as your data set gets larger, but it always exists.

Think of it this way: if you measure the heights of 10 people in a room, you are working with a sample of all people everywhere. VAR.S assumes there is more variation in the full population than what you see in your 10 people, so it gives a larger variance. If those 10 people are the only ones you care about, VAR.P is correct because you have measured the entire population.

In practice, most data you work with is a sample, so VAR.S is the right choice. You only use VAR.P when you have deliberately measured every single item in the group you are studying.

Copying the variance formula to other cells

Once you have typed a variance formula in one cell, you can copy it to other cells to calculate variance for different groups of data. Click the cell containing your formula, then drag the small square at the bottom right corner of the cell down to the cells below. Excel automatically adjusts the cell references for each row.

For example, if you have sales data for five different regions in columns A through E, you can calculate the variance for each region by putting a VAR.S formula in row 6 for each column, then copying the formula across. Each formula will adjust to measure the correct column.

You can also copy a formula to the right by dragging the corner square to the right instead of down. Excel handles the adjustment either way.

Common mistakes when using variance formulas

The most common mistake is including text or empty cells in your range. Excel ignores both automatically, so this usually is not a problem — but if you have a cell that contains a space or a dash meant to represent missing data, Excel may treat it as text and skip it, which changes your result. Check that your range contains only the numbers you want to measure.

Another mistake is using VAR.S when you meant VAR.P, or vice versa. Remember: VAR.S is for samples, VAR.P is for complete populations. If you are unsure which one you need, VAR.S is almost always the safer choice.

A third mistake is forgetting the parentheses. The formula must be =VAR.S(A1:A10), not =VAR.S A1:A10. Excel will show an error if you leave out the parentheses.

Frequently Asked Questions

What is 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. Standard deviation is easier to interpret because it is in the same units as your original data, while variance is in squared units. Excel has STDEV.S and STDEV.P formulas that work the same way as the variance formulas.

Can I calculate variance for data in non-adjacent cells?

Yes. Type the formula and then click the first range, type a semicolon or comma (depending on your regional settings), then click the second range. For example: =VAR.S(A1:A10,C1:C10). Excel will include both ranges in the calculation.

Why does my variance formula show an error?

The most common cause is a typo in the formula name or missing parentheses. Check that you typed VAR.S or VAR.P exactly, with an opening parenthesis after the name and a closing parenthesis at the end. If your range contains text or special characters, Excel may also return an error — make sure your range contains only numbers.

Does variance change if I add more data to my spreadsheet?

Only if you update the formula range to include the new data. If your original formula was =VAR.S(A1:A10) and you add numbers in A11 and A12, the formula still only measures A1 through A10. You must edit the formula to =VAR.S(A1:A12) to include the new numbers.

Can I use variance to compare two different data sets?

Yes, but be careful. Variance alone does not tell you whether one data set is truly more spread out than another if the data sets have different averages or units. For comparing variation between groups, standard deviation or the coefficient of variation (standard deviation divided by the mean) is often more useful. Calculate variance for each group separately, then compare the results.