Excel has two variance functions that give you slightly different answers

The variance you calculate depends on whether your data is the entire group you care about or a sample from a larger group. Excel gives you two functions: VAR.P for a population (the complete set) and VAR.S for a sample (a subset). Most of the time you will use VAR.S, because most real data you work with is a sample — your sales this quarter, test scores from one class, measurements from a production run.

VAR.P divides by the total count of numbers. VAR.S divides by the count minus one. That difference matters when your data set is small. If you have 100 data points, the difference is tiny. If you have 5, it is significant. Excel also has older functions called VAR and VARP that do the same thing, but VAR.S and VAR.P are the current standard.

Key Takeaways

  • Use VAR.S when your numbers represent a sample from a larger group, which is true for most real-world data like sales, test scores, or measurements.
  • Use VAR.P only when your numbers are the complete population you care about, such as the heights of all employees in a five-person company.
  • Type the formula as =VAR.S(A1:A10) or =VAR.P(A1:A10), replacing the range with your actual cell addresses.
  • Variance is measured in the square of your original units — if you measured in dollars, variance is in dollars squared, which is why standard deviation (the square root of variance) is often more useful.

How to enter a variance formula in a cell

Click the cell where you want the variance to appear. Type an equals sign, then the function name, then your data range in parentheses. For example, if your numbers are in cells A1 through A10, type =VAR.S(A1:A10) and press Enter. Excel calculates the result and displays it in that cell.

You can select the range by typing it, or by clicking and dragging. Click the cell after the opening parenthesis, then click the first cell of 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 in a column with a header row, exclude the header. For example, if row 1 says "Sales" and your numbers start in A2, use =VAR.S(A2:A100), not =VAR.S(A1:A100).

When to use VAR.S versus VAR.P

Use VAR.S if your data is a sample — a subset chosen from a larger group. This covers almost all real-world cases: survey responses from some customers (not all), monthly sales figures (not a complete history), test scores from one class (not every class that ever existed). VAR.S assumes you are trying to estimate the variance of the whole population based on what you have.

Use VAR.P only if your data is the complete population you care about. Examples are rare: the heights of all five people in a small department, the test scores of all students in a specific class on a specific day, the daily closing prices of a stock for one calendar year if you only care about that year. If you might ever want to compare your data to other years or other groups, you probably have a sample, not a population.

When in doubt, use VAR.S. It is the safer choice and the one most analysts use.

Understanding what the variance number means

Variance tells you how spread out your data is. A small variance means your numbers cluster close together. A large variance means they are scattered far apart. But variance is measured in the square of your original units, which makes it hard to interpret directly.

If you measured sales in dollars, variance is in dollars squared — a number that does not correspond to anything real. That is why people often use standard deviation instead, which is the square root of variance. In Excel, type =STDEV.S(A1:A10) to get standard deviation. It is in the same units as your original data, so it is easier to understand.

For example, if your sales data has a variance of 10,000 and you take the square root, you get a standard deviation of about 100. If your average sale is 500, a standard deviation of 100 tells you that most sales fall between 400 and 600 — a much clearer picture than "variance is 10,000."

Variance formulas with conditions or non-adjacent cells

If your data is not in one continuous range, you can list multiple ranges separated by commas. For example, =VAR.S(A1:A10,C1:C10) calculates variance across both ranges as if they were one list.

If you want variance only for cells that meet a condition — for example, only sales over 1,000 — Excel does not have a built-in conditional variance function. You have two options. The simpler one is to filter your data to show only the rows you want, then calculate variance on the visible cells. The more complex one is to use an array formula with IF, but that requires understanding array syntax and is rarely worth the effort for a one-time calculation.

Common mistakes when calculating variance

The most common error is using VAR.P when you should use VAR.S. This gives you a variance that is too small and makes your data look more consistent than it really is. If someone else is checking your work and you used VAR.P on sample data, they will catch it.

The second mistake is including text or blank cells in your range. Excel ignores text and blank cells automatically, so this usually does not cause an error — but it can cause confusion if you think you included 100 data points and Excel only counted 87 because some cells were empty. Check your count by using =COUNTA(A1:A10) to see how many non-empty cells are in your range.

The third mistake is forgetting to exclude headers. If row 1 contains the word "Sales" and you include it in your variance formula, Excel will ignore it (because it is text), but you might think your formula is wrong when it actually works fine.

Variance in context: comparing two data sets

Variance becomes useful when you compare it across groups. If one sales team has a variance of 5,000 and another has a variance of 20,000, the second team is less consistent — some months are much higher or lower than others. The first team is steadier.

You can calculate variance for each team in separate cells, then compare them side by side. Or you can build a small table: list each team name in column A, their variance in column B, and their standard deviation in column C. This makes it straightforward to see which groups are most and least consistent.

If you are comparing more than two or three groups, consider a chart. A box plot or scatter plot can show variance visually, which is often clearer than a list of numbers.

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. If your data is in dollars, standard deviation is in dollars. Variance is in dollars squared, which is abstract.

Why does Excel have two variance functions?

VAR.S is for sample data (a subset of a larger group) and VAR.P is for population data (the complete group). Sample variance divides by n-1 instead of n to account for the fact that you are estimating a larger population. Most real data is a sample, so VAR.S is the default choice.

Can I calculate variance for text or mixed data?

No. Variance only works with numbers. Excel will ignore text and blank cells, but if your entire range is text, the formula returns an error. If your data is mixed, separate the numbers from the text first, or use a filter to show only numeric rows.

Does variance change if I add or remove one data point?

Yes, significantly if your data set is small. Adding one number that is very different from the others will increase variance. Removing an outlier will decrease it. This is why variance is useful for spotting inconsistency — it is sensitive to extreme values.

Should I use VAR.S or VAR.P for historical data?

Use VAR.S. Even if you have data from the past five years, you are usually treating it as a sample of what future years might look like. Use VAR.P only if you care about the variance of those specific five years and never plan to compare to other time periods.