STATS_SPEARMAN – Spearman Rank Correlation (rho) for Google Sheets™

What it does

Spearman rank correlation (rho): the correlation for rating scales, ranks and skewed data — does one go up as the other goes up?

Two columns give one rho with its p and interval; three or more give a correlation matrix.

The confidence interval uses the Fisher transform with the variance of Fieller, Hartley and Pearson, 1.06/(n − 3), as SPSS — slightly wider than a Pearson-style interval.

The p-value, as SPSS: t = rho·√((n − 2)/(1 − rho²)) with n − 2 degrees of freedom. It is the same on every recalculation (no permutations, no Monte Carlo); below about 10 rows, or with many ties, it is approximate and can differ from an exact permutation p.

Syntax

=STATS_SPEARMAN(columns, colFilter, ciLevel, alternative)

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):

  1. By name - exact header text (case-sensitive)
    • "age,score,total"
  2. Negative rules - exclude by name (prefix with -)
    • "-id,-notes" (include all except id and notes)
  3. 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.
  4. 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.

ciLevel — optional, default 0.95

Confidence level for intervals in tests/bootstraps: a number strictly between 0 and 1 (0.95, not 95). A number written as text ("0.9") is accepted.

Another value is an error: ciLevel must be between 0 and 1 (e.g. 0.95), got 95. Empty — the level from Settings.

Should be a number between 0 and 1

Examples:

  • 0.9
  • 0.95
  • 0.8
alternative — optional, default "two-sided"

Alternative hypothesis for two-sample/one-sample tests and correlations: "two-sided", "greater" or "less"; 1 and -1 (number or text) mean greater and less.

Case and spaces around do not matter ("Greater", " less "). Another value is an error: alternative must be two-sided, greater or less (or 1, -1), got "grater". Empty — the default from Settings.

A one-sided hypothesis gives a one-sided interval: [lower, ∞) for greater, (−∞, upper] for less (for correlations [lower, 1] and [−1, upper]).

Allowed values: "two-sided", "greater", "less"

Examples:

  • "two-sided"
  • "greater"
  • "less"

Examples

  • — the sample data (50 rows) all examples use
  • — Returns correlation matrix =STATS_SPEARMAN(Data!A1:T51, "q1,q2,q3",0.95,"two-sided")
  • — =STATS_SPEARMAN(Data!A1:T51, "x,y",0.95,"two-sided")
  • — Error - only one column =STATS_SPEARMAN(Data!A1:T51, "x",0.95,"two-sided")

What the result contains

Flat 2D report that spills below the formula cell.

For 2 columns: a Spearman rho with CI and p-value; for 3+ columns: a symmetric correlation matrix with labels.

A column without spread, or fewer than two common rows: rho, p and the interval are empty.

Each value needs its own number of common rows, below it the cell is empty: rho from 2, t and p from 3, the interval from 4. The block keeps its layout.

How to read the result

Start with rho (the row r in the report): its sign is the direction, its size the strength — "as one goes up, does the other go up too?" (0.1 small, 0.3 medium, 0.5 large). In the text call it rs or ρ, not r, so the reader knows it is Spearman.

Then p: below 0.05 — the relation is unlikely to be chance; in a matrix the stars say the same.

Prefer it to Pearson for rating scales, ranks and skewed data or outliers.

In a report: "Satisfaction and loyalty were related, rs(48) = .42, p = .002."

What each number means

Significance: *<0.05,<0.01,*<0.001 — Significance stars

What the stars in the matrix mean: * p < 0.05, p < 0.01, * p < 0.001. Text columns left out of the matrix are named after it.

r — Spearman’s rho

For ranks, rating scales and skewed data: it asks whether one goes up as the other goes up (a monotonic relation), not whether they follow a straight line. Read it as r: 0.1 small, 0.3 medium, 0.5 large.

The cell says r, but it is Spearman's rho: in the text write rs or ρ, so the reader knows which correlation it is. In a report: rs(48) = .14, p = .326.

t — t of the correlation

A step on the way to p; for a correlation it is not usually reported.

df — Degrees of freedom of the correlation

Written in brackets after r in a report: r(48) = .15.

p — p of rho

Below 0.05 — the relation is unlikely to be chance. On fewer than about 10 rows, or with many tied values, the approximation is rough.

Details of every pair

Pair — Pair of columns

Each row of this table is one cell of the matrix above, with its details. For a correlation the order of the two columns does not matter.

df — Degrees of freedom of the correlation

Written in brackets after r in a report: r(48) = .15.

r — Spearman’s rho

For ranks, rating scales and skewed data: it asks whether one goes up as the other goes up (a monotonic relation), not whether they follow a straight line. Read it as r: 0.1 small, 0.3 medium, 0.5 large.

The cell says r, but it is Spearman's rho: in the text write rs or ρ, so the reader knows which correlation it is. In a report: rs(48) = .14, p = .326.

p — p of rho

Below 0.05 — the relation is unlikely to be chance. On fewer than about 10 rows, or with many tied values, the approximation is rough.

CI 0.95 Lower — Lower bound for rho

Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.

CI 0.95 Upper — Upper bound for rho

Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.

Confidence interval

Level — Confidence level

0.95 means that in 95 samples of 100 an interval built this way would contain the true value.

Lower — Lower bound for rho

Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.

Upper — Upper bound for rho

Together with the other bound: a range of plausible values for the true coefficient. If the interval contains 0, the relation may be nil.

How it is computed

Two columns: the rows where both are filled. Three or more: a matrix, each pair on its own common rows; text columns are left out of a matrix.

Each column is ranked, tied values getting the average rank; rho is the Pearson correlation of the ranks.

The test of rho = 0: t = rho · √((n − 2) / (1 − rho²)) with n − 2 degrees of freedom, as SPSS NONPAR CORR — no permutations, so the p is the same on every recalculation.

The confidence interval: the Fisher z transform with the variance 1.06 / (n − 3) (Fieller, Hartley and Pearson), as SPSS.

Errors

Fewer than two columns: one cell Spearman requires at least 2 columns, got: x; when text columns were skipped, it names them (. Text columns skipped: t, u …). Only text columns: the message about text columns.

A column that does not exist: "Column nope does not exist".

Regex is case-sensitive and has no flags.

About STATS_SPEARMAN

STATS_SPEARMAN computes Spearman's rank correlation, rho — the correlation for rating scales, ranks and skewed data: does one go up as the other goes up? Each column is ranked, tied values get the average rank, and rho is the Pearson correlation of the ranks. Two columns give rho with its p-value and confidence interval; three or more give a correlation matrix. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.

The p-value follows SPSS NONPAR CORR: t = rho · √((n − 2) / (1 − rho²)) with n − 2 degrees of freedom. It uses no permutations, so it is the same on every recalculation; below about 10 rows, or with many ties, it is approximate. The confidence interval uses the Fisher transform with the variance 1.06 / (n − 3) of Fieller, Hartley and Pearson, as SPSS — slightly wider than a Pearson-style interval.

Each pair uses its own common rows; text columns are left out of a matrix. With too few rows a cell stays empty: rho needs 2, p needs 3, the interval needs 4. Every number of the report is explained on this page in plain words.