Standard deviation measures how spread out numbers are around their average. A low value means the numbers sit close together. A high value means they swing.
Functions
How to Calculate Standard Deviation in Excel
Ready to practice?
Apply what you just learned with interactive exercises.
Excel has two everyday functions:
- STDEV.S — sample standard deviation. Use this on a subset (this week's orders, this class of students). This is the one you want most of the time.
- STDEV.P — population standard deviation. Use this only when the range is the entire group you care about.
Syntax
=STDEV.S(number1, [number2], ...)
=STDEV.P(number1, [number2], ...)
Pass a range (B2:B20) rather than listing cells. Text and blank cells are ignored. Logical values typed into the range are ignored; logical values you pass as arguments are counted.
Worked example
Five test scores in B2:B6 — 10, 12, 14, 16, 18.
=AVERAGE(B2:B6) → 14
=STDEV.S(B2:B6) → 3.16
=STDEV.P(B2:B6) → 2.83
STDEV.S divides by n−1 (4), STDEV.P by n (5). The sample number is always a bit larger. If you are describing "these five scores only," use STDEV.P. If these five are a sample of a larger class, use STDEV.S.
When the number looks wrong
- You used STDEV.P on a sample. Switch to STDEV.S.
- A text "N/A" sat in the range. STDEV ignores it, so the divisor is smaller than you think. Filter those rows out first.
- You included the header. Text is ignored, so this one is usually harmless.
- #DIV/0! — fewer than two numeric cells for STDEV.S.
STDEV vs AVERAGE vs MAX
AVERAGE tells you the centre. STDEV tells you the scatter. Two classes can both average 70. One with STDEV.S of 3 is consistent; one with STDEV.S of 18 has a mix of very high and very low scores. Report both.
Frequently Asked Questions
How do I calculate standard deviation in Excel?
Select the cell for the result and type =STDEV.S(B2:B20) where B2:B20 is your data. Press Enter. Use STDEV.P instead only if that range is the entire population.
What is the difference between STDEV.S and STDEV.P?
STDEV.S is the sample formula (divide by n−1). STDEV.P is the population formula (divide by n). For a sample of a larger set, STDEV.S is correct.
Does Excel have a standard deviation function for a sample?
Yes. STDEV.S. The older STDEV name does the same thing.
Can I calculate standard deviation for a filtered list?
STDEV.S includes hidden rows. Use =SUBTOTAL(7, B2:B20) if you want the sample standard deviation of visible rows only (function number 7 is STDEV.S).
Keep going
Guides people read next.
How to Calculate Average in Excel: AVERAGE, AVERAGEIF
Calculate an average in Excel with AVERAGE, then use AVERAGEIF, AVERAGEIFS and a weighted average, and see how blanks, zeros and text change the result.
14 min
SQL Data Types: Categories, Examples and How to Choose
SQL data types explained: numeric, text, date and time, Boolean and binary types, with examples, MySQL, SQL Server, Oracle and PostgreSQL differences, and how to choose.
15 min
Excel Tutorial for Beginners: Learn the Basics Step by Step
A plain-English Excel tutorial for beginners: the grid, data entry, first formulas, tables, charts and a learning path, with practice you can start today.
14 min