How to do a chi-square test in Google Sheets

When to use it

The chi-square test of independence checks whether two categorical variables are related: course format and pass/fail, region and product choice, age group and yes/no answers. Each person must be counted once, in one cell only.

The example data

190 students took the same course online, in a hybrid format or in person. Did the pass rate depend on the format? Enter the counts like this:

Row totals in D2:D4 are =SUM(B2:C2) and so on; column totals in B5:D5 are =SUM(B2:B4).

Step 1: the expected counts

If format and outcome were unrelated, each cell would hold row total × column total ÷ grand total. In F2, type:

=$D2*B$5/$D$5

Fill it across to G2 and down to G4. The dollar signs keep the totals fixed while the formula moves.

All expected counts are above 5, so the test is valid.

Step 2: the p-value

=CHISQ.TEST(B2:C4, F2:G4)

Result: p = 0.017. The pass rate depends on the course format (at the 0.05 level). Do not include the totals in the ranges.

Step 3: the χ² statistic and degrees of freedom

=SUMPRODUCT((B2:C4-F2:G4)^2/F2:G4)

Result: χ² = 8.14. The degrees of freedom are (rows − 1) × (columns − 1) = 2 × 1 = 2. You can check: =CHISQ.DIST.RT(8.14, 2) returns the same p-value.

Step 4: the effect size (Cramér's V)

=SQRT(8.14/(190*(MIN(3,2)-1)))

Result: V = 0.21, a small-to-medium association. In the formula, 3 and 2 are the number of rows and columns of the observed table.

Step 5: which cells drive the result?

A significant test says there is an association, not where. Standardized residuals, (observed − expected) / SQRT(expected), show which cells are far from what was expected. Values close to or beyond ±2 stand out:

The largest gap is online students who failed: 22 observed against 14.5 expected. Hybrid students failed less often than expected.

Assumptions and alternatives

• Use counts, never percentages or means.
• Each person appears in exactly one cell. For the same people answering twice (before/after), use McNemar's test instead.
• Expected counts should all be at least 5. For a 2 × 2 table with small counts, use Fisher's exact test (ExplainStats Pro includes it).

How to report it (APA style)

Frequently asked questions

What does CHISQ.TEST return?

The p-value only. It needs two ranges of the same size: the observed counts and the expected counts. To get the χ² value itself, use a SUMPRODUCT formula or CHISQ.INV.RT(p, df).

What is the minimum expected count for a chi-square test?

The usual rule is that all expected counts should be at least 5. For a 2 × 2 table with small counts, use Fisher's exact test instead.

How do I do a chi-square goodness-of-fit test?

Put the observed counts in one column and the expected counts (total × expected proportion) in another, then =CHISQ.TEST(observed, expected). The degrees of freedom are the number of categories minus 1.

Can I use percentages instead of counts?

No. The chi-square test must be run on raw counts. Percentages give a wrong χ² value and a wrong p-value.