How to calculate a z-score in Excel

The z-score formula

An online shop wants to know which days were unusual over two weeks. A z-score answers that on a common scale: 0 is an average day, 1 is one standard deviation above, -2 is two below.

The dollar signs matter. Without them, filling down shifts the range to B3:B14, B4:B15 and so on, and each row is compared with a different, shrinking set of days. Lock the range, leave the B2 at the front relative.

STANDARDIZE: the same thing, spelled out

STANDARDIZE(x, mean, standard_dev) does the subtraction and division for you, but it does not calculate the mean or standard deviation. You still pass them in: =STANDARDIZE(B2,AVERAGE($B$2:$B$13),STDEV.S($B$2:$B$13)). A common mistake is =STANDARDIZE(B2,$B$2:$B$13,...), passing the range where the mean should go.

There is no advantage in accuracy. Use STANDARDIZE if it makes the sheet easier to read, or the plain formula if colleagues will audit it. A cleaner layout for both: put the mean in E2 and the standard deviation in E3 once, then use =(B2-$E$2)/$E$3. If the standard deviation is 0 (every value identical), STANDARDIZE returns #NUM! and the plain formula returns #DIV/0!.

Sample or population standard deviation?

Use STDEV.S when the data is a sample of a bigger process, such as two weeks out of the shop's whole history. Use STDEV.P when it is the complete group, such as the final scores of every student in one class when the class is all you are describing. Textbook z-score formulas for test scores often use the population version.

STDEV.P is smaller, so z-scores based on it are slightly larger. With 12 values the difference is about 4%, which can tip a borderline value over a cutoff. The formula below uses the population standard deviation written out; in Excel you would just swap STDEV.S for STDEV.P.

Flag outliers with |z| > 2 or 3

Wrap the z-score in ABS so high and low values are both caught, and use IF to label them. The usual rule of thumb is |z| > 2 for "worth a look" and |z| > 3 for "very unusual". These come from the normal (bell) curve, where roughly 95% of values fall within 2 standard deviations, so they only mean much when the data is roughly bell-shaped.

To count the flags in one cell: =SUMPRODUCT(--(ABS(($B$2:$B$13-AVERAGE($B$2:$B$13))/STDEV.S($B$2:$B$13))>2)). Here that returns 1: only the promo day. The outage day, Thursday 12 Nov with 61 orders, scores about -1.76 and is not flagged. The next section explains why.

Why |z| > 3 can miss the outliers in small data

Outliers inflate the standard deviation they are measured against. Take out the promo and outage days and the other ten days have a standard deviation of about 5.3 orders. With them in, it is about 50. So the outage day scores only -1.76 and slips under the |z| > 2 rule, even though it was 75 orders below the slowest normal day.

There is also a hard limit. With the sample standard deviation, no value in a set of n numbers can have a z-score bigger than (n - 1) / SQRT(n). For 12 days that is about 3.18, and for 10 values or fewer it is under 3, so a |z| > 3 rule can never fire at all. For small data, or data with several odd values, compare against the median instead. A common robust version is the modified z-score: 0.6745 x (value - median) / MAD, where MAD is the median of the absolute distances from the median, with values beyond 3.5 or -3.5 flagged.

Highlight unusual values without a helper column

Conditional formatting can colour the cells directly with the same test, so the flags update as you add days.

1. Select B2:B13.
2. Go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format.
3. Enter =ABS((B2-AVERAGE($B$2:$B$13))/STDEV.S($B$2:$B$13))>2 and pick a fill colour.
4. Click OK. The rule is written for B2, the top-left cell of the selection, and Excel adjusts it for every other cell.