Standard Deviation in Excel: STDEV.S or STDEV.P?

By Emma Updated 2 November 2026 3 min read

Standard deviation: STDEV.S or STDEV.P?

Standard deviation measures how far the values in a list are, on average, from their mean. In Excel, use =STDEV.S(B2:B7) when your data is a sample (the usual case), or =STDEV.P(B2:B7) when it covers the whole population you are studying.

The result is in the same unit as the data: pounds for sales, days for lead times. The bigger it is, the more spread out the values are.

Example: two shops, same average, different spread

Weekly sales for two shops over six weeks:

ABC
1WeekLondon (£)Leeds (£)
2W11,200900
3W21,2501,500
4W31,1801,100
5W41,2201,400
6W51,1501,000
7W61,2001,300

Both shops have the same average: =AVERAGE(B2:B7) and =AVERAGE(C2:C7) both return £1,200. Standard deviation shows what the average hides:

  • =STDEV.S(B2:B7) returns £34.06 for London;
  • =STDEV.S(C2:C7) returns £236.64 for Leeds.

London sells roughly the same amount every week. Leeds swings between good and bad weeks, so stock and staffing are harder to plan there. To show it, plot both columns on a line chart: London's line stays almost flat, while Leeds goes up and down from week to week.

STDEV.S or STDEV.P: which one to use?

Both functions run the same calculation with one difference: the sum of squared deviations is divided by n − 1 in STDEV.S and by n in STDEV.P (n = number of values).

  • STDEV.S (sample): your data is part of a larger group and you want to draw a general conclusion. Six weeks of sales to judge how steady a shop is, 50 customers surveyed out of 2,000, 20 parts checked from a production run: all samples.
  • STDEV.P (population): your data includes every item you care about and you are not generalising. The marks of all 28 pupils in a class to describe that class, the salaries of every employee in a small firm: that is the whole population.

If in doubt, use STDEV.S. On our example:

ShopSTDEV.SSTDEV.P
London£34.06£31.09
Leeds£236.64£216.02

STDEV.S always gives a slightly larger result. The gap is visible with 6 values; with several hundred, the two results are almost identical.

Excel's other standard deviation functions

You will come across six names in the function list. Here is what each one does:

FunctionDataNotes
STDEV.SSampleThe default choice.
STDEV.PPopulationWhen you have every value.
STDEVSampleOlder name, same result as STDEV.S. Kept for compatibility with old workbooks.
STDEVPPopulationOlder name, same result as STDEV.P.
STDEVASampleAlso counts text (as 0) and logical values (TRUE = 1, FALSE = 0) in the range.
STDEVPAPopulationSame logic as STDEVA, for a population.

STDEV.S and STDEV.P ignore empty cells, text and logical values in a range. STDEVA and STDEVPA are rarely needed: use them only when a cell such as "absent" or FALSE should count as zero.

Reading the result

A standard deviation on its own says little: £236 is a lot on weekly sales of £1,200 and almost nothing on sales of £100,000. To compare, divide it by the mean. This is the coefficient of variation:

=STDEV.S(B2:B7)/AVERAGE(B2:B7)

Formatted as a percentage, it gives 2.8% for London and 19.7% for Leeds. The same ratio lets you compare series with different units or scales.

A standard deviation of 0 means every value is identical. It is never negative.

Standard deviation with a condition

If your data is laid out as a list (City in column A, Amount in column B, rows 2 to 13), there is no "STDEVIF" function. Nest IF inside STDEV.S:

=STDEV.S(IF(A2:A13="London",B2:B13))

IF returns the amount for "London" rows and FALSE for the others, which STDEV.S ignores. In Excel 365 and 2021, just press Enter. In older versions, confirm with Ctrl + Shift + Enter (array formula), otherwise the result is wrong.

In Excel 365 or 2021, FILTER reads more clearly:

=STDEV.S(FILTER(B2:B13,A2:A13="London"))

Errors and unexpected results

#DIV/0!

STDEV.S needs at least two numbers, because it divides by n − 1. A range holding a single number, or only text, returns #DIV/0!. Check how many numbers Excel actually sees with =COUNT(B2:B7).

Result too low or too high

  • Numbers stored as text: imported figures (left-aligned, small green triangle) are skipped without any warning. If =COUNT(B2:B7) returns fewer than the number of rows, convert them (warning button, then "Convert to Number").
  • Zero instead of a blank: a week with no data entered as 0 counts as a £0 week and inflates the standard deviation. Leave the cell blank if the value is unknown.
  • Total row inside the range: if the range includes the Total row, that one far-off value distorts the whole calculation.

For an average where values do not all carry the same weight, see our guide to the weighted average in Excel. Microsoft's documentation covers the STDEV.S function in detail.

Emma · Writer, Excel Zone

Emma writes the tutorials on formulas and data cleaning: COUNTIF, SUMIF, DATEDIF, duplicates, drop-down lists. Each method is shown on a small table you can copy.