Chi-square test in Excel

In short: you need two tables of the same size, the observed counts and the expected counts. The expected count for a cell is row total times column total divided by the grand total. =CHISQ.TEST(observed,expected) then returns the p-value. Below 0.05, the two variables are probably related.

Try it now

A newsletter went out with two subject lines to 200 people each. The expected counts are already filled in. Click the yellow cell and calculate the p-value.

Task A newsletter went out with two subject lines, 200 people each. The top table shows who clicked, the bottom one the expected counts if the subject line made no difference. In F2, use the chi-square test to calculate the p-value.

	A	B	C	D	E	F
1	Observed	Clicked	Not clicked
2	Subject A	48	152		p-value
3	Subject B	72	128
4
5	Expected	Clicked	Not clicked
6	Subject A	60	140
7	Subject B	60	140

How the chi-square test works

The test compares what you observed with what you would expect if the two variables had nothing to do with each other (the null hypothesis of independence). Overall, 120 of 400 people clicked, which is 30%. If the subject line made no difference, you would expect 60 clicks from each group of 200. You saw 48 and 72.

Step	Formula	In the example
Expected count per cell	row total × column total / grand total	200 × 120 / 400 = 60
Contribution per cell	(observed - expected)² / expected	(48 - 60)² / 60 = 2.4
Chi-square statistic	Sum of all contributions	6.86
Degrees of freedom	(rows - 1) × (columns - 1)	1
p-value	=CHISQ.TEST(B2:C3,B6:C7)	0.0088

CHISQ.TEST handles the last three steps at once but never shows the statistic itself. You can calculate it with =SUMPRODUCT((B2:C3-B6:C7)^2/B6:C7), or recover it from the p-value: =CHISQ.INV.RT(F2,1) also returns 6.86.

Interpreting the p-value

p = 0.0088 means: if the subject line made no difference, a deviation from the expected counts at least this large would occur in under 1% of cases. At a 5% significance level you reject independence. Subject B, with a 36% click rate, did better than subject A at 24%. As with the t-test, the p-value tells you nothing about how big the difference is or what caused it, so always report the click rates alongside it.

One more exercise: calculating expected frequencies

The neatest way to build the expected table is a single formula filled across the whole range. For that to work you need mixed references.

Task The top table shows which plan customers in two regions chose, with row and column totals. In B8, calculate the expected count for North and Basic: row total times column total divided by the grand total. The formula is then filled to D9 and must be right in every cell.

	A	B	C	D	E
1		Basic	Plus	Premium	Total
2	North	38	45	17	100
3	South	32	65	23	120
4	Total	70	110	40	220
5
6
7	Expected	Basic	Plus	Premium
8	North
9	South

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

In =$E2*B$4/$E$4 the column E stays fixed when the formula is filled to the right, and row 4 stays fixed when it is filled down. For this plan data =CHISQ.TEST(B2:D3,B8:D9) returns a p-value of 0.196 with 2 degrees of freedom. That is not significant: the differences between North and South could easily be chance. If your raw data are a long list, count them into a contingency table first with a PivotTable or COUNTIFS.

Assumptions

• Expected counts not too small: Microsoft notes that the test is most appropriate when the expected values are not too small. A common rule of thumb asks for at least 5 in every cell. Otherwise merge categories or use an exact test in a statistics package.
• Counts, not percentages: the test needs numbers of observations, not percentages or averages. 24% and 36% without the 200 recipients behind them give a meaningless result.
• Independent observations: each person may appear in one cell only. Someone who received both newsletters breaks the test.

For 2×2 tables some statistics packages apply Yates' continuity correction automatically. CHISQ.TEST does not. In the newsletter example the corrected p-value would be 0.012 instead of 0.0088, so do not be surprised if another tool gives a slightly different answer.

Goodness of fit: one row against an expectation

CHISQ.TEST also works with a single row or column, for example complaints per weekday against an even spread. The degrees of freedom are then the number of cells minus 1. The expected values come from your assumption rather than from totals, such as the total divided by 5 for five weekdays.

Common mistakes

Including the totals

The observed and expected ranges cover the data cells only, never the row or column totals.

Ranges of different size

If the two ranges do not hold the same number of cells, CHISQ.TEST returns #N/A.

Reading the result as the chi-square value

CHISQ.TEST returns the p-value, not the test statistic.

Chi-square test in German Excel

English	German
=CHISQ.TEST(B2:C3,B6:C7)	=CHIQU.TEST(B2:C3;B6:C7)
CHISQ.INV.RT, CHISQ.DIST.RT	CHIQU.INV.RE, CHIQU.VERT.RE
SUMPRODUCT	SUMMENPRODUKT

German Excel separates arguments with semicolons.

Which Excel versions have CHISQ.TEST?

Microsoft lists CHISQ.TEST as new in Excel 2010 and documents it for Microsoft 365, Excel 2024, 2021, 2019 and 2016, on Windows and Mac. The older CHITEST gives the same result and remains as a compatibility function. The Analysis ToolPak has no chi-square tool, so the formula is the way to go.

FAQ