Chi-square in Excel sounds intimidating until you actually do it. Then it clicks. You're comparing two categorical variables — survey responses by age group, click rates by source, defect counts by shift — and you want to know if the pattern is real or just noise. Excel has two functions for this: the newer CHISQ.TEST and the legacy CHITEST. Both return a p-value. That's the number that tells you whether your data is hiding a relationship or just sitting there being random.

Here's the thing nobody mentions when they first show you the formula. Chi-square does not give you a chi-square statistic in Excel. Not directly. CHISQ.TEST jumps straight to the p-value. Which is fine — that's usually what you want — but if your professor or boss asks for the actual chi-square value, you'll need CHISQ.INV.RT or a manual sum across cells. We'll cover both paths.

This guide walks through the whole thing. Building a contingency table from raw data. Calculating expected frequencies using row and column totals. Plugging the ranges into CHISQ.TEST. Reading the p-value. And — the part most tutorials skip — knowing when chi-square is the wrong tool and you should be running something else entirely. By the end you'll be able to do this in any spreadsheet, on any version of Excel from 2010 forward, without looking it up again.

Before we touch any formula, get the data shape right. Chi-square tests independence between two categorical variables. That means you need a contingency table — rows are one variable's categories, columns are the other's, and each cell holds the observed count. Not percentages. Not averages. Counts. If you've got percentages, multiply them back to raw frequencies first or the math breaks.

Quick example. You surveyed 200 people: 100 men and 100 women. You asked if they prefer coffee, tea, or neither. That gives you a 2×3 grid — two rows for gender, three columns for drink preference. Each cell is the number of people who fall into that combination. Add row totals on the right. Add column totals at the bottom. The grand total in the corner should equal your sample size. If it doesn't, you've miscounted somewhere.

The reason this layout matters is that Excel's CHISQ.TEST needs two ranges: observed (your actual counts) and expected (what you'd see if the variables were independent). The expected range has the same dimensions as the observed range. You compute it cell by cell using a simple formula we'll get to next.

Expected frequencies are the bridge between raw data and the chi-square result. The formula for each cell is straightforward — multiply the row total by the column total, then divide by the grand total. That's it. In Excel, if your row total for "Men" is in D2 and your column total for "Coffee" is in B5 and your grand total is in D5, the expected value for the Men/Coffee cell is =D2*B5/D5. Lock those references with dollar signs so you can drag the formula across the whole expected table without breaking it.

