STATS_KRUSKAL – Kruskal–Wallis H test in Google Sheets™

What it does

Kruskal–Wallis test for two or more independent groups (usually three or more; for two it agrees with STATS_MANN_WHITNEY) — the rank replacement for STATS_ANOVA when the data are not normal, or are ranks or rating scales. Each selected column is a group, as in STATS_ANOVA; no column is dropped.

The test says whether the groups differ, not which ones: there are no pairwise (post-hoc) comparisons yet.

Mean Rank is the average rank of a group in the joint ranking of every value: the further apart the mean ranks, the larger H.

H is corrected for ties, as SPSS; df is the number of groups minus one; Asymp. Sig. is the upper tail of chi-square, so there is no alternative (the test is one-sided by construction).

On two groups H equals the square of the Mann–Whitney Z and the p-values agree.

Data in long format (one column of values, one of groups) are split into group columns first with STATS_SPLIT_COLS.

Syntax

=STATS_KRUSKAL(columns, colFilter)

Parameters

columns — required

Cell range with data, e.g., A1:D100 or A:D.

Usually the first row should contain column names.

You can also add labels as the first row and column names as the second row.

Labels should use formats like "male:1,female:2" or "low:1:3,mid:3:5,high:5:7".

Examples:

A1:D100 — use 4 columns
A1:C200 — use 3 columns
A:F — use 6 columns
A1:D100 where row 1 contains labels and row 2 contains column names — use labels from the sheet

If every non-empty cell of the first row holds at least two label rules ("low:1,high:2"), it is the labels row, and the next row holds the column names; an empty cell there is a column without labels.

One rule in a cell ("yes:1") is read as a name, so a header such as "T:1", "Ratio 1:2" or "Note:" stays the names row. Give such a column a second rule, or pass one label in the labels parameter, where one rule is valid.

If the first row contains ordinary text, it is treated as the column names row as before.

A number, a date or a checkbox in the names row is a name: 2025, 2025-01-01, TRUE.

Column names must be unique: two identical headers are an error "Duplicate column name: x".

Every column with values needs a name: an empty header cell over values is an error "The name of column 2 is empty: a column with values needs a name", even when colFilter does not select that column. A column with neither a name nor values (the empty right edge of a range such as A:Z) is dropped without an error.

