You have the delivery times of forty orders and your boss asks whether the service is steady or erratic. The average says one thing, but it does not tell you how many orders strayed from normal.
How to use Standard Deviation Formula step by step
1. Choose the measure
Sample or population deviation, the variance of each, coefficient of variation, a value’s Z-score or an outlier flag.
2. Give the range
Type the data range and, if needed, the value cell and how many deviations you tolerate before flagging it as odd.
3. Copy the formula
Paste it into your sheet. References to the range come out pinned so you can drag the formula down a column.
The Step Count: Traditional Friction vs. KorexTools
Working out dispersion by hand (5 steps)
- Find the average of the data.
- Subtract the average from each value.
- Square each difference.
- Average those squares and take the root.
- Repeat whenever a figure changes.
With the KorexTools “Standard Deviation Formula” builder (3 steps)
- You choose the measure.
- You give the data range.
- You copy the formula and reuse it when new data arrives.
The key question is not how to calculate it, but whether your data is a sample or the whole universe
Standard deviation sums up how spread out the data is around the average. Excel offers two versions: STDEV.S for a sample, which divides by one less than the number of data points, and STDEV.P when you have the whole population. Choosing wrongly gives a slightly different result, and with little data the gap shows.
Variance is the deviation squared, and dividing the deviation by the average gives the coefficient of variation, useful for comparing spreads of different magnitudes. The Z-score says how many deviations a value sits from the average, and it is how outliers get flagged: the usual habit is to call odd anything beyond two or three deviations.
Which measure to use
- •Your data is part of a larger set: sample, STDEV.S.
- •You hold every case that exists: population, STDEV.P.
- •You want to compare two series of different scales: coefficient of variation.
The values 2, 4, 4, 4, 5, 5, 7 and 9 in B2:B9
| Measure | Formula | Approximate result |
|---|---|---|
| Sample deviation | =STDEV.S(B2:B9) | 2.14 |
| Population deviation | =STDEV.P(B2:B9) | 2 |
| Z-score of the 9 | =(B9-AVERAGE(B2:B9))/STDEV.S(B2:B9) | 1.87 |
Common Mistakes and How to Avoid Them
- ⚠️Using STDEV.P on a small sample and understating the spread.
- ⚠️Including in the range cells with zeros that are really missing data.
Frequently Asked Questions
Do I use STDEV.S or STDEV.P?
STDEV.S if your data is a sample of a larger set, which is the most common; STDEV.P only if you hold every case, like the marks of a whole class and nothing else.
What is an outlier?
A value that strays from the average more than you tolerate, usually two or three deviations. The tool flags it with the text you choose and you can change the threshold.
What does the coefficient of variation measure?
Relative spread: deviation over average. It lets you compare the variability of series with different units or scales, such as sales in thousands against units.
What I would do if I were in your shoes
«Before trusting the average, look at the deviation: two series with the same average can be different worlds.» — DataKorex Engineering Team.