Standard Deviation Formula in Excel: STDEV.P vs STDEV.S Guide 2026
Master the standard deviation formula in Excel. Learn STDEV.P, STDEV.S, when to use each, syntax, examples, and the fastest shortcut for accurate results.

Microsoft Excel: upcoming dates and official changes
- Jun 30, 2026: Certiport retired the Microsoft Office Specialist 2016 certifications, including MOS Excel 2016, on June 30, 2026. The Microsoft 365 Apps and Office 2019 Excel exams are still available. (certiport.pearsonvue.com)
- Updated Oct 16, 2026: Delivery is now Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) (previously Proctored live-in-the-application exam via Certiport Authorized Testing Centers).
- Updated Oct 16, 2026: Eligibility is now No formal prerequisites; about 150 hours of instruction and hands-on experience recommended (previously No formal prerequisites; ~150 hours of instruction and hands-on experience recommended (Associate)).
- Updated Oct 16, 2026: Time limit is now 50 minutes (60 minutes seat time) (previously 50 minutes (60 minutes seat time)).
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
- The MOS Excel (Microsoft 365 Apps) exam has 33 to 37 questions. (certiport.pearsonvue.com)
- The MOS Excel Expert (Microsoft 365 Apps) exam has 23 to 27 questions. (certiport.pearsonvue.com)
- Microsoft Office Specialist Excel exams last 50 minutes, with 60 minutes of total seat time. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- The passing score for Microsoft Office Specialist Excel exams is always 700 on Certiport's 1 to 1000 scaled score. (certiport.pearsonvue.com)
- After failing a Microsoft Office Specialist Excel exam, candidates must wait 24 hours before the first retake and 48 hours before each later retake, and there is no annual limit on attempts. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft Office Specialist Excel certifications earned on or after September 23, 2025 are valid for five years; those earned earlier do not expire. (certiport.pearsonvue.com)
- The Microsoft Office Specialist Excel exam is proctored and delivered live in the Excel application through Certiport Authorized Testing Centers, either in person or remote-proctored. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft recommends that candidates for the MOS Excel Associate (Microsoft 365 Apps) certification have about 150 hours of instruction and hands-on experience with Excel. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Candidates get their MOS Excel score report as soon as they finish the exam, and it can also be viewed in their Certiport profile. (certiport.pearsonvue.com)
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Microsoft Excel Exam at a Glance
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1โ1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
=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.What the Standard Deviation Formula in Excel Actually Does
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. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).
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.
Free Excel Study Tools
Interactive Excel resources on PracticeTestGeeks, free to start:
- Free Excel Questions and Answers โ 16 questions with instant scoring and answer explanations
- Excel flashcards โ review key terms and concepts card by card
- Excel study guide โ topic-by-topic overview of what the exam covers
- Excel cheat sheet โ one-page printable summary for last-minute review

Standard Deviation Functions at a Glance
STDEV.S vs STDEV.P: Which One Do You Pick?
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.
Three Ways to Calculate Standard Deviation in Excel
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.
Step-by-Step: Build the Formula From Scratch
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.
Common Standard Deviation Formula Variations
Sample standard deviation, divides by n-1. The modern, recommended choice for almost all analytics work.
- โธUse for surveys
- โธUse for test data
- โธUse for sampled measurements
- โธReturns same units as data
Population standard deviation, divides by n. Use only when your data is the complete population of interest.
- โธUse for full datasets
- โธSlightly lower result than STDEV.S
- โธCommon in physics/engineering
- โธNo Bessel correction
Older sample-version function kept for compatibility. Behaves identically to STDEV.S in modern Excel.
- โธAvailable in Excel 2007 and older
- โธUse STDEV.S instead
- โธStill works in formulas
- โธAvoid in new workbooks
Older population-version function. Same math as STDEV.P, kept for compatibility with older spreadsheets.
- โธAvailable in Excel 2007 and older
- โธUse STDEV.P instead
- โธStill works in formulas
- โธAvoid in new workbooks

A Worked Example You Can Copy Right Now
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.
VALUE() or Paste Special > Multiply by 1 to convert them before calculating.The Hidden Trap: When Excel Returns #DIV/0!
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.
Quality-Check Your Standard Deviation Formula
- โPicked STDEV.S for samples, STDEV.P only for full populations
- โRange contains at least 2 numeric values (5+ preferred)
- โNo text or empty rows accidentally included as zeros
- โResult is in the same units as the source data
- โResult is smaller than the range max minus the range min
- โCross-checked with =COUNT(range) to confirm cell count
- โResult is reasonable compared to the data's visual spread
Speed Tricks: Calculate Without Typing
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.
Combining Standard Deviation With Other Excel Formulas
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.