One catch. Every expected cell needs to be at least 5 for the chi-square approximation to hold. If any expected value drops below 5, the test gets unreliable. You've got two options at that point — combine sparse categories until each expected count clears 5, or switch to Fisher's exact test (which Excel can't do natively, but R, Python, or even some online calculators handle it fine). Don't ignore this. People do, and it's the most common reason chi-square results get challenged in peer review.

With observed and expected tables built, the actual test is one cell. Type =CHISQ.TEST(observed_range, expected_range) and hit enter. Excel returns the p-value. If it's below 0.05, you reject the null hypothesis — the two variables are not independent, there's a real association. Above 0.05 and you don't have enough evidence to claim a relationship. That's the entire test.

CHITEST is the older version of the same function. Identical syntax, identical output. Microsoft kept it around for backward compatibility with files made before Excel 2010. If you open a legacy workbook and see CHITEST, leave it alone — it still works. For new files, use CHISQ.TEST. The dot in the name is Excel's signal that this is the current, supported version.

Neither function gives you the chi-square statistic itself. Just the p-value. To get the actual statistic, you need to sum (observed - expected)² / expected across every cell. Use SUMPRODUCT for an elegant one-liner: =SUMPRODUCT((observed-expected)^2/expected). That gives you the chi-square value, which you can then check against a critical value from CHISQ.INV.RT.

Degrees of freedom for chi-square independence is (rows − 1) × (columns − 1). For our 2×3 gender/drink example that's (2−1) × (3−1) = 2. You need this number to find the critical value. In Excel, =CHISQ.INV.RT(0.05, 2) returns roughly 5.99. If your computed chi-square statistic exceeds 5.99, the result is significant at the 5% level — same conclusion you'd reach from CHISQ.TEST's p-value being under 0.05. Both paths land in the same place; pick whichever your audience expects to see.

Some statistics courses still teach Yates' continuity correction for 2×2 tables. The correction subtracts 0.5 from the absolute difference between observed and expected before squaring. Excel does not apply Yates automatically and offers no built-in function for it. If you need Yates, build a helper column with =(ABS(observed-expected)-0.5)^2/expected and sum it manually. Most modern statisticians have stopped using Yates because it's overly conservative — but if your assignment requires it, you now know how.

Let's run a complete worked example. Imagine a small business tracked customer feedback across three store locations. They want to know if satisfaction (Satisfied / Neutral / Dissatisfied) depends on which store the customer visited. Here's the observed data laid out in cells B2:D4, with row labels in A2:A4 and column labels in B1:D1.

Store A had 50 satisfied, 20 neutral, 10 dissatisfied. Store B had 40, 30, 20. Store C had 30, 25, 25. Sum the rows in column E. Sum the columns in row 5. The grand total — 250 — goes in E5. Build the expected table in B7:D9 using the formula pattern above, anchoring row and column totals with $. Run =CHISQ.TEST(B2:D4, B7:D9) in any empty cell.

Excel returns approximately 0.0067. That's well below 0.05, so you'd conclude satisfaction is not independent of store — something about Store A's experience is different from B and C, and it's worth digging in.

Want the chi-square statistic too? =SUMPRODUCT((B2:D4-B7:D9)^2/B7:D9) gives roughly 14.16. Degrees of freedom is (3−1)×(3−1) = 4. The critical value from =CHISQ.INV.RT(0.05, 4) is about 9.49. Our 14.16 clears it easily — significant.

People confuse chi-square with other tests all the time. Here's the rule. Use chi-square when both variables are categorical — labels, not numbers. Gender, color, brand, yes/no. If one variable is continuous (age in years, income in dollars, score out of 100), chi-square is the wrong tool. You'd use a t-test, ANOVA, or regression instead, depending on the shape of your question.

There are two distinct chi-square tests, by the way. The one we've been doing is the test of independence — two variables, one sample, asking whether they're related. The other is the goodness-of-fit test, which compares observed counts against a single theoretical distribution (like checking if a die is fair). Same formula behind the scenes; different question being asked. Excel handles both with CHISQ.TEST as long as you build the expected values correctly for each case.

Errors creep in when people skip the data-cleaning step. Sort your raw data first. Make sure categories are spelled consistently — "Coffee" and "coffee" and "COFFEE" will be counted separately by PivotTables, which inflates your row count and skews the expected values. Use Excel's Find & Replace or the PROPER() function to standardize text before building the contingency table. Five minutes here saves you from a wrong answer later.

If your data is in long format — one row per respondent — use a PivotTable to build the contingency table in seconds. Drag one variable to Rows, the other to Columns, and any field to Values (set to Count). Excel does the cross-tabulation for you. Copy the PivotTable's values into a static range before running CHISQ.TEST, because the function doesn't always like PivotTable references directly.

A few advanced moves are worth knowing. For larger tables, build the expected matrix once and reuse it for multiple tests by changing only the observed range. If you're running chi-square across dozens of comparisons, write a small VBA function that wraps CHISQ.TEST and CHISQ.INV.RT together, returning both the p-value and the decision (significant / not significant) in one shot. Saves time on bulk analysis.

Reporting the result follows the same conventions as any published statistic. Quote the chi-square value, degrees of freedom, sample size, and p-value: χ²(4, N = 250) = 14.16, p = .007. Excel's CHISQ.TEST gives you the p-value directly; the chi-square statistic comes from your SUMPRODUCT cell; df you computed from the table dimensions; N is your grand total. Four numbers, every report.

Chi-square gets a lot of mileage in marketing, healthcare, education, manufacturing — anywhere you've got categorical outcomes and want to know if a treatment or segment makes a real difference. Excel makes it fast enough that there's no excuse to skip the test, eyeball the data, and call it a day. Two minutes of formula setup tells you whether the pattern you're seeing in the numbers is genuine or just chance.

One last note. Statistical significance is not the same as practical significance. A p-value of 0.001 with a tiny effect size in a sample of ten thousand might be statistically real but practically useless. Always pair chi-square with an effect-size measure — Cramér's V is the standard for tables larger than 2×2 — and a glance at the actual cell percentages to see what's driving the result. Excel will give you the math. You bring the judgment.

If you're new to Excel functions, the workflow above probably feels like a lot of steps. It gets faster. After two or three tests you'll have a template — observed table on top, expected table below, formulas pre-built — that you just paste new data into. Most analysts keep a single workbook with chi-square, t-test, and ANOVA templates ready to go. Build yours now. Future-you will thank present-you.

And if Excel ever starts feeling cramped — large datasets, lots of conditions, automation — the same logic ports cleanly to Python's scipy.stats.chi2_contingency or R's chisq.test(). The underlying statistic is identical. Excel is just one interface to the test. Get the concept right here and you can move to any tool when you outgrow the spreadsheet.

Pulling it all together — chi-square in Excel is three moves. Build the contingency table with raw counts. Compute expected values from row and column totals. Run CHISQ.TEST against the two ranges. The p-value tells you whether the variables are independent. CHISQ.INV.RT gives you the critical value if your professor wants the classical test. SUMPRODUCT recovers the chi-square statistic itself for reporting. Check expected counts ≥ 5, use Yates for small 2×2 tables if required, and remember chi-square only works for categorical data.

The functions haven't changed since Excel 2010, so anything you learn here works in every modern version including Excel for Mac, Excel Online, and Microsoft 365. The only difference between versions is the function name — older files use CHITEST, newer ones use CHISQ.TEST. Both return the same p-value from identical inputs. You're set.

Common follow-up question: what alpha should you use? The default of 0.05 (5% significance) is convention, not law. For exploratory work some analysts loosen to 0.10. For high-stakes decisions — clinical trials, regulatory filings — 0.01 is standard. Whatever you pick, declare it before running the test, not after seeing the p-value. Adjusting your alpha to match the result you wanted is called p-hacking and it'll get your paper retracted.

One more practical tip. If you're comparing many groups in a single dashboard — say chi-square across twenty different product categories — the chance of getting a false positive somewhere climbs fast. The Bonferroni correction is the easy fix: divide your alpha by the number of comparisons. Running twenty chi-square tests at the usual 0.05? Your effective threshold per test becomes 0.0025. Brutal but honest. Excel doesn't do this adjustment for you — you keep it in your head or note it in a hidden column.

Worth knowing the limits too. Chi-square treats every cell as independent — no nesting, no clustering, no repeated measures from the same person. If your data has structure (multiple responses per respondent, students within classes, etc.) chi-square will overstate significance. You'd want a mixed-effects logistic regression instead, which is well outside Excel territory. For genuinely independent observations across categorical variables, though, Excel's chi-square is solid and gets the job done.

Last thing — keep your raw data. Always. Even after you've built the contingency table and run the test, the underlying response-level data is what you go back to when reviewers ask questions or when you want to slice the analysis differently. Save the raw sheet, the contingency table, and the chi-square output as three separate tabs in the same workbook. Future you, or whoever inherits the file, will be able to reproduce or extend the work without guessing what you did.