STATS_ALPHA – Cronbach’s Alpha reliability in Google Sheets™

What it does

Computes Cronbach’s Alpha — a measure of internal consistency (reliability) for a set of items, such as Likert-type questions.

Optionally includes Alpha if Item Deleted diagnostics to assess contribution of each item.

Optionally adds standardized alpha (based on standardized items, as in SPSS), which does not depend on the scale of each item.

Syntax

=STATS_ALPHA(columns, colFilter, ifItemsDeleted, commonPrefix, standardized)

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.

ifItemsDeleted — optional, default FALSE

TRUE to include 'Alpha if item deleted' diagnostics.

TRUE, 1 or the text "true"/"1" turn it on; FALSE, 0, their text or empty turn it off. Anything else is an error: ifItemsDeleted must be TRUE or FALSE (or 1, 0), got "no".

The table needs at least 3 items: with 2 items the report shows alpha and the line 'Alpha if item deleted needs at least 3 items' instead of the table.

Should be TRUE or FALSE

Examples:

TRUE — Add if item deleted for each item
FALSE

commonPrefix — optional

Optional title suffix for Cronbach Alpha report.

If provided, title becomes "Cronbach Alpha for <commonPrefix>".

If omitted, prefix is auto-detected from selected item names when possible.

Examples:

"Scale"
"Q"
"" — Use auto-detection / default title fallback

standardized — optional

TRUE to add standardized alpha (SPSS: Based on Standardized Items).

TRUE, 1 or their text turn it on; FALSE, 0, their text or empty turn it off. Anything else is an error: standardized must be TRUE or FALSE (or 1, 0), got "yes".

Computed from the mean correlation between items, so it does not depend on the scale of each item.

Adds two columns to the main table and one to the 'Alpha if item deleted' table. FALSE or empty: no standardized alpha.

Examples:

TRUE — Add standardized alpha
FALSE

Examples

— the sample data (50 rows) all examples use
— all columns starting with q; includes 'if item deleted' =STATS_ALPHA(Data!A1:T51, "/^q/", TRUE)
— =STATS_ALPHA(Data!A1:T51, "q1,q2,q3,q4")
— =STATS_ALPHA(Data!A1:T51, "-id,-age,/^q/")
— Error example (returns single-cell message) =STATS_ALPHA(Data!A1:T51, "q1")
— Two items: alpha only; the "if item deleted" table needs at least 3 =STATS_ALPHA(Data!A1:T51, "q1,q2", TRUE)
— Explicit report title suffix =STATS_ALPHA(Data!A1:T51, "/^q/", TRUE, "Scale")
— Standardized alpha next to alpha, and in the if item deleted table =STATS_ALPHA(Data!A1:T51, "/^q/", TRUE, "", TRUE)

What the result contains

Returns a flattened 2D report containing:

Title 'Cronbach Alpha for q (N = 48)': N is the number of rows left after the rows with an empty item were dropped;
Optional 'Alpha if item deleted' table if requested, with a 'corrected item-total correlation' column (from 3 items; with 2 items the line 'Alpha if item deleted needs at least 3 items' instead);
With standardized = TRUE: 'Alpha standardized' next to alpha (two more columns) and a standardized column in the 'Alpha if item deleted' table; keep the cells to the right of the block free.
Items without spread, or items whose row sum is constant: alpha is an empty cell (it is not defined). One constant item among varying ones still gives an alpha.
If fewer than 2 numeric items are found, returns a single-cell error message.

How to read the result

Start with Alpha: 0.7 is the usual minimum for a scale, 0.8 good, 0.9 excellent.

A low or negative alpha: first check the coding — an item worded the other way round must be reversed (STATS_RECODE with "reverse") if its wording asks for it. If no item is reversed and alpha stays low, the items measure different things: report the low alpha honestly and analyse them separately.

In the "if item deleted" table, an item whose deletion raises alpha above the scale's alpha weakens the scale; its corrected item-total correlation is usually low (below about 0.3) or negative.

Check N in the title: a row with any empty item is left out, so on a long scale N can be much smaller than the sample.

In a report: "The 7-item scale was reliable, Cronbach's α = .84."

What each number means

Alpha — Cronbach’s alpha

At most 1, and it can be negative. 0.7 is the usual minimum for a research scale, 0.8 good, 0.9 excellent (above 0.95 the items may repeat each other). A low or negative alpha means the items do not hang together: check the coding of reversed items, and whether the items measure one thing at all.

In a report: "The scale was reliable, α = .84 (7 items)."

Alpha if item deleted needs at least 3 items — Too few items

With two items, deleting one leaves a single item, whose alpha is not defined; only the alpha of the pair is shown.

Alpha standardized — Standardized alpha

It differs from alpha when the items have very different spreads; report it when the items are on different scales.

Alpha if item deleted

Variable — Variable: Read the numbers along this row or column for that variable.
alpha if deleted — Alpha if the item is deleted: Higher than the alpha of the whole scale — the scale is more reliable without this item: check whether it is reversed or off-topic.
corrected item-total correlation — Corrected item-total correlation: How well the item goes along with the rest of the scale. Below about 0.3 the item measures something else: check whether it is reversed or off-topic. Negative — almost always a reversed item.

How it is computed

The selected items are numeric columns; rows where any item is empty are left out (listwise), as SPSS RELIABILITY.

α = k / (k − 1) · (1 − Σ s²ᵢ / s²ₜ), sample variances of the items and of the row sums.

Standardized alpha (standardized = TRUE): k · r̄ / (1 + (k − 1) · r̄) from the mean correlation between the items.

Alpha if item deleted: the same alpha on the remaining items, for each item in turn; it needs at least 3 items. Next to it, the corrected item-total correlation: the correlation of the item with the sum of the other items.

Compared with SPSS RELIABILITY: alpha, N of cases (in the title), "Cronbach's Alpha if Item Deleted" and "Corrected Item-Total Correlation" — no N of items, no Scale Mean or Variance if Item Deleted.

Errors

If selected columns contain fewer than 2 numeric items, returns a single-cell message instead of a report.

For 'Alpha if item deleted' analysis, at least 3 numeric columns are required: with 2 the report shows alpha and a note, not an error.

Text items: 'Alpha needs numeric items, text columns: t'. A column that does not exist: 'Column nope does not exist'.

About STATS_ALPHA

STATS_ALPHA computes Cronbach’s alpha, a measure of internal consistency: how well a set of items, such as the Likert questions of one scale, measure the same thing. Select the item columns by name, by a pattern such as /^q/ (all columns starting with q) or by position. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.

An empty cell is a missing answer: rows where any item is empty are left out (listwise), as in SPSS RELIABILITY, and the title shows how many rows remain (N). Alpha needs at least two numeric items. With ifItemsDeleted = TRUE and three or more items, a table shows for each item the alpha of the scale without it and the corrected item-total correlation, the correlation of the item with the sum of the other items. It helps to find a question that weakens the scale.

With standardized = TRUE the report adds the standardized alpha (SPSS: Based on Standardized Items). It is computed from the mean correlation between the items, so it does not depend on the scale of each item. Every number of the report is explained on this page in plain words.