Standard Deviation Combined With Other Functions
Classic descriptive statistics pair. Use to report mean ยฑ standard deviation in a single readable cell.
- โธMean shows the center
- โธSTDEV shows the spread
- โธTogether describe the distribution
Build confidence intervals for small samples. Standard deviation drives the margin-of-error calculation.
- โธWorks for n < 30
- โธReturns half-width of CI
- โธAdd/subtract from mean
Better pairing when data has outliers. Median is strong to extremes while STDEV still describes spread.
- โธUse for skewed data
- โธUse for income/salary stats
- โธReports both center and spread
Compute standardized scores to flag outliers automatically. Z above 2 or below -2 marks unusual values.
- โธFormula: (x - mean) / stdev
- โธUnitless ratio
- โธSame scale across datasets
How Standard Deviation Connects to Other Statistical Measures
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.
From Raw Data to Standard Deviation in Four Steps
Step 1: Audit Your Data
Step 2: Pick the Right Variant
Step 3: Enter the Formula
Step 4: Sanity-Check the Result
Real-World Use Cases That Actually Matter
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.
Standard Deviation Sanity Checks Before You Hit Save
- โThe right function variant matches your data type
- โRange is anchored with $ signs if the formula will be copied
- โAll values share the same units (no mixing seconds with minutes)
- โFormula references stop at the actual last data row, not the bottom of the sheet
- โResult is wrapped in IFERROR if it will appear on a dashboard
- โResult is rounded sensibly with ROUND() if displayed publicly
Mistakes That Cost You Points and Promotions
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.
Readers who want more detail usually continue with standard deviation formula excel.
If you want more targeted practice, the ms excel download free page covers the same material with additional questions and explanations.
For a deeper look at this topic, see our guide to standard deviation formula excel.
Standard Deviation Formula Excel Pros and Cons
- +Calculates spread in one short formula instead of seven manual steps
- +Result stays in the original units (dollars, kg, minutes, etc.)
- +Works on huge datasets with no performance hit
- +Pairs naturally with AVERAGE, MEDIAN, and confidence intervals
- +Modern functions (STDEV.S, STDEV.P) make the sample vs population distinction obvious
- โPicking the wrong variant (sample vs population) silently gives you a wrong answer
- โSensitive to outliers โ a single extreme value distorts the result heavily
- โMixing text-formatted numbers with real numbers produces misleading counts
- โResult is meaningless on non-numeric or categorical data
- โDoesn't tell you the shape of the distribution โ only its spread
Pros and Cons at a Glance
| Pros | Cons |
|---|---|
| Calculates spread in one short formula instead of seven manual steps | Picking the wrong variant (sample vs population) silently gives you a wrong answer |
| Result stays in the original units (dollars, kg, minutes, etc.) | Sensitive to outliers โ a single extreme value distorts the result heavily |
| Works on huge datasets with no performance hit | Mixing text-formatted numbers with real numbers produces misleading counts |
| Pairs naturally with AVERAGE, MEDIAN, and confidence intervals | Result is meaningless on non-numeric or categorical data |
| Modern functions (STDEV.S, STDEV.P) make the sample vs population distinction obvious | Doesn't tell you the shape of the distribution โ only its spread |
Sample Excel Practice Questions
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
Let's say that cell A1 shows the value "12000.7789". How should this number be rounded to the nearest integer using the following formula?
- A. =MROUND(A1,10)
- B. =ROUND(A1,0)
- C. =MROUND(A1,100)
- D. =ROUND(A1,1)
Answer: B. =ROUND(A1,0)
The `ROUND` function in Excel is used to round a number to a specified number of decimal places. To round a number to the nearest integer, you specify `0` as the number of decimal places. This instructs Excel to round the value in cell A1 to the closest whole number.
The formula palette can be used to
- A. Enter assumptions data
- B. Copy a range of cells
- C. Create and edit formula containing functions
- D. Format cells containing numbers
Answer: C. Create and edit formula containing functions
The Formula Palette (or Function Arguments dialog box in modern Excel versions, accessed via the 'Insert Function' button next to the formula bar) is a helpful tool designed to assist users in creating and editing formulas, especially those involving functions. It provides a guided interface to input arguments for functions, making complex formulas easier to construct and debug by showing argument descriptions and results.
On the left of the formula bar, there is a Name box.
- A. Shows the name of worksheet currently working on
- B. Shows the name of cell or range currently working on
- C. Show the name of Workbook currently working on
- D. None of these
Answer: B. Shows the name of cell or range currently working on
The Name Box, situated to the left of the formula bar in Excel, displays the address of the currently selected cell (e.g., A1). When a range of cells is selected or a named range is active, it shows the address of the first cell in the range or the name of the defined range. It also allows users to quickly navigate to specific cells or ranges by typing their names.
Which formula correctly converts the text string "123" in cell A1 to a numeric value?
- A. =TEXT(A1)
- B. =VALUE(A1)
- C. =NUMBER(A1)
- D. =INT(A1)
Answer: B. =VALUE(A1)
VALUE converts a text string that looks like a number into an actual numeric value.
Excel Questions and Answers
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- learn.microsoft.com โ Retake policy, test delivery, time limit, eligibility ยท verified Oct 2026
- certiport.pearsonvue.com โ Exam format, score validity, passing score, score release, exam information ยท verified Oct 2026