What variation means and why you'd measure it
Variation is how spread out your numbers are from their average. If you have a list of test scores, variation tells you whether everyone scored similarly or whether some students did much better or worse than others. Excel gives you two main tools to measure this: variance (the average of squared differences from the mean) and standard deviation (the square root of variance, which is easier to interpret because it's in the same units as your original data).
You'll use these calculations when you want to understand consistency. A factory measuring widget weights might use variation to check whether their machines are producing consistent sizes. A teacher might use it to see whether a class's test scores cluster tightly around the average or spread widely. The smaller the variation, the more consistent your data.
Excel has four functions that calculate variation, and which one you use depends on whether your numbers represent an entire group or just a sample of a larger group. This distinction matters because sample variation uses a slightly different formula than population variation.
Key Takeaways
- Use VAR.S() for sample data (a subset of a larger group) and VAR.P() for population data (the entire group you're measuring).
- Use STDEV.S() for sample standard deviation and STDEV.P() for population standard deviation; standard deviation is easier to interpret than variance because it's in the same units as your original numbers.
- Type the function name, then open parentheses, select or type the range of cells containing your numbers, then close parentheses and press Enter.
- If your data includes text, blank cells, or logical values (TRUE/FALSE), Excel ignores them automatically.
The four variation functions and when to use each
Excel offers VAR.S(), VAR.P(), STDEV.S(), and STDEV.P(). The letters at the end tell you what each does: S means sample, P means population. Variance and standard deviation measure the same thing (spread), but standard deviation is usually more useful because it's expressed in the same units as your data.
Use VAR.S() or STDEV.S() when your numbers are a sample — a subset drawn from a larger group you're interested in. For example, if you measured the height of 30 students from a school of 500, you'd use the sample functions. Use VAR.P() or STDEV.P() when your numbers represent the entire population you care about. If you measured all 500 students, you'd use the population functions.
In practice, most real-world data is a sample. You're rarely measuring every single thing in existence. If you're unsure, sample functions (S) are the safer choice. The difference between sample and population functions is small when you have many numbers, but it grows when your dataset is tiny.
How to enter a variation formula in Excel
Open your spreadsheet and locate the cells containing your numbers. Let's say your data is in cells A2 through A11 (ten numbers). Click on an empty cell where you want the result to appear — maybe C2. Type the function name followed by the range in parentheses.
For standard deviation of a sample, you would type: =STDEV.S(A2:A11) and press Enter. Excel calculates the result and displays it in that cell. For variance of a sample, type =VAR.S(A2:A11). If your data is a population, replace the S with P: =STDEV.P(A2:A11) or =VAR.P(A2:A11).
You can also select the range by clicking and dragging. Type the function name and opening parenthesis, then click on the first cell of your data, hold Shift, and click on the last cell. Excel fills in the range automatically. Then type the closing parenthesis and press Enter.
Understanding the numbers your formula produces
Variance is measured in squared units, which makes it hard to interpret. If you're measuring weight in pounds, variance is in pounds squared — a number that doesn't map back to reality easily. Standard deviation solves this by taking the square root of variance, so it's back in your original units (pounds, inches, dollars, whatever you started with).
A smaller standard deviation means your numbers cluster tightly around the average. A larger standard deviation means they're spread out. If you're comparing two datasets, the one with the smaller standard deviation is more consistent. For example, if one factory's widget weights have a standard deviation of 0.5 ounces and another's has 2 ounces, the first factory is producing more uniform widgets.
The exact number is hard to interpret on its own — you need context. But you can compare standard deviations across different datasets, or use it to identify outliers (numbers that fall far from the average). A common rule is that numbers more than two standard deviations away from the average are unusual.
Handling missing data, text, and errors
Excel's variation functions ignore blank cells, text, and logical values (TRUE/FALSE) automatically. If your range includes a mix of numbers and text, Excel processes only the numbers. This is usually what you want, but it means you should check your data first to make sure you're not accidentally excluding numbers stored as text.
If a cell contains an error value (like #DIV/0! or #N/A), the entire formula returns an error. You'll need to fix or remove the problematic cell before the variation calculation works. If you have a few scattered errors in a large dataset, you can use IFERROR() to replace them with a placeholder value, or you can manually delete the error cells.
Negative numbers are treated like any other number — they don't cause problems. Variation measures spread regardless of whether your numbers are positive, negative, or mixed.
Comparing sample and population functions side by side
| Situation | Function to Use | Example |
|---|---|---|
| You have a sample (subset of a larger group) | STDEV.S() or VAR.S() | 10 test scores from a class of 100 students |
| You have the entire population | STDEV.P() or VAR.P() | Test scores for all 100 students in the class |
| You want the result in original units (easier to interpret) | STDEV.S() or STDEV.P() | Standard deviation of weights in pounds |
| You want the squared result (harder to interpret) | VAR.S() or VAR.P() | Variance of weights in pounds squared |
Common mistakes and how to avoid them
The most common mistake is using the population function (P) when you have a sample, or vice versa. If you're unsure, sample functions are the safer default. Another mistake is including headers or labels in your range. If row 1 contains the word "Scores" and you select A1:A11, Excel will ignore the text and calculate only the numbers in A2:A11, but it's cleaner to select only the cells with numbers from the start.
Some people confuse variance and standard deviation and use the wrong one. Remember: standard deviation is almost always more useful because it's in the same units as your data. Use variance only if you have a specific reason to work with squared units.
If your formula returns a very small number (like 0.00001) or seems wrong, check that your data actually contains numbers and not text that looks like numbers. Text numbers won't be included in the calculation. You can verify by clicking a cell and checking the formula bar to see whether it's left-aligned (text) or right-aligned (number).
Frequently Asked Questions
What's the difference between STDEV and STDEV.S?
STDEV is an older function that does the same thing as STDEV.S (sample standard deviation). Excel still supports it for backward compatibility, but STDEV.S is the current standard. Use STDEV.S in new spreadsheets. The same applies to VAR and VAR.S.
Can I calculate variation for data in multiple non-adjacent columns?
Not in a single formula. You can either combine the ranges using semicolons (=STDEV.S(A2:A11;C2:C11)) if your Excel uses semicolons as separators, or commas if it uses commas. Alternatively, copy all your data into adjacent columns and select the whole range. Check your regional settings to see which separator your version uses.
Why does my variation formula give a different result than my colleague's?
They might be using a population function while you're using a sample function, or vice versa. Check which function each of you typed. If you're both using the same function and getting different results, one of you might have included or excluded different cells, or one dataset might contain text or errors the other doesn't.
How do I know if a number is an outlier using standard deviation?
Calculate the average (mean) and standard deviation of your data. Numbers more than two standard deviations away from the average are often considered unusual. For example, if your average is 100 and standard deviation is 10, numbers below 80 or above 120 are potential outliers. This is a rough guideline, not a hard rule.
Should I use variance or standard deviation?
Use standard deviation in almost all cases. It's in the same units as your original data, making it easier to understand and explain. Variance is useful mainly in advanced statistics or when you're doing calculations that require squared values, but for most everyday analysis, standard deviation is the better choice.