What's the difference between STDEV.S and STDEV.P?

STDEV.S calculates sample standard deviation (dividing by n-1) and is used when your data is a subset of a larger population. STDEV.P calculates population standard deviation (dividing by n) and is used when you have the entire population. For example, hours logged on a few tasks (sample) vs. all hours ever logged by the company (population).

Why does STDEV.S divide by n-1 instead of n?

Dividing by n-1 (Bessel's correction) accounts for the fact that sample data underestimates true population variance. This correction makes STDEV.S an unbiased estimator. For sample data, n-1 always gives a better estimate of the population's true standard deviation.

Can STDEV.S handle negative numbers?

Yes, STDEV.S works perfectly with negative numbers. Standard deviation measures distance from the mean, and direction (positive or negative) doesn't matter—the deviations are squared, so -2 and +2 contribute equally.

How do I calculate STDEV.S for groups (like one person's tasks vs. another)?

Use multiple STDEV.S calls, one per group, or use an array formula with conditional logic. For example, =STDEV.S(IF(B2:B6="Alice",E2:E6)) with Ctrl+Shift+Enter to find variance in hours for Alice's tasks only. Alternatively, filter the data and call STDEV.S on each subset separately.

STDEV.S function

STDEV.S returns the sample standard deviation of numeric values, measuring how spread apart data points are from their mean.

=STDEV.S(number1, ...)

Generate a STDEV.S formula

Describe what you need. The generator will reach for STDEV.S where STDEV.S is the right tool, and tell you when it is not.

Arguments

How STDEV.S reads its arguments

Argument	Required	Description
number1	Required	Required. A numeric value, cell reference, or range. At least 2 numeric values must be supplied across all arguments.
...	Repeating	Optional. Additional numeric values, cell references, or ranges. All numbers are included in the standard deviation calculation; empty cells are ignored, but error values propagate.

Returns

A positive number representing the sample standard deviation, or an error if fewer than 2 values are provided.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Measure variability in hours logged across all tasks

Task	Owner	Start Date	Due Date	Hours Logged
API Documentation	Alice	2024-01-08	2024-01-12	8
Bug Fix: Login	Bob	2024-01-08	2024-01-10	12
UI Redesign	Carol	2024-01-09	2024-01-19	5
Database Migration	Alice	2024-01-09	2024-01-15	16
Testing Suite	Dave	2024-01-10	2024-01-12	6

=STDEV.S(E2:E6)

Result: 4.56

The hours logged (8, 12, 5, 16, 6) have a mean of 9.4. The sample standard deviation of 4.56 shows that task hours typically vary by about 4.6 hours from the average, indicating inconsistent task sizing. A high standard deviation suggests some tasks are much smaller or larger than average.

2. Compare task duration consistency for a single team member

Task	Owner	Hours Logged
API Documentation	Alice	8
Database Migration	Alice	16

=STDEV.S(8,16)

Result: 5.66

Alice's two tasks logged 8 and 16 hours respectively. The sample standard deviation of 5.66 indicates high variability in her task sizes (mean 12 hours). With only two data points, this tells us her workload is uneven—one task is much smaller than the other.

3. Detect outliers by comparing individual hours against team variance

Task	Hours Logged	Mean	Stdev Range
API Documentation	8	9.4	±4.56
Bug Fix: Login	12	9.4	±4.56
UI Redesign	5	9.4	±4.56
Database Migration	16	9.4	±4.56
Testing Suite	6	9.4	±4.56

=STDEV.S(E2:E6)*2

Result: 9.12

Multiplying the standard deviation by 2 (9.12) creates a rough 95% confidence interval around the mean. The Database Migration task (16 hours) falls outside this range, flagging it as unusually large. This helps project managers identify tasks that deviate significantly from typical effort expectations.

Common errors

Which STDEV.S error are you seeing?

Error	Why it happens	How to fix it
#DIV/0!	STDEV.S requires at least 2 numeric values to calculate variance. Providing only 1 value, or a range with only 1 non-empty cell, causes division by zero (n-1 becomes 0).	Ensure your range or argument list includes at least 2 numeric values. For example, change =STDEV.S(E2) to =STDEV.S(E2:E6) to include multiple tasks.
#VALUE!	STDEV.S cannot process text values. If a range contains any text (like a task name or status), the function returns #VALUE!.	Verify the range contains only numbers or empty cells. For example, =STDEV.S(E2:E6) only, not =STDEV.S(A2:E6) which includes the task names.
#N/A	If any cell in the range contains an error value (such as #VLOOKUP error, #DIV/0!, or #REF!), that error propagates through STDEV.S.	Use IFERROR or IFNA to replace error values with valid numbers or exclude them. For example, =STDEV.S(IFERROR(E2:E6,"")) to skip error cells.

Tips and when to use something else

Use STDEV.P instead of STDEV.S if you're analyzing an entire population rather than a sample. STDEV.P divides by n, while STDEV.S divides by n-1 (Bessel's correction), making STDEV.S slightly larger and more conservative for sample data.
STDEV.S ignores empty cells and logical values (TRUE/FALSE), but error values will cause the formula to fail. Use AGGREGATE to skip both errors and hidden rows.
Combine STDEV.S with AVERAGE to communicate variability: 'Average task is 9.4 hours ± 4.6 hours (one standard deviation)' gives context to data spread.
Watch out: STDEV.S with 2 values always returns the exact difference ÷ √2 (approximately 0.707× the difference). This is mathematically correct but can be misleading with tiny datasets.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.STDEV.PSTDEV.P returns the population standard deviation of a dataset, measuring how spread out values are across the entire population.VAR.SVAR.S returns the sample variance of a dataset, measuring how spread values are around the mean using an unbiased n-1 denominator.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.AGGREGATEReturns the result of a function (like SUM, AVERAGE, COUNT) applied to a range, with built-in options to ignore error values, hidden rows, and nested SUBTOTALs.

Frequently asked questions

What's the difference between STDEV.S and STDEV.P?: STDEV.S calculates sample standard deviation (dividing by n-1) and is used when your data is a subset of a larger population. STDEV.P calculates population standard deviation (dividing by n) and is used when you have the entire population. For example, hours logged on a few tasks (sample) vs. all hours ever logged by the company (population).
Why does STDEV.S divide by n-1 instead of n?: Dividing by n-1 (Bessel's correction) accounts for the fact that sample data underestimates true population variance. This correction makes STDEV.S an unbiased estimator. For sample data, n-1 always gives a better estimate of the population's true standard deviation.
Can STDEV.S handle negative numbers?: Yes, STDEV.S works perfectly with negative numbers. Standard deviation measures distance from the mean, and direction (positive or negative) doesn't matter—the deviations are squared, so -2 and +2 contribute equally.
How do I calculate STDEV.S for groups (like one person's tasks vs. another)?: Use multiple STDEV.S calls, one per group, or use an array formula with conditional logic. For example, =STDEV.S(IF(B2:B6="Alice",E2:E6)) with Ctrl+Shift+Enter to find variance in hours for Alice's tasks only. Alternatively, filter the data and call STDEV.S on each subset separately.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17