Chi-square test in Excel: test of independence, step by step

What the test asks

The null hypothesis H₀ says the two variables are independent: knowing whether a customer lives in a city, a suburb or a rural area tells you nothing about the delivery option they prefer. If that were true, every area would show the same split as the total, 210 ÷ 380 = 55.3% home delivery, 22.4% lockers and 22.4% pickup. The test measures how far the observed table is from that pattern and how likely a gap that large would be by chance alone.

Use it for counts of independent observations, one answer per person, sorted into the categories of two variables. It does not work on percentages, averages or repeated answers from the same people.

Worked example: delivery preference by area, 380 survey answers

Rows 2 to 4 are the observed counts, rows 8 to 10 the expected counts, rows 13 to 15 each cell's contribution to χ². Rows 17 to 24 are the test. The survey is fictional.

Worked example: delivery preference by area, 380 survey answers

	A	B	C	D	E
1	Observed	Home	Locker	Pickup	Total
2	City	60	45	25	130
3	Suburb	80	30	30	140
4	Rural	70	10	30	110
5	Total	210	85	85	380
6
7	Expected	Home	Locker	Pickup	Total
8	City	71.8	29.1	29.1	130
9	Suburb	77.4	31.3	31.3	140
10	Rural	60.8	24.6	24.6	110
11
12	(O − E)² ÷ E	Home	Locker	Pickup	Total
13	City	1.952	8.717	0.572	11.241
14	Suburb	0.090	0.055	0.055	0.200
15	Rural	1.396	8.669	1.183	11.248
16
17	Chi-square statistic χ²				22.69
18	Same in one formula (SUMPRODUCT)				22.69
19	Degrees of freedom (r − 1)(c − 1)				4
20	p-value (CHISQ.DIST.RT)				0.0001
21	p-value (CHISQ.TEST)				0.0001
22	Critical value at 5% (CHISQ.INV.RT)				9.49
23	Smallest expected count (should be ≥ 5)				24.6
24	Cramér's V				0.173

The p-value of 0.000146 is shown rounded to four decimals.

Step 1: the cross-tab of observed counts

Put one variable in the rows and the other in the columns, with row and column totals. From raw survey rows, one COUNTIFS builds every cell: =COUNTIFS($A:$A,$H2,$B:$B,I$1), with the area labels down column H and the options across row 1. Check that the grand total equals the number of answers, so no row was lost to a typo. The 380 answers in the example come from a fictional customer survey of an online shop.

Step 2: expected counts

The expected count of a cell is what independence predicts: =$E2*B$5/$E$5, row total × column total ÷ grand total. City customers are 130 of 380, and 85 of all 380 prefer lockers, so under H₀ about 130 × 85 ÷ 380 = 29.1 city customers should choose a locker. The dollar signs lock the total column and the total row, so one formula fills the whole expected table, and its row totals must match the observed ones.

Check the rule of thumb: every expected count should be at least 5. The smallest here is 24.6, comfortably above it. With small expected counts, combine categories or use Fisher's exact test.

Step 3: the chi-square statistic

Each cell contributes (O − E)² ÷ E. Squaring makes every gap positive, and dividing by E scales it to the size of the cell. The statistic is the sum of all nine contributions, =SUM(B13:D15), or in one formula =SUMPRODUCT((B2:D4-B8:D10)^2/B8:D10): χ² = 22.69.

The contribution table also shows where the association sits. Two cells carry 17.39 of the 22.69: city customers choose lockers more often than expected (45 vs 29.1) and rural customers much less often (10 vs 24.6). Suburban customers look almost exactly like the average. Dividing O − E by √E gives a standardised residual, about +2.95 and −2.94 for those two cells, and values beyond ±2 are worth mentioning in a report.

Step 4: degrees of freedom and the p-value

The degrees of freedom are (rows − 1) × (columns − 1) = 2 × 2 = 4. Large χ² values are evidence against independence, so the p-value is the right tail of the chi-square distribution: =CHISQ.DIST.RT(22.69,4) = 0.00015. That is far below 0.05, so you reject H₀. The same decision with a critical value: CHISQ.INV.RT(0.05,4) = 9.49, and 22.69 is well above it.

=CHISQ.TEST(observed,expected) returns the same p-value in one step, but only the p-value, and it needs the expected table that you built in step 2. Report the test with the statistic, the degrees of freedom and the sample size: χ²(4, N = 380) = 22.69, p < 0.001.

Effect size: Cramér's V

A p-value says whether there is an association, not how strong it is. With a large enough sample, a trivial difference becomes "significant". Cramér's V puts the strength on a 0 to 1 scale: =SQRT(χ² / (N × MIN(r − 1, c − 1))) = √(22.69 ÷ (380 × 2)) = 0.173. With two degrees of freedom in the smaller dimension, about 0.07 counts as small, 0.21 as medium and 0.35 as large in Cohen's rough benchmarks, so this is a small to medium association, concentrated in the locker column. For a 2 × 2 table, report the odds ratio and relative risk as well, because they say in which direction and by how much the groups differ.

Chi-square test of independence vs goodness of fit

Excel's functions serve two different tests. The test of independence, shown here, compares two variables in a cross-tab and builds the expected counts from the table's own totals. The goodness-of-fit test compares one variable with a distribution you specify in advance, for example whether support tickets are spread evenly across weekdays. It uses the same (O − E)² ÷ E sum and CHISQ.DIST.RT, but with k − 1 degrees of freedom for k categories. For paired yes/no answers from the same people, such as before and after a campaign, neither applies: use McNemar's test. For ranked or continuous data, a rank test such as the Mann-Whitney U test is the better tool.

Common mistakes

• Running the test on percentages. χ² must be calculated from counts. The same percentages from 38 or from 3,800 people give very different statistics.
• Using CHISQ.DIST instead of CHISQ.DIST.RT. CHISQ.DIST with TRUE returns the left tail, so a large χ² looks like a p-value close to 1. The test needs the right tail.
• Wrong degrees of freedom. Leave the total row and column out: a 3 × 3 table has (3 − 1)(3 − 1) = 4, not 8 or 9.
• Ignoring small expected counts. With expected counts below 5 in more than a fifth of the cells, the p-value is unreliable. Combine categories or use Fisher's exact test.
• Counting the same person twice. Multiple-answer questions or before-and-after answers break the independence assumption. Count one answer per person, or use McNemar for paired yes/no data.
• Reporting only the p-value. Add Cramér's V and say which cells drive the result, otherwise readers cannot tell a large effect from a large sample.