The STDEV.S function in Excel estimates standard deviation from a sample of numeric observations.
It describes spread around the mean in the same units as the original data. A larger result indicates more variation within that sample.
Use STDEV.P instead when the values represent the entire population you want to describe.
In this article, I’ll show you how to compare sample and population standard deviation, apply a condition, and calculate rolling standard deviation.
STDEV.S Function Syntax in Excel
The STDEV.S function uses the following syntax:
=STDEV.S(number1,[number2],...)
- number1: Required. The first number, range, or array from your sample.
- number2, …: Optional. Additional numbers, ranges, or arrays from the same sample. STDEV.S accepts up to 254 number arguments in total.
STDEV.S estimates standard deviation using the sample method, which divides by one less than the number of numeric observations.
When to Use STDEV.S Function
- Measure variation when your worksheet contains a sample from a larger group.
- Compare how consistent two sampled sets of measurements are.
- Ignore blanks, text, and logical values stored inside a referenced range.
- Measure variation for rows that meet a condition.
- Track changing variability with a rolling sample window.
Example 1: Calculate Sample Standard Deviation
Here is a simple service desk example.
Below is the dataset with ten service tickets and their resolution times in hours.
I want to measure how much these sampled resolution times vary around their average.
Enter this formula in cell B12:
=STDEV.S(B2:B11)
The formula returns 0.96 hours after rounding to two decimal places.
Excel treats the ten values as a sample and estimates how much resolution times vary around their average.
Example 2: Compare STDEV.S and STDEV.P
The right function depends on whether your data is a sample or the complete group.
Below are daily order counts for eight stores.
I want to compare the sample and population standard deviations from the same values.
Enter the STDEV.S formula in cell B11:
=STDEV.S(B2:B9)
The sample standard deviation is 2.88 orders.
If these eight stores are the entire population you want to analyze, enter this formula in cell B12 instead:
=STDEV.P(B2:B9)
The population standard deviation is 2.69 orders.
STDEV.S uses the n-1 method to estimate variation in a larger population. That adjustment usually makes its result slightly larger than STDEV.P for the same values.
Pro Tip: Use STDEV.S when the values are a sample. Use STDEV.P only when the range contains the complete population you want to describe.
Example 3: Ignore Text, Blanks, and TRUE
Referenced ranges can contain entries that are not numeric.
Below are inspection scores mixed with a text status, a TRUE value, and a blank cell.
I want Excel to calculate the sample standard deviation from the five numeric scores.
Enter this formula in cell B10:
=STDEV.S(B2:B9)
The formula returns 3.83.
Because the data comes through a reference, STDEV.S ignores the text, TRUE value, and blank. It uses only 95, 88, 91, 86, and 94.
Pro Tip: Use STDEVA when referenced logical values or text should count. TRUE counts as 1, while FALSE and text, including numeric text, count as 0.
Example 4: Calculate STDEV.S With a Condition
You can combine STDEV.S with the FILTER function to measure only the rows that meet a condition.
Below are order values for four regions.
I want the sample standard deviation for East region orders only.
Enter this formula in cell D2:
=STDEV.S(FILTER(B2:B11,A2:A11="East"))
FILTER returns the four East values: 6,800, 7,100, 6,500, and 7,600. STDEV.S reduces that filtered array to $469.04.
FILTER is available in Excel 2021, Excel 2024, and Microsoft 365. In older versions, use a helper range or enter an equivalent legacy array formula.
Pro Tip: One numeric match gives #DIV/0! because STDEV.S needs two values. No matches gives FILTER’s #CALC! error unless you handle an empty result.
Example 5: Calculate Rolling Standard Deviation
A rolling window shows how variability changes as new observations arrive.
Below are weekly customer complaint counts, with a result column for each three-week window.
I want the sample standard deviation for each current three-week period.
Enter this formula in cell C4 and fill it down through C11:
=STDEV.S(B2:B4)
The first window returns 3.06 for weeks 1 through 3.
As you fill the formula down, Excel shifts the range by one row. The remaining results are 3.06, 2.65, 4.04, 4.00, 3.06, 2.00, and 3.51.
There is no newer direct replacement for this fixed rolling calculation. The changing relative references keep the formula easy to audit.
Tips & Common Mistakes
- Use STDEV.S for a sample and STDEV.P for an entire population. This choice changes the calculation, not just the displayed format.
- STDEV.S needs at least two numeric values. With fewer values, Excel returns #DIV/0!.
- Blanks, text, and logical values inside a referenced range are ignored. Logical values and text representations of numbers typed directly as arguments are counted.
- Use STDEVA when referenced logical values or text should count. TRUE becomes 1, while FALSE and text, including numeric text, become 0.
- Avoid the older STDEV function in new work. Microsoft keeps it for backward compatibility, while STDEV.S is the current function name.
- STDEV.S returns one value. It can accept a spilled range such as
A2#or reduce an array returned by FILTER.
I covered a basic sample, the STDEV.S versus STDEV.P choice, ignored values, a conditional sample, and a rolling three-week calculation.
I hope you found this article helpful.
Other Excel articles you may also like:
