How to do a Mann-Whitney U test in Google Sheets

The example data

A website team measured how long users took to finish a task (in minutes) with the old page layout and with a new one, ten different users each. Task times are often skewed by a few very slow users, so a rank-based test is a sensible choice. Put the group in column A (type Old or New) and the time in column B, one user per row (rows 2 to 21):

Step 1: rank all values together

In C2, fill down to C21:

=RANK.AVG(B2, $B$2:$B$21, 1)

The 1 ranks from smallest to largest. RANK.AVG gives tied values the average of their ranks, which the test requires. The smallest time (6.8) gets rank 1 and the largest (30.4) rank 20.

Step 2: rank sums and U

U₁ = 82 out of a maximum of n₁ × n₂ = 100 means that, in 82% of all old/new pairs of users, the old-layout user was slower.

Step 3: z and the p-value

F8 (z): =(F7-F2*F3/2)/SQRT(F2*F3*(F2+F3+1)/12)
F9 (p): =2*NORMSDIST(-ABS(F8))

Results: z = −2.42 and p = 0.016. The difference is significant at the 0.05 level. The exact p-value, which some statistical software uses for samples this small, is 0.015 (0.017 with a continuity correction): the same conclusion.

Step 4: effect size

Two effect sizes are common. r = |z| / √N = 2.42 / √20 = 0.54 (large; benchmarks are .1, .3 and .5). The rank-biserial correlation = 1 − 2U / (n₁n₂) = 1 − 36/100 = 0.64.

Describe the groups with medians

Because the test works on ranks, report medians rather than means: =MEDIAN(FILTER(B2:B21, A2:A21="Old")) gives 14.85 minutes, and the same for "New" gives 9.80 minutes.

Things to watch

• Ties: with many tied values, the z formula above slightly overstates the spread. Software applies a tie correction.
• Small samples: below about 10 per group, prefer an exact p-value.
• Paired data: if the same people are measured twice, use the Wilcoxon signed-rank test instead.
• What it tests: strictly, whether values in one group tend to be larger than in the other. It is a test of medians only when both groups have the same shape.

How to report it (APA style)

Frequently asked questions

When should I use Mann-Whitney instead of a t-test?

Use it for two independent groups when the data are ordinal (ranks, ratings) or clearly not normal with small samples, or when outliers would distort the means.

Is the Mann-Whitney U test the same as the Wilcoxon rank-sum test?

Yes. They are two names for the same test and give the same p-value. The Wilcoxon signed-rank test is different: it is for paired data.

Which U do I report?

Many textbooks and SPSS report the smaller of U1 and U2 (18 here), while R and SciPy report U for the first group (82 here). Both give the same p-value. Say which one you report, and give the medians so the direction is clear.

Is the normal approximation accurate for small samples?

It is reasonable from about 10 values per group. For smaller samples, or with many ties, an exact p-value is better; ExplainStats computes the exact p-value for small samples automatically.