How-to
How to Do a Chi-Square Test in Excel (CHISQ.TEST)
In Excel, a chi-square test of independence takes three steps: build the observed counts with a PivotTable, compute the expected counts with the formula row total × column total / grand total, then enter =CHISQ.TEST(actual_range, expected_range) to get the p-value.
Reviewed by the crosstabs.com methods team · Last updated
Step 1 — Build the observed table with a PivotTable
Start from raw data where each row is one observation and you have two categorical columns — say, Plan and Renewed. Select the data and choose Insert → PivotTable. In the field list:
- Drag one variable (e.g., Plan) to Rows.
- Drag the other variable (e.g., Renewed) to Columns.
- Drag either variable to Values and make sure it shows Count (right-click → Summarize Values By → Count).
The PivotTable now shows your contingency table of observed counts. Copy just the inner counts — not the Grand Total row or column — and paste them as values into a clean range. CHISQ.TEST needs a plain rectangular range, and including totals would double-count every observation.
Step 2 — Compute the expected counts
CHISQ.TEST does not compute expected counts for you — this is the step most people miss. Add row totals to the right of your observed table, column totals below it, and a grand total. Then build a second table of the same shape where each cell is:
Expected count formula
E = (row total × column total) / grand total
- Excel
- = with observed counts in B2:C3 and totals in row 5 / column E, the top-left expected cell is =$E2*B$5/$E$5 — the mixed references let you fill the formula across the whole expected table
Sanity check: the expected table's row totals, column totals, and grand total should match the observed table's exactly. If any expected count is below 5 in a 2×2 table, the chi-square approximation is questionable — see what to do when expected counts are less than 5.
Step 3 — Run CHISQ.TEST
In any empty cell, enter:
where the first range is your observed counts and the second is your expected counts. Excel returns a single number: the p-value of the chi-square test of independence. If it is below 0.05, you reject the null hypothesis that the two variables are independent. (In Excel 2007 and earlier the same function is called CHITEST.)
Worked example
Observed table [[20, 30], [30, 20]], n = 100. All row and column totals are 50, so every expected count is (50 × 50) / 100 = 25.
=CHISQ.TEST({20,30;30,20}, {25,25;25,25}) returns 0.0455— just significant at the 0.05 level. The statistic behind it is χ² = 4.00 with df = 1, which you can confirm with =SUM((O−E)^2/E) over the two ranges.
What CHISQ.TEST doesn't give you
CHISQ.TEST returns only the p-value. It does not report the chi-square statistic, the degrees of freedom, or any effect size — all of which journals and instructors expect you to report. To fill the gaps:
- Test statistic: =SUM((B2:C3-B8:C9)^2/B8:C9) (in older Excel, confirm with Ctrl+Shift+Enter).
- Degrees of freedom:(rows − 1) × (columns − 1), computed by hand.
- Critical value: =CHISQ.INV.RT(0.05, df) gives the cutoff your statistic must exceed at α = 0.05 (3.841 for df = 1).
- Effect size:Cramér's V = √(χ² / (n × (min(r,c) − 1))) — Excel has no function for it, so build the formula yourself or use our Cramér's V calculator.
Excel also has no Fisher's exact test, which is what you actually need when a 2×2 table has small expected counts.
The faster way
The PivotTable–expected-table–CHISQ.TEST routine works, but it is five manual steps and an easy place to make a range-reference mistake. Our chi-square calculator does the whole thing in one step: upload the same XLSX file, pick your two columns, and get the crosstab with the chi-square statistic, degrees of freedom, p-value, expected counts, and Cramér's V together. Everything runs in your browser — the file never leaves your device. See also crosstabs from Excel files for the full workflow.
Frequently asked questions
- Does CHISQ.TEST give the chi-square statistic?
- No. CHISQ.TEST(actual_range, expected_range) returns only the p-value. To report the statistic itself, compute it with =SUM((observed-expected)^2/expected) entered over the two ranges, or recover it from the p-value with =CHISQ.INV.RT(p, df). Degrees of freedom are (rows−1)×(columns−1).
- What is the difference between CHISQ.TEST and CHITEST in Excel?
- They are the same function. CHITEST is the legacy name from Excel 2007 and earlier, kept for backward compatibility; CHISQ.TEST is the current name introduced in Excel 2010. Both take an actual range and an expected range and return the p-value.
- Why doesn't Excel compute the expected counts for me?
- CHISQ.TEST predates Excel's modern dynamic-array functions and was designed to compare any observed distribution against any expected one, so it requires you to supply the expected range yourself. For a test of independence you must build the expected table with the formula row total × column total / grand total before calling CHISQ.TEST.
- Does Excel's chi-square test apply Yates' continuity correction?
- No. CHISQ.TEST computes the uncorrected Pearson chi-square p-value, even for 2×2 tables. If your 2×2 table has small expected counts (below 5), the better remedy is Fisher's exact test, which Excel does not provide built in.