Descriptive Statistics in Excel: One Outlier Turns a 10 ms Spread Into 194
Fifteen response times, one bad measurement, and the standard deviation moves from 10.00 to 194.42. Here is the full descriptive block, the IQR rule that finds the culprit automatically, and the QUARTILE variant that gives a different answer.
The Block Worth Building Once
Before any chart or test, a column of numbers deserves a dozen cells summarising it. Centre, spread, shape, and extremes. Building that block takes two minutes and it catches the data problems that would otherwise survive into a conclusion.
The dataset here is fifteen API response times in milliseconds, one of which went badly wrong.
Try the standard deviation calculatorPaste a dataset for mean, median, variance, and both standard deviation modes with the working shown.The data
Sorted: 120, 126, 128, 131, 133, 135, 137, 139, 141, 142, 144, 148, 152, 155, 890.
Swipe sideways to compare columns.
| Measure | Formula | All 15 | Excluding 890 |
|---|---|---|---|
| Count | =COUNT(range) | 15 | 14 |
| Mean | =AVERAGE(range) | 188.07 | 137.93 |
| Median | =MEDIAN(range) | 139.00 | 138.00 |
| Standard deviation, sample | =STDEV.S(range) | 194.42 | 10.00 |
| Standard deviation, population | =STDEV.P(range) | 187.83 | 9.64 |
| Minimum | =MIN(range) | 120 | 120 |
| Maximum | =MAX(range) | 890 | 155 |
| First quartile | =QUARTILE.INC(range, 1) | 132.00 | 131.50 |
| Third quartile | =QUARTILE.INC(range, 3) | 146.00 | 143.50 |
| Trimmed mean, 20% | =TRIMMEAN(range, 0.2) | 139.31 | 138.00 |
STDEV.S or STDEV.P
STDEV.S divides by n − 1 and STDEV.P divides by n. The choice is about what the data represents, not about how much of it there is. A sample standing in for a wider population uses STDEV.S; a complete set that is the thing being described uses STDEV.P.
These fifteen measurements are a sample of the service's behaviour, so STDEV.S is right. When in doubt use STDEV.S, which is the conservative choice because it reports more spread rather than less.
Swipe sideways to compare columns.
| Sample, n − 1 | Population, n |
|---|---|
| STDEV.S | STDEV.P |
| VAR.S | VAR.P |
| STDEV (legacy) | STDEVP (legacy) |
QUARTILE.INC and QUARTILE.EXC disagree
Excel has two quartile functions using different interpolation rules, and on this dataset they give different answers.
Swipe sideways to compare columns.
| QUARTILE.INC | QUARTILE.EXC | |
|---|---|---|
| Q1 | 132.00 | 131.00 |
| Median | 139.00 | 139.00 |
| Q3 | 146.00 | 148.00 |
| IQR | 14.00 | 17.00 |
INC positions quartiles at (n − 1) intervals and can return values at the extremes; EXC positions them at (n + 1) intervals and excludes the endpoints. Neither is wrong. INC is the default in most software and PERCENTILE.INC matches it. Pick one, name it in your documentation, and never mix them within a report.
Finding the outlier automatically
The standard rule flags anything more than 1.5 interquartile ranges beyond the quartiles. It is robust precisely because quartiles are unaffected by the extreme values you are hunting for.
Shape, in two numbers
Swipe sideways to compare columns.
| Measure | Formula | Reading |
|---|---|---|
| Skewness | =SKEW(range) | Positive means a long right tail |
| Kurtosis | =KURT(range) | Positive means heavy tails and a sharp peak |
| Mean minus median | =AVERAGE(range)−MEDIAN(range) | Quick skew check with no new function |
The third row is the one to build into every summary. Here the mean exceeds the median by 49.07 milliseconds, on a median of 139. Any gap of that size means the mean is not describing a typical value, and it takes one subtraction to see it.
The Data Analysis ToolPak
File, Options, Add-ins, Excel Add-ins, Analysis ToolPak. It adds a Data Analysis button with a Descriptive Statistics option that produces the whole block in one dialog, including skew, kurtosis, and a confidence interval for the mean.
The catch is that the output is static values, not formulas. It does not update when the data changes. Use it for a one-off look and formulas for anything that lives in a workbook people will keep using.
What quietly goes wrong
- COUNT ignores text and blanks; COUNTA does not. If they differ, something in the column is not a number, and every statistic below is computed on fewer rows than you think.
- Numbers stored as text are excluded from AVERAGE and STDEV silently. Check with =COUNT against =COUNTA before trusting anything.
- MODE.SNGL returns #N/A when nothing repeats, which is correct and looks like an error. MODE.MULT returns all tied modes as a spilled array.
- AVERAGE ignores blanks and treats zeros as values. If a blank means zero in your data, the mean is wrong; if zero means missing, it is wrong the other way.
- Hidden or filtered rows are still included by all of these functions. Use SUBTOTAL or AGGREGATE if you want visible rows only.
What the summary does not tell you
- Whether the outlier is an error or a finding. A 890 ms response might be a broken measurement or the one request that matters most. The statistics identify it; only investigation decides what it means.
- The shape of the distribution. Every number here is consistent with several very different histograms, including bimodal data where the mean sits where nothing is.
- Anything about time order. Fifteen response times measured over a week and fifteen measured during one incident summarise identically and mean completely different things.
- Whether the sample is representative. A precise summary of a biased sample is a precise description of the bias.
- The tail, which for response times is usually the whole question. The 95th and 99th percentiles matter more than the mean for anything user-facing.
- Statistical significance. Descriptive statistics describe. Testing whether two groups differ is a separate exercise with its own assumptions.
Should I use STDEV.S or STDEV.P?
STDEV.S if the data is a sample of something larger, which is almost always the case. STDEV.P only when the numbers are the complete population you are describing, such as every employee in a department. When unsure, STDEV.S is the conservative choice.
Why do QUARTILE.INC and QUARTILE.EXC give different answers?
They interpolate differently. INC positions quartiles across n − 1 intervals and can return the extremes; EXC uses n + 1 intervals and excludes them. On the fifteen values here Q3 is 146 and 148 respectively. Choose one convention and state it.
How do I flag outliers in Excel?
Compute Q1, Q3, and the IQR, then flag anything below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR with an IF formula. Add conditional formatting on the same test so they are visible in the data rather than only in a helper column.
Why does my mean differ so much from my median?
Skew, usually from a small number of extreme values. Here the mean is 188.07 against a median of 139 because of one measurement. When the gap is large, report the median as the typical value and mention the outlier explicitly.
What does TRIMMEAN do?
It drops a percentage from each end and averages the rest. =TRIMMEAN(range, 0.2) removes 20% of the values, split between the two ends, and returns 139.31 here, close to the median while still using most of the data. Excel rounds the trim count down to an even number so both ends lose the same amount.
Do these functions include hidden rows?
Yes. AVERAGE, STDEV, and the rest ignore filtering entirely. Use SUBTOTAL with the appropriate function number, or AGGREGATE, which can also ignore errors, if you want the summary to follow what is visible.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team