STATS_CHI2 – Pearson chi-square test in Google Sheets™
What it does
Pearson chi-square test for association between two categorical columns.
Expected counts and p-values are calculated from the observed contingency table.
Rows and columns of the tables follow the labels of the columns when they have them; otherwise numbers ascending, text alphabetically (as SPSS).
Syntax
=STATS_CHI2(columns, rowCol, colCol)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 columnsA1:C200— use 3 columnsA:F— use 6 columnsA1: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.
rowCol — required
Name of the categorical column used for table rows.
Examples:
gendergroupmood
colCol — required
Name of the categorical column used for table columns.
Examples:
groupgendermood
Examples
- — the sample data (50 rows) all examples use
- —
=STATS_CHI2(Data!A1:T51, "gender", "group")
What the result contains
Returns result, observed counts, expected counts, and warnings when relevant.
The result table includes chi2, df, p, N, row count, and column count.
How to read the result
Start with p: below 0.05 — the two variables are related.
Check Warnings: with many expected counts below 5 the p is unreliable — merge rare categories into one (recode them with STATS_RECODE) and run the test again.
Compare Observed with Expected counts to see where the relation lies: the cells furthest from their expected count.
For the strength of the relation use STATS_CRAMER. In a report: "Gender and group were not significantly related, χ²(2, N = 50) = 0.31, p = .859."
What each number means
chi2— Pearson chi-squareThe larger, the stronger the evidence of a relation; read p. In a report: χ²(2, N = 50) = 0.31, p = .859.
df— Degrees of freedom of the chi-squareWritten in brackets: χ²(2, N = 50) = 0.31.
p— p of the chi-squareBelow 0.05 — the two variables are related: the answers to one depend on the answer to the other. It does not say how strongly — Cramér's V does. With warnings below, treat p with care.
N— NReport it with the test: χ²(2, N = 50) = 0.31.
Rows— RowsThe size of the table as shown. The chi-square counts only categories that have rows: df = (Rows − 1)(Columns − 1) over those.
Columns— ColumnsThe size of the table as shown. The chi-square counts only categories that have rows: df = (Rows − 1)(Columns − 1) over those.
Observed counts
gender \ group— Rows \ columnsRead a row across to see how the answers to the second question split within one answer to the first. The counts table has counts only; with details = TRUE the report adds row and column percentages and adjusted residuals below the summary.
Total— TotalDivide a cell by its row total for the row percentage.
Expected counts
gender \ group— Rows \ columnsRead a row across to see how the answers to the second question split within one answer to the first. The counts table has counts only; with details = TRUE the report adds row and column percentages and adjusted residuals below the summary.
Expected counts include only non-empty row and column categories used in the chi-square calculation.— Expected counts noteCategories without a single row are left out of the expected counts and of the test, so this table can be smaller than the observed one.
How it is computed
The crosstab of the two columns (as STATS_CROSSTABS); categories without rows are left out.
Expected counts E = row total · column total / N; Pearson's χ² = Σ (O − E)² / E, without continuity correction; df = (Rows − 1)(Columns − 1); p from the chi-square distribution.
Warnings as SPSS: the number of expected counts below 5 and below 1.
Errors
Unknown columns or degenerate categories return a one-cell message or warnings.
Rows with missing values in either selected column are excluded pairwise.
About STATS_CHI2
STATS_CHI2 runs Pearson's chi-square test: it checks whether two categorical columns are associated, for example whether the answer to a question depends on gender or group. Name the column for the table rows (rowCol) and the column for the table columns (colCol). The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.
From the observed counts the function computes the expected counts, what each cell would hold if the two columns were unrelated, and the chi-square statistic, without continuity correction. The result table gives chi2, the degrees of freedom (df), the p-value, N and the numbers of rows and columns. Rows with a missing value in either column are left out.
Rows and columns of the tables follow the value labels of the columns when they have them, such as male:1,female:2 in a labels row above the column names; otherwise numbers go in ascending order and text alphabetically, as in SPSS. As SPSS does, the report warns how many expected counts are below 5 and below 1. For the strength of the association, use STATS_CRAMER. Every number of the report is explained on this page in plain words.