Descriptive statistics in Excel: ToolPak and formulas, read on real order values

Descriptive statistics in Excel come two ways: the Analysis ToolPak writes a summary table in one step, and worksheet formulas compute the same figures live. On ten order values, the mean is $2,213 and the median $1,475; the gap is one order of $8,900, and the rest of the statistics show exactly how much it distorts.

Descriptive statistics in Excel, two ways

ToolPak route: Data > Data Analysis > Descriptive Statistics, select the input range, check Labels if the first cell is a header, check Summary statistics, OK.

Formula route: AVERAGE, MEDIAN, MODE.SNGL, STDEV.S, VAR.S, MIN, MAX, QUARTILE.INC, SKEW and KURT on the same range.

If the Data Analysis button is missing, the add-in is not switched on; the Data Analysis ToolPak guide has the click path for Windows and Mac and covers its other 18 tools. This page is about the statistics themselves. Microsoft's Analysis ToolPak reference lists Descriptive Statistics among the tools.

The rows you need

One column of numbers for one population: order values for one period, one currency, one business unit. No subtotal or total rows inside the range, no text, no blanks standing in for zero. Run the five checks before you trust a spreadsheet total first; a summary of the wrong rows is precise and wrong.

Worked example: ten order values

Ten order values in B2:B11: 1,200; 1,450; 980; 2,300; 1,750; 1,100; 1,600; 8,900; 1,350; 1,500.

Sorted, the values are 980; 1,100; 1,200; 1,350; 1,450; 1,500; 1,600; 1,750; 2,300; 8,900. With ten values the median is the average of the fifth and sixth: (1,450 + 1,500) / 2 = 1,475. The interquartile range is 1,712.50 − 1,237.50 = 475: the middle half of orders sits inside a $475 band.

The Analysis ToolPak output, line by line

The ToolPak's Summary statistics table has thirteen lines, each matching one formula above:

The ToolPak does not print quartiles; add QUARTILE.INC beside it. Its table is also pasted as values, so it does not update when the orders change. The formulas do.

The check

Three identities prove the table:

=SUM(B2:B11)/COUNT(B2:B11)    2,213        must equal AVERAGE
=SQRT(VAR.S(B2:B11))          2,378.94     must equal STDEV.S
=MAX(B2:B11)-MIN(B2:B11)      7,920        must equal the ToolPak Range

22,130 / 10 = 2,213 and √5,659,356.67 = 2,378.94. If the ToolPak and the formulas disagree on any line, the input range differs: usually the Labels box was set the wrong way, so the header was read as data or the first order was read as a header.

Reading it: one large order

Read mean and median together. The mean of $2,213 is $738 above the median of $1,475, and eight of the ten orders are below the mean. That is the sign of a few large values pulling the average up, and here it is one order of $8,900.

Take it out and the other nine orders have a mean of $1,470, a median of $1,450 and a standard deviation of $395.25. One order moved the mean by $743 and multiplied the standard deviation about six times; the median moved by $25.

Shape says the same. Microsoft's SKEW reference describes positive skewness as an asymmetric tail extending toward more positive values; 3.02 is a long right tail. KURT returns excess kurtosis: the NIST/SEMATECH e-Handbook explains that a normal distribution has kurtosis of three, and the excess definition subtracts three so the normal scores zero. At 9.33 the set is heavy-tailed, again because of one value.

The quartiles give a test. The usual fence is 1.5 × IQR beyond the quartiles: 1,712.50 + 712.50 = 2,425 at the top and 525 at the bottom. The 8,900 order is outside it; 2,300 is not. Whether to keep it is a business question, covered in outliers in sales data; mark it with an outlier flag rather than deleting it. For a "typical order" in a report, quote the median and the quartiles, which is also how to define the norm from your own customer base.

Sample or population: STDEV.S vs STDEV.P

Microsoft's STDEV.S reference says it assumes its arguments are a sample of the population, and to use STDEV.P when the data is the entire population. STDEV.S divides by n − 1, STDEV.P by n. On these ten orders STDEV.P returns 2,256.86 against 2,378.94.

Use STDEV.S when one month's orders stand for orders in general, which is the usual reporting case. Use STDEV.P when the ten rows are the whole thing you are describing, such as all ten branches of a company. With hundreds of rows the two converge.

Descriptive statistics by group

Per customer or region, use the conditional functions on a table named Orders:

=AVERAGEIFS(Orders[Value],Orders[Region],"Northeast")
=MEDIAN(FILTER(Orders[Value],Orders[Region]="Northeast"))
=STDEV.S(FILTER(Orders[Value],Orders[Region]="Northeast"))
=QUARTILE.INC(FILTER(Orders[Value],Orders[Region]="Northeast"),1)

FILTER needs Excel 365 or Excel 2021. Group means weighted by order count and by value give different answers; count-weighted and value-weighted explains which to use.

Where it goes wrong

• Totals inside the range. A subtotal row in the column double counts and wrecks every statistic.
• Numbers stored as text. AVERAGE, COUNT and STDEV.S ignore text in a reference, so those rows silently drop out. Compare COUNT with COUNTA.
• The mean alone. Order values are almost always skewed right. Report the median and quartiles beside the mean.
• A static ToolPak table. The output is values. Change the data and the table is stale; rebuild it or use formulas.
• Mixed periods or currencies. Two months or two currencies in one column describe nothing real.

Spread and outliers from your own files

Covirage computes medians, quartiles and outlier flags per customer, product or region from your uploaded files and states which rows were excluded; the external AI model explains the spread, never the arithmetic. See Covirage for finance teams to run it on your own order data. For the next steps, see regression in Excel, moving average in Excel and weighted average in Excel.