Mann-Whitney U test in Excel, step by step

When to use the Mann-Whitney U test

Use it to compare two independent groups, with different customers, orders or patients in each, when a two-sample t-test is a poor fit: the data is skewed, has outliers, is measured on an ordinal scale such as ratings, or the groups are small. Delivery times are a typical case. Most are close together, but a few late orders stretch the right tail and pull the mean up. Ranks ignore how extreme a value is, so the 61-minute delivery counts no more than any other slowest value would.

For paired data, such as the same couriers before and after a training, use the Wilcoxon signed-rank test instead. For three or more groups, use Kruskal-Wallis.

Worked example: delivery times with the old and the new routing app

Rows 2 to 18 are the data, one delivery per row, ranked together in column C. Rows 19 to 29 calculate U, z and the p-value. The data is fictional.

Worked example: delivery times with the old and the new routing app

	A	B	C
1	App	Minutes	Rank (all 17)
2	Old	34	8.0
3	Old	41	13.0
4	Old	29	3.5
5	Old	52	16.0
6	Old	38	11.0
7	Old	45	15.0
8	Old	36	10.0
9	Old	61	17.0
10	New	27	2.0
11	New	31	6.0
12	New	29	3.5
13	New	33	7.0
14	New	40	12.0
15	New	26	1.0
16	New	35	9.0
17	New	30	5.0
18	New	44	14.0
19		Old	New
20	Group size n	8	9
21	Median minutes	39.5	31.0
22	Rank sum R	93.5	59.5
23	U = R − n(n + 1)/2	57.5	14.5
24	Check: U₁ + U₂ and n₁ × n₂	72.0	72
25	U (the smaller one)	14.5
26	Mean of U under H0	36.0
27	SD of U	10.392
28	z	-2.069
29	Two-sided p-value	0.0386

No tie correction is applied here. With a single tie the difference is negligible.

Step 1: rank all values together

Put the data in one long column with a group label next to each value, then rank every value against all values: =RANK.AVG(B2,$B$2:$B$18,1). The last argument 1 ranks in ascending order, so the fastest delivery gets rank 1. RANK.AVG gives tied values the average of the ranks they share. Two deliveries took 29 minutes, one with each app, and share ranks 3 and 4, so both get 3.5. RANK.EQ would give both a 3, and the rank sums would no longer add up.

Check: the ranks of N values always add up to N(N + 1)/2. With 17 deliveries that is 153, and 93.5 + 59.5 = 153.

Step 2: rank sums and U

Add up the ranks of each group with =SUMIF($A$2:$A$18,"Old",$C$2:$C$18): R = 93.5 for the old app and 59.5 for the new one. Then subtract the smallest rank sum the group could possibly have:

U = R − n(n + 1)/2

For the old app that is 93.5 − 8 × 9 / 2 = 57.5, for the new app 59.5 − 9 × 10 / 2 = 14.5. U counts how often a value from one group is larger than a value from the other, ties counting half. The two U values always add up to n₁ × n₂, here 57.5 + 14.5 = 72 = 8 × 9, a quick check that the ranking is right. The test statistic is the smaller U, 14.5.

Step 3: z-score and p-value with the normal approximation

If both apps were equally fast, U would be around n₁n₂/2 = 36. Its standard deviation is √(n₁n₂(n₁ + n₂ + 1)/12) = √(8 × 9 × 18 / 12) = 10.392. The z-score is (14.5 − 36) / 10.392 = −2.069, and the two-sided p-value is

=2*NORM.S.DIST(-ABS(z),TRUE) = 0.0386

That is below 0.05, so you reject the hypothesis that both apps deliver equally fast. With groups this small the normal approximation is rough, so it is worth checking an exact table as well: for groups of 8 and 9, the two-sided 5% critical value of U is 15, and 14.5 is below it, which gives the same answer.

How to report the result

Report the medians and group sizes, not the means, because the test works on ranks: "Deliveries with the new app (median 31 minutes, n = 9) were faster than with the old app (median 39.5 minutes, n = 8), Mann-Whitney U = 14.5, z = −2.07, p = 0.039." Strictly, the test shows that values in one group tend to be larger than in the other. It is a test of medians only if both distributions have the same shape. If readers need a sense of size, add an effect size such as the rank-biserial correlation next to the p-value.

Ties, continuity correction and exact tests

Ties make the standard deviation of U slightly smaller, and statistics packages apply a tie correction automatically. With one tie in 17 values, as here, the effect is negligible, but with many ties, such as ratings from 1 to 5, apply the correction or the p-value will be a little too large. Some textbooks also subtract 0.5 from |U − mean| as a continuity correction, which makes the test slightly more conservative. With fewer than about 10 values per group, read the critical value from an exact table instead. To see why skewed data needs a rank test in the first place, plot it with a histogram in Excel, and to plan how many observations an experiment needs, see the A/B test sample size guide.

Common mistakes

• Ranking each group separately. The ranks must come from all values together. Otherwise both rank sums start at 1 and U means nothing.
• Using RANK.EQ with ties. Tied values must share the average rank. RANK.EQ gives them the same lower rank, and the rank sums no longer add up to N(N + 1)/2.
• Losing track of the direction. Ranking in descending order gives the same test, but the smaller U then belongs to the other group. Rank ascending and say so.
• Using it on paired data. Before-and-after measurements on the same people need the Wilcoxon signed-rank test.
• Reporting means. The test works on ranks, so report the medians and the group sizes.
• Trusting the normal approximation with tiny groups. With fewer than about 10 values per group, check an exact table.