STATS_CROSSTABS – observed contingency tables in Google Sheets™
What it does
Observed contingency table for a pair of categorical columns.
Builds row totals, column totals, grand total, and excludes rows with missing values pairwise.
Syntax
=STATS_CROSSTABS(columns, rowCol, colCol, details)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
details — optional
TRUE to add three tables after the summary: row percentages, column percentages and adjusted residuals (SPSS CROSSTABS: % within row, % within column, adjusted standardized residuals).
TRUE, 1 or their text turn it on; FALSE, 0, their text or empty turn it off. Anything else is an error: details must be TRUE or FALSE (or 1, 0), got "yes".
Off by default: the report stays as it was. On: the block is taller by the three tables, and the counts table and the summary stay where they were.
Examples:
TRUE— Add the percentages and residualsFALSE
Examples
- — the sample data (50 rows) all examples use
- —
=STATS_CROSSTABS(Data!A1:T51, "gender", "group") - — with row and column percentages and adjusted residuals
=STATS_CROSSTABS(Data!A1:T51, "gender", "group", TRUE)
What the result contains
Returns a flat report with observed counts and a crosstabs summary.
With details = TRUE, three more tables follow the summary: Row percentages, Column percentages and Adjusted residuals (as SPSS CROSSTABS /CELLS ROW COLUMN ASRESID). Off by default, so the report stays as it was.
Categories follow the labels of the columns (a labels row above the names) when they have them; otherwise the order of SPSS: numbers ascending, text alphabetically. A text scale with a natural order needs labels to keep it.
How to read the result
Look at N and Excluded first: how many rows the table rests on.
Read each row across: how the answers to the second question split within one answer to the first; compare with the row Total.
Whether the difference is more than chance, STATS_CHI2 tests; how strong it is, STATS_CRAMER measures.
In a report give row percentages: "Among women, 33% were in group 1 (10 of 30), among men 40% (8 of 20)." With details = TRUE the report computes them, and the column percentages too.
With details = TRUE, adjusted residuals beyond ±2 show the cells that make a significant STATS_CHI2: more rows than independence would give (positive) or fewer (negative).
What each number means
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.
Crosstabs summary
N— NReport it with the test: χ²(2, N = 50) = 0.31.
Excluded— Excluded rowsA large number means that many respondents skipped one of the two questions.
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.
Row percentages
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.
Column percentages
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.
Adjusted residuals
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.
How it is computed
Every row with an answer in both columns is counted in the cell of its pair of answers; rows with an empty answer in either column are excluded (pairwise), rows with "." are counted apart as not applicable.
Categories in the order of the labels, or numbers ascending and text alphabetically, as SPSS; row and column totals added.
Errors
Unknown columns or empty categories return a one-cell message.
Rows with missing values in either selected column are excluded pairwise.
About STATS_CROSSTABS
STATS_CROSSTABS builds a contingency table (a crosstab) of two categorical columns, for example gender by group. Every respondent is counted in the cell of their pair of answers, and the table adds row totals, column totals and a grand total. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.
Categories follow the value labels of the columns when they have them (a labels row such as "male:1,female:2" above the column names); otherwise numbers go ascending and text alphabetically, as in SPSS. A text scale with a natural order, such as education levels, needs labels to keep that order. A row with an empty answer in either column is left out; the sign "." is counted apart as not applicable.
With details = TRUE, three more tables follow: row percentages, column percentages and adjusted residuals, as SPSS CROSSTABS prints them with /CELLS ROW COLUMN ASRESID. An adjusted residual shows how far a cell is from the count expected if the two answers were unrelated. Every number of the report is explained on this page in plain words.