Column names that start with "#" are reserved/unsupported in filters, because "#" is used for index-based colFilter rules (for example #1, #2-#5).

What counts as a number (the same in every function):

A column is numeric when it holds more numbers than text; with as many texts as numbers, or more, it is a text column.
Every cell is typed by itself: a number written as text ('10', '007') is the number, a checkbox is 1 (checked) or 0 (unchecked). The texts 'TRUE'/'FALSE' stay text.
A cell that does not match the type of its own column (text in a numeric column, a number in a text column) is missing for calculations and is counted in missing.
An empty cell is a missing answer; the sign "." means "not applicable" — it is not a missing answer, not a category and not counted in missing.
A text column with value labels (a labels row above the names) is analysed by the codes of its labels. A text column without labels is not analysed as numbers: lists of columns skip it with a note, tests on a pair of columns and models show an error.

An empty tail of the range (rows below the data) is dropped — only blank cells make it: a row of "." at the end stays and counts as not applicable, as in the middle. A column with neither a name nor values is dropped; a named empty column stays.

colFilter — optional, default "*"

Comma-separated column selector.

Use names ("age,score"), negative rules ("-id,-note"), a regex ("/^q/"), or index rules ("#N", "#A-#B", "-#N", "-#A-#B").

Case-sensitive; flags are not supported.

A name that does not exist is an error: "Column nope does not exist" (excluding it, "-nope", is ignored). Empty — all columns ("*").

Examples:

"age,score" — Names only (include only "age" and "score")
"-id,-group" — Exclude some (include all except "id" and "group")
"/^q/" — Regex only (all starts with "q")
"#1,#3-#5" — Index include rules (first, and range 3..5)
"-#1,-#3-#4" — Index exclude rules (drop first and 3..4)
"age,#2-#4,-#3" — Mixed name/index rules with deterministic order
"age,/^q/,-q5" — Mixed (include age, all starts with "q" except "q5")
"/^x1{1,2}$/" — Regex with a comma inside: x1 and x11

Choose which columns from columns to use - a comma-separated string of rules.

⚙️ Four rule types (you can mix them):

By name - exact header text (case-sensitive)
- "age,score,total"
Negative rules - exclude by name (prefix with -)
- "-id,-notes" (include all except id and notes)
Regex - a regular expression between slashes /.../
- "/^q/" (all headers starting with q), "/(score|age)/", "/^x1{1,2}$/" (commas inside /.../ do not split the filter)
- ❗— case-sensitive
- ❗— flags are not supported.
Index rules - position-based rules with #
- "#1" (first selected column), "#2-#5" (range), "-#1", "-#2-#4"

"*" alone selects every column; it cannot be combined with other rules ("*,-id" is an error) — write "-id" instead.

Rules are applied in the written order, and the selected columns keep that order ("/^q/,-q5,age" puts age last).

Index rules count the columns of the range: "#1" is its first column.

A name that does not exist is an error: "Column nope does not exist" — also in colName, yName, steps, rowCol, colCol and colNameForSplit. Excluding a name that does not exist ("-nope") is ignored.

A filter that selects no column — a regex without a match ("/^Q/" where the columns are q1, q2), index rules past the end, exclusions that remove every column — is an error that names the filter and the columns of the range: colFilter "/^Q/" selects no columns (columns: q1, q2, t). Empty and "*" select every column.

In STATS_ANALYZE, a regex with brackets or | needs the new syntax: PEARSON('/(score|age)/').

A colFilter saved in Settings applies to every formula without its own filter: on a range without those columns the formula shows the error above.

# prefix is reserved for index syntax and is not treated as a column-name filter token.

colFilter applies to all expressions in query. It does not create separate groups per expression.

Examples

— the sample data (50 rows) all examples use
— three groups =STATS_KRUSKAL(Data!A1:T51, "x,x1,x2")
— every column whose name starts with q is a group =STATS_KRUSKAL(Data!A1:T51, "/^q/")

What the result contains

A report that spills below the formula cell:

the title "Kruskal-Wallis Test for a,b,c",
Ranks: N and Mean Rank of each group,
Test Statistics: Kruskal-Wallis H, df, Asymp. Sig.

How to read the result

Each group must be its own column. If the scores are in one column and the group (gender, class) in another, first split them with STATS_SPLIT_COLS — one column per group — and select those columns here.

Start with Asymp. Sig.: below 0.05 — at least one group tends to have higher values than the others.

The Mean Rank of each group shows which ones are higher. To find the pairs that differ, run STATS_MANN_WHITNEY on each pair and compare its p with 0.05 divided by the number of pairs (3 pairs — 0.017): the Bonferroni correction.

Use it instead of one-way ANOVA for rating scales or skewed data. Its p is a chi-square approximation: with very small groups (fewer than about 5 values each) it is rough.

In a report: "Satisfaction differed between the three departments, H(2) = 9.84, p = .007."

What each number means

Ranks

Column — Column: Read the numbers along the row for that column.
N — N: Compare it with your sample: a small group makes the test weak.
Mean Rank — Mean rank: The rank-test counterpart of the mean: the group with the higher mean rank tends to have the higher values. Report it next to the test, or the medians of the groups.

Test Statistics

Kruskal-Wallis H — Kruskal–Wallis H: The rank-based counterpart of the ANOVA F: the larger H, the more the groups differ. Read Asymp. Sig.; in a report: H(2) = 1.71, p = .425.
df — Degrees of freedom of H: Written in brackets after H: H(2) = 1.71.
Asymp. Sig. — Asymptotic significance: This is the p of the test. Below 0.05 — at least one group tends to have higher values than the others. To find which pairs differ, run STATS_MANN_WHITNEY on each pair and compare its p with 0.05 divided by the number of pairs (3 pairs — 0.017): the Bonferroni correction. In a report: H(2) = 1.71, p = .425.

How it is computed

Each selected column is a group with its own valid values.

All values are ranked together (ties get the average rank); H = 12 / (N(N + 1)) · Σ Rᵢ²/nᵢ − 3(N + 1), divided by the tie correction 1 − Σ (t³ − t) / (N³ − N), as SPSS NPAR TESTS.

p from the chi-square distribution with k − 1 degrees of freedom.

Errors

One cell instead of the report:

one column: "Kruskal-Wallis Test requires at least 2 groups";
fewer than 2 values in a column: "Kruskal-Wallis Test: need at least 2 observations";
every value of every group equal: "Kruskal-Wallis Test: all values of a, b, c are equal, the test is not defined";
a text column without value labels: "Kruskal-Wallis Test needs numeric columns, text columns: t (add value labels or recode to numbers)";
a column name that does not exist: "Column nope does not exist".

About STATS_KRUSKAL

STATS_KRUSKAL compares two or more independent groups by ranks. It replaces one-way ANOVA when the data are not normal, or are rating scales or ranks. Each selected column is one group, as in STATS_ANOVA. If the scores are in one column and the group in another, split them first with STATS_SPLIT_COLS. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.

All values are ranked together, and the Ranks table shows the Mean Rank of each group: the further apart the mean ranks, the larger H. H is corrected for ties, as in SPSS; df is the number of groups minus one, and Asymp. Sig. is the p-value. Below 0.05, at least one group tends to have higher values than the others. The test does not say which pairs differ: to find them, run STATS_MANN_WHITNEY on each pair and compare its p with 0.05 divided by the number of pairs (the Bonferroni correction).

Every number of the report is explained on this page in plain words, together with the line to write in your report, for example: Satisfaction differed between the three departments, H(2) = 9.84, p = .007.