=STDEV.S(range) for a sample (the most common case) and =STDEV.P(range) for a full population. The .S version divides by n-1, the .P version divides by n. Both functions ignore empty cells, text, and logical values automatically, so you almost never need to clean data first.Standard deviation measures how spread out numbers are around their average. A small number means your data clusters tightly around the mean. A large number means values are scattered all over the place. In Excel, you compute it with one short formula instead of doing the seven-step math by hand.
Most spreadsheets use one of two functions. STDEV.S calculates standard deviation from a sample (a subset of a larger group). STDEV.P calculates it from an entire population (every observation, no exceptions). The difference is one tiny denominator, but it changes your result, your statistical claims, and sometimes your grade or your client's bonus calculation.
You'll see legacy names too: STDEV and STDEVP. Those still work for backward compatibility with old Excel files. Microsoft introduced the dotted versions in Excel 2010 to make the sample/population distinction obvious. New workbooks should use the modern names exclusively.
Think of standard deviation as the typical distance from the average. If you measure 100 widgets and the mean weight is 50 grams with a standard deviation of 2 grams, then most widgets weigh between 48 and 52 grams. That single number gives readers a feel for the data without dumping the whole spreadsheet on them.
This is the question every student and analyst gets wrong at least once. The honest rule is simple. If you have data from a survey, a quality-control batch, or any measurement that represents a portion of a bigger group, use STDEV.S. If your dataset literally contains every member of the group, use STDEV.P.
Most real-world business and academic work involves samples. You survey 200 customers out of 50,000. You test 30 widgets from a production run of 10,000. You pull last quarter's transactions as a representative slice of the full year. All of those scenarios call for STDEV.S. The n-1 denominator (called Bessel's correction) accounts for the uncertainty introduced by sampling.
Population standard deviation comes up in narrower cases. A teacher computing the spread of test scores for one class she teaches is working with a population. A finance analyst pulling daily closing prices for one specific year is working with a population. When the data is the universe of interest, STDEV.P is correct.
The mathematical reason matters here. Samples miss some of the variability that exists in the full population. Dividing by n-1 instead of n nudges your estimate upward to compensate. Without that correction, sample standard deviations are biased low. For tiny samples (under 30), the gap between the two formulas is noticeable. For large samples it shrinks toward nothing.
Syntax: =STDEV.S(number1, [number2], ...)
Type the formula in any empty cell, drag-select the range that holds your numbers, close the parenthesis, and press Enter. Example: =STDEV.S(A2:A201) for 200 customer satisfaction scores.
Use this 90% of the time. It's the default for survey data, experimental results, market research, and any quality-control measurement where you're inferring something about a bigger group.
Syntax: =STDEV.P(number1, [number2], ...)
Identical input format to the sample version. Example: =STDEV.P(B2:B32) for one teacher's class of 31 final exam scores.
Only use this when your data covers the entire group you care about. Confirm with yourself: am I describing this exact set, or making predictions about a larger one? If it's the larger one, switch back to STDEV.S.
Syntax: =STDEVA(value1, [value2], ...) or =STDEVPA(...)
These versions count text as zero and logical values (TRUE/FALSE) as 1 and 0. Useful when your column contains a mix of numbers and labels that you want treated as zeros.
Most analysts avoid these and clean the data first instead. They exist mainly for compatibility with imported datasets where blanks and text appear inside numeric columns.
Open your workbook and click the cell where the result should appear. Many people put it directly under the data column or in a small summary block to the right. Either works. Just keep the result visible so you spot odd values before they propagate into downstream formulas.
Type the equals sign, then the function name. Excel's IntelliSense pops up the moment you type =ST and suggests every standard-deviation variant. Press Tab to accept the highlighted one. Drag-select the data range, or type it manually if you prefer keyboard navigation.
Close the parenthesis and press Enter. The number that appears is your standard deviation in the same units as your raw data. If your column held dollar amounts, the result is in dollars. If it held weights in kilograms, it's in kilograms. This is one of the friendliest features of standard deviation: units stay intact, unlike variance, which squares them.
A common mistake is locking the range incorrectly when you copy the formula across rows. Use $ signs to anchor the cells you want fixed. Example: =STDEV.S($A$2:$A$201) stays pinned to the same range no matter where you drag it. Without dollar signs, the range shifts as you copy, which can silently break your analysis.
Sample standard deviation, divides by n-1. The modern, recommended choice for almost all analytics work.
Population standard deviation, divides by n. Use only when your data is the complete population of interest.
Older sample-version function kept for compatibility. Behaves identically to STDEV.S in modern Excel.
Older population-version function. Same math as STDEV.P, kept for compatibility with older spreadsheets.
Imagine you ran a customer satisfaction survey and collected ten responses on a 1-to-10 scale: 7, 8, 9, 6, 7, 8, 9, 7, 8, 9. Drop those values in cells A2 through A11. In cell C2, type =STDEV.S(A2:A11) and press Enter. Excel returns roughly 1.034.
That single number tells you most respondents sit within about one point of the average. The mean is 7.8 and the standard deviation is just over 1, so the typical answer falls between 6.8 and 8.8. That's tight clustering, which means your customers feel pretty consistent about their experience.
Switch the formula to =STDEV.P(A2:A11) in cell C3 and you'll get about 0.98. The population version dropped because dividing by n (10) instead of n-1 (9) makes the result smaller. The two answers are close because the sample is small. Larger samples make the gap shrink toward zero.
Now try a deliberately noisy dataset. Put these values in A2:A11: 2, 5, 7, 1, 9, 3, 8, 4, 10, 6. Run =STDEV.S(A2:A11) and you'll see roughly 3.03. Same number of cells, completely different spread. The dataset has a mean near 5.5 but values fan out across the entire scale, which is exactly what a higher standard deviation tells you.
Excel throws the #DIV/0! error when you give STDEV.S a single data point or an empty range. The function needs at least two numeric values to divide by n-1 without hitting zero. STDEV.P at least needs one number. If you see that error, your selected range is empty or includes only text and blanks.
The fix is to widen the range or check your data for missing entries. Press Ctrl+End to jump to the last used cell in the sheet. If that lands above your supposed data range, your formula is pointing at empty space. Re-select the right block and the error vanishes.
Another related glitch: if you accidentally include a header row of text inside the range, the function silently excludes it but your count looks off. Always verify by running =COUNT(range) next to your standard deviation. The count should equal the number of numeric values you actually have.
Wrap the formula in IFERROR if you want a clean result on dashboards. Example: =IFERROR(STDEV.S(A2:A101), "Not enough data"). That hides the ugly error code and displays a sensible fallback when the range is sparse. It's the kind of defensive trick that separates polished spreadsheets from messy ones.
If you only need the number once, you don't have to write a formula at all. Select the data range and look at the Excel status bar at the bottom of the screen. Right-click the status bar and turn on the Standard Deviation option. From now on, selecting any range instantly shows its standard deviation in the corner, alongside count, average, and sum.
For a permanent calculation that updates with your data, use the AutoSum dropdown on the Home tab. Click the small arrow next to AutoSum, choose More Functions, and pick STDEV.S from the statistical category. Excel inserts a properly written formula and pre-fills a likely range, which you can adjust.
Keyboard fanatics can press Alt+M, then F, to open the Insert Function dialog directly. Type stdev in the search box and double-click the version you want. This route is the fastest if you're already navigating with your hands on the keys.
Standard deviation rarely lives alone. Most useful analyses combine it with the average to describe a distribution in one line. A classic pattern: =AVERAGE(A2:A101)&" ± "&ROUND(STDEV.S(A2:A101),2). That gives you a clean text output like 74.3 ± 5.21, perfect for dashboards and reports.
For confidence intervals, pair STDEV.S with CONFIDENCE.T (for small samples) or CONFIDENCE.NORM (for large ones). The standard deviation feeds directly into either function. Add and subtract the result from your mean to get a 95% interval. That's how most academic papers report findings.
If your data has outliers — values that sit far from the rest — consider STDEV.S alongside MEDIAN instead of AVERAGE. Standard deviation gets pulled hard by extreme values, and pairing it with the median rather than the mean signals to readers that you understand the data isn't perfectly symmetric.
Conditional standard deviations are possible too, although Excel makes you work for them. There's no STDEVIF function. Instead, combine an array formula with STDEV.S and IF: =STDEV.S(IF(B2:B101="East",A2:A101)) entered with Ctrl+Shift+Enter. In Microsoft 365 the array part happens automatically with dynamic arrays.
Classic descriptive statistics pair. Use to report mean ± standard deviation in a single readable cell.
Build confidence intervals for small samples. Standard deviation drives the margin-of-error calculation.
Better pairing when data has outliers. Median is robust to extremes while STDEV still describes spread.
Compute standardized scores to flag outliers automatically. Z above 2 or below -2 marks unusual values.
Variance is standard deviation squared. If you know one, you know the other. Excel's VAR.S and VAR.P mirror the standard deviation functions exactly. Variance is harder to interpret because its units don't match the data, which is why standard deviation gets used in reports while variance stays under the hood.
The coefficient of variation divides standard deviation by the mean, giving you a unitless ratio. It's useful for comparing spread across datasets with different scales. Formula: =STDEV.S(A2:A101)/AVERAGE(A2:A101). A coefficient under 0.15 means your data is fairly stable. Over 0.30 means it's quite variable.
The Z-score uses standard deviation to tell you how far a single value sits from the mean, in standard-deviation units. Formula: =(value-AVERAGE(range))/STDEV.S(range). Z-scores above 2 or below -2 mark values that fall outside roughly 95% of the data, useful for flagging outliers automatically.
The empirical rule (sometimes called the 68-95-99.7 rule) ties standard deviation to the normal distribution. About 68% of values land within one standard deviation of the mean, 95% within two, and 99.7% within three. Excel doesn't enforce this — your data must actually be approximately normal — but it's a useful sanity check on any reasonably bell-shaped dataset.
Scan the column for stray text, blanks, and outliers. Convert text-formatted numbers to real numbers before running any statistical function.
Decide whether you have a sample or a population. Choose STDEV.S for samples (almost always) or STDEV.P only when the dataset is the complete population of interest.
Type =STDEV.S(range) in any empty cell, drag-select your data, press Enter. Lock the range with $ signs if you plan to copy the formula across other cells.
Compare the output to the visual spread of your data. Cross-check with =COUNT(range) and =AVERAGE(range) to confirm the values used. A standard deviation of zero means every value was identical.
Investors use standard deviation as a proxy for risk. A stock with high return variability (high standard deviation) is considered riskier than one whose returns hover near the mean. Pull a column of monthly returns and run =STDEV.S for an instant risk score. Pair with AVERAGE to compute the Sharpe ratio, the workhorse metric of modern portfolio theory.
Quality engineers track standard deviation on the factory floor. If a manufacturing process produces parts whose dimensions have a standard deviation creeping upward over time, something is going wrong. Control charts plot mean and standard deviation week by week to catch problems before defective products ship.
Teachers and researchers report standard deviation alongside the mean to give context to scores. Saying "the class averaged 78" leaves out crucial information. Saying "the class averaged 78 with a standard deviation of 4" tells you the scores were tight; "averaged 78 with a standard deviation of 15" tells you the class was wildly mixed.
Sports analytics teams love standard deviation for measuring consistency. A basketball player who scores between 18 and 22 points every game has a tiny standard deviation. A player who alternates between 5 and 35 has a huge one. Coaches use that number to decide who can be trusted in close games and who is too volatile.
Marketing teams use it to spot which channels behave predictably. If email campaigns produce click-through rates with a low standard deviation, you can plan around them. If social posts swing wildly week to week, you need bigger sample sizes before drawing conclusions. The number guides budgeting decisions long before anyone runs a formal test.
The single biggest mistake is silent: choosing STDEV.P when you should have used STDEV.S. The formula runs without complaint, the number looks reasonable, and downstream calculations propagate the error all the way to the final report. Always ask yourself the sample vs population question explicitly, write the answer in a comment, then pick the matching function.
The second is anchoring problems when you copy the formula. Drag a standard deviation formula across a row of summary cells and Excel happily shifts every range one column over. If you forgot to add $ signs, your second cell now reads =STDEV.S(B2:B201) instead of the original A2:A201. The result still appears, but it's measuring the wrong column entirely.
The third is mixing units. If column A contains some values in seconds and others in minutes, the standard deviation is meaningless. Excel can't tell the difference; it just crunches numbers. Always normalize units before any statistical work. A quick column with =A2/60 to convert seconds to minutes prevents hours of confused debugging later.
Fourth on the list: reporting standard deviation without the sample size. A standard deviation of 2 from three data points is almost meaningless. The same number from three thousand data points is reliable and well-supported. Always include the count next to your result so readers can judge how seriously to take the spread you've reported.
Finally, beware comparing standard deviations from datasets with different means. Two columns of numbers can have identical standard deviations but very different relative variability. Switch to the coefficient of variation whenever you want to compare scatter across datasets that sit at different scales. That tiny adjustment makes your comparisons honest.
Document every assumption inside your workbook. Add a small notes cell that records which formula variant you chose, why, and what range it covers. Future-you (or the analyst who inherits the file) will save hours of confusion when revisiting the analysis months later.
STDEV.S for samples (the most common case) and STDEV.P for full populations. If you're not sure whether your data is a sample or population, ask yourself whether you're trying to learn about a bigger group. If yes, that's a sample, so use STDEV.S.STDEV.S and STDEV.P accept up to 255 separate arguments. Example: =STDEV.S(A2:A50, C2:C50, E2:E50) combines three columns into one calculation. Excel treats the values as one combined dataset.STDEV name was kept for backward compatibility with files made before 2010. New workbooks should use STDEV.S because the name makes the sample-vs-population choice explicit.