Spreadsheet How-To
How to Run a Chi-Square Test in Excel and Google Sheets
Enter the observed counts, build the expected counts as row total × column total ÷ grand total, and type =CHISQ.TEST(observed_range,expected_range). The result is the p-value. For the table 30, 20, 10 over 20, 30, 40 the expected counts are 20 and 30 in every column, the statistic is 16.6667 with 2 degrees of freedom and the p-value is 0.00024, so the two variables are associated.
Test of independence, step by step
Suppose 150 people were asked whether they prefer three versions of a product, and you want to know whether the preference depends on the group they belong to:
| Version A | Version B | Version C | Row total | |
|---|---|---|---|---|
| Group 1 | 30 | 20 | 10 | 60 |
| Group 2 | 20 | 30 | 40 | 90 |
| Column total | 50 | 50 | 50 | 150 |
- Type the six observed counts in B2:D3 and the totals in E2:E3, B4:D4 and E4.
- Build a second table of expected counts. In B7 enter
=$E2*B$4/$E$4and fill it across and down to D8. Every cell of the first row becomes 20 and every cell of the second row 30. - The p-value:
=CHISQ.TEST(B2:D3,B7:D8)returns 0.00024. - The statistic:
=SUMPRODUCT((B2:D3-B7:D8)^2/B7:D8)returns 16.6667. - The critical value at α = 0.05 with (2 − 1)(3 − 1) = 2 degrees of freedom:
=CHISQ.INV.RT(0.05,2)returns 5.9915.
Because 16.6667 is well above 5.9915 and the p-value is far below 0.05, we reject independence: the preference depends on the group. The chi-square independence calculator reproduces this table with the contributions of each cell.
Which cells drive the result, and how strong is it
| Cell contribution (O − E)² / E | Version A | Version B | Version C |
|---|---|---|---|
| Group 1 | 5.0000 | 0.0000 | 5.0000 |
| Group 2 | 3.3333 | 0.0000 | 3.3333 |
The contributions add up to the statistic, 16.6667. Groups differ on versions A and C, and not at all on version B. A small p-value says an association exists but not how strong it is; measure that with Cramér's V:
=SQRT(chi_square/(n*MIN(rows-1,columns-1)))
= √(16.6667 / (150 × 1)) = 0.3333
For a table with two rows or two columns, Cohen's rough guide calls a V near 0.1 small, near 0.3 medium and near 0.5 large, so this association is medium. The Cramér's V calculator gives V, the bias-corrected V and the benchmarks for a table of any size. Read more about this test in the chi-square test explained.
Goodness of fit
To check whether a die is fair, roll it 60 times and count each face: 5, 8, 9, 8, 10 and 20. A fair die expects 10 of each face.
=CHISQ.TEST(B2:G2,B3:G3) returns 0.0199
Statistic = 2.5 + 0.4 + 0.1 + 0.4 + 0 + 10 = 13.4 with 5 degrees of freedom
The p-value 0.0199 is below 0.05, driven mostly by the twenty sixes. Use the goodness-of-fit calculator to test other expected proportions.
Checks and common errors
- Expected counts of at least 5. Microsoft's help page for CHISQ.TEST notes that some statisticians suggest this minimum. For a small 2 × 2 table use Fisher's exact test.
- Counts, not percentages. The test needs the actual frequencies. Percentages make the statistic meaningless.
- Leave the totals out of the ranges. Only the cells of the table go into CHISQ.TEST.
- Independent observations. Each person or item should fall in exactly one cell. Repeated measurements on the same people need a different test.
- Google Sheets. CHISQ.TEST and its older name CHITEST work the same way. For the right-tail probability of a statistic use
CHIDIST(x,df)in place of CHISQ.DIST.RT.
Not sure a chi-square test is the right one? See which statistical test to use, and how to read a chi-square table for critical values.
Try the Chi-Square Test of Independence
Enter a contingency table and get the statistic, degrees of freedom, p-value and expected counts.
Frequently Asked Questions
What does CHISQ.TEST return?
Only the p-value, not the chi-square statistic and not the degrees of freedom. To report the statistic as well, calculate it with =SUMPRODUCT((observed-expected)^2/expected) over the two ranges. For the 2 × 3 example that gives 16.6667, and CHISQ.DIST.RT(16.6667,2) returns the same p-value as CHISQ.TEST.
How do I calculate expected counts in Excel?
For each cell multiply its row total by its column total and divide by the grand total: =$E2*B$4/$E$4 with row totals in column E and column totals in row 4. Fill it across and down. For a goodness-of-fit test the expected counts come from your hypothesis instead, for example the total divided equally among the categories.
Do I include the totals in the ranges?
No. The observed and expected ranges for CHISQ.TEST must cover only the cells of the table, without the row totals, the column totals or the headers. Including a totals row or column inflates the degrees of freedom and gives a wrong p-value with no error message.
What if some expected counts are below 5?
The chi-square approximation becomes unreliable. Microsoft's own documentation notes that some statisticians suggest each expected count should be at least 5. Merge sparse categories if that makes sense, or use Fisher's exact test, which does not rely on the approximation and is the standard choice for small 2 × 2 tables.
The p-value is significant. Which cells are responsible?
Look at each cell's contribution, (observed − expected)² / expected, which the statistic adds up. In the example table the cells in the first and third columns carry all of the 16.67, while the middle column contributes nothing because it matches the expected counts exactly.
Does the same function work for a goodness-of-fit test?
Yes. Give CHISQ.TEST a single row or column of observed counts and a matching row or column of expected counts, and it uses k − 1 degrees of freedom. For 60 die rolls with counts 5, 8, 9, 8, 10, 20 against 10 expected per face, the statistic is 13.4 with 5 degrees of freedom, and the p-value is 0.0199.