CHISQ.TEST: Chi‑Square Test for Independence & Goodness of Fit

Home » pub » Tutorials » CHISQ.TEST: Chi‑Square Test for Independence & Goodness of Fit

Master the CHISQ.TEST function in Excel to determine whether observed data differs significantly from expected results. This tutorial explains the CHISQ.TEST syntax, required inputs, practical examples, interpretation of p-values, common errors, and best practices. You’ll also learn how to use the function for goodness-of-fit tests, tests of independence, contingency tables, hypothesis testing, and statistical data analysis. Ideal for students, researchers, analysts, statisticians, finance professionals, and Excel users working with categorical data and inferential statistics.

Does a customer preference depend on region? Does a die roll fairly? Do survey answers link to age group? These are questions about categories, not averages. A t-test or F-test cannot answer them. You need a method built for counts and frequencies. The chi-square test is that method, and Excel packs it into one tidy function. CHISQ.TEST compares the counts you observed against the counts you would expect by chance, then returns a p-value that settles the question.

This guide explains the two jobs chi-square does: testing independence and testing goodness of fit. First, it shows how the test works. Then it walks through building expected counts. Seven examples and a full troubleshooting section make it practical. By the end, you will run both kinds of test in Excel with one simple function. You will also know how to check that the result is valid.

How the Chi-Square Test Works

The chi-square test compares two sets of counts. One set is what you actually observed. The other is what you would expect if nothing special were happening. In effect, the test measures the gap between them. Small gaps suggest chance alone. Large gaps suggest a real effect. The infographic below shows the whole idea.

Together, the statistic adds up the squared gaps, scaled by the expected counts. A big total means the observed data strays far from expectation. Consequently, the p-value shrinks. A small p-value, usually below 0.05, signals a genuine pattern rather than random noise.

Two Jobs: Independence and Goodness of Fit

Chi-square handles two related questions. The first asks whether two category variables are linked. The second asks whether one set of counts matches an expected pattern. CHISQ.TEST serves both. The difference lies in how you build the expected counts.

The two tests compared: TEST OF INDEPENDENCE (a contingency table) Question: are two variables related? Example: region x product choice. Expected = (row total x column total) / grand total. df = (rows - 1) x (columns - 1). GOODNESS OF FIT (one row vs a claim) Question: do counts match an expected distribution? Example: is a die fair (each face equally likely)? Expected = total / number of categories (if uniform). df = categories - 1.

The Syntax

CHISQ.TEST takes two ranges. One holds observed counts. The other holds expected counts. It returns a p-value directly, so you skip the manual statistic.

Syntax: =CHISQ.TEST(actual_range, expected_range) actual_range -> your observed counts. expected_range -> the counts expected under "no effect". Returns: a p-value. Related functions: CHISQ.DIST.RT(x, df) -> right-tail p from a statistic. CHISQ.INV.RT(prob, df) -> the critical chi-square value.

CHISQ.TEST does the heavy lifting. It computes the statistic, works out the degrees of freedom from the table shape, and returns the p-value in one step. You only need the observed and expected ranges.

Example 1: Test of Independence

Start with the classic case. You want to know if product choice depends on region. You have a table of counts. First you build the expected counts. Then CHISQ.TEST returns the p-value.

Lay out the observed counts as a table, with regions in rows and products in columns.

Add row totals, column totals, and a grand total around the table.

Build each expected cell: =(row_total*column_total)/grand_total.

Run =CHISQ.TEST(observed_range, expected_range) for the p-value.

Decision: A p-value below 0.05 means region and product choice are linked. A larger p-value means they appear independent.

Example 2: Build the Expected Table

Indeed, the expected counts are the heart of the test. Each one assumes the two variables are unrelated. You get it from the row and column totals. This step is worth seeing in full.

Expected counts from totals: Observed table with totals: ProdA ProdB | Row total North 30 10 | 40 South 20 40 | 60 ---------------------------------- Col tot 50 50 | 100 (grand) Expected for North/ProdA: = (row 40 x col 50) / grand 100 = 20 Repeat for every cell. The expected table becomes: North: 20 20 South: 30 30

Example 3: Goodness of Fit for a Fair Die

Now switch to the second job. Suppose you rolled a die 60 times. A fair die should give each face 10 times. Do your counts fit that claim? Goodness of fit answers this. It is the standard way to test a claimed distribution against real data.

Is the die fair? Observed rolls (faces 1 to 6): 12 8 11 9 14 6 Expected if fair (60 / 6 each): 10 10 10 10 10 10 =CHISQ.TEST(observed_range, expected_range) A p-value above 0.05 means the die looks fair. A p-value below 0.05 means the die is likely biased. Here the small gaps give a high p-value, so the die passes.

Example 4: Compute the Statistic Manually

Sometimes you want the chi-square statistic itself, not just the p-value. This helps you compare against a critical value. The manual formula sums the scaled squared gaps. It is easy to build with SUMPRODUCT.

The statistic and its p-value: Chi-square statistic: =SUMPRODUCT((observed - expected)^2 / expected) This sums (O - E)^2 / E across every cell. Then get the p-value from the statistic: =CHISQ.DIST.RT(statistic, df) For a 2x2 table, df = (2-1)*(2-1) = 1. The result should match CHISQ.TEST exactly.

Example 5: Find the Critical Value

Similarly, a critical value gives you a fixed threshold. You compare the statistic against it. CHISQ.INV.RT returns the value for a chosen significance level. This mirrors the critical-value approach from other tests.

Critical chi-square at 5%: For alpha = 0.05 with df = 1: =CHISQ.INV.RT(0.05, 1) Result: about 3.841. How to use it: statistic > 3.841 -> significant, reject independence. statistic < 3.841 -> not significant, keep independence. For df = 2 the threshold rises to about 5.991.

Example 6: A Larger Survey Table

In practice, real surveys often use bigger tables. Suppose you cross three age groups with four answer choices. The method does not change. You still build expected counts and run CHISQ.TEST. Only the degrees of freedom grow.

Table shape

df formula

df value

2 x 2

(2-1)(2-1)

3 x 4

(3-1)(4-1)

A 3-by-4 table has 6 degrees of freedom. CHISQ.TEST works this out for you automatically. Therefore, the same one-step formula handles tables of any size. You never adjust the formula as the table grows. Only the expected counts and the range selection change.

Example 7: Check the Expected-Count Rule

Chi-square has one important assumption. Expected counts should not be too small. A common rule asks for at least 5 in most cells. A quick check flags any cells that break it. This keeps your test valid.

Flag low expected counts: =IF(MIN(expected_range) < 5, "Warning - some expected counts are below 5", "OK - expected counts are large enough") Why it matters: Very small expected counts make the test unreliable. If many cells fall below 5, combine categories or collect more data before trusting the p-value. This guard keeps you from over-reading a fragile result.

Troubleshooting CHISQ.TEST

All three problems below are the most common. Each has a clear cause and a quick fix.

You get a #N/A error

A #N/A error usually means the observed and expected ranges are different shapes. CHISQ.TEST needs both ranges to have the same number of rows and columns. Check that each range covers exactly the same grid. A common slip is including a totals row or column in one range but not the other. Select only the count cells, with no totals, for both ranges. Once the two ranges match in size and position, the function returns a proper p-value instead of the error.

You get a #DIV/0! error

This error appears when an expected count is zero. The chi-square statistic divides by each expected value, so a zero breaks the calculation. An expected count of zero usually means a row total or column total was zero. Check your totals for any empty category. If a category truly has no data, remove it or merge it with another. Rebuilding the expected table without zero totals fixes the division. Every expected cell must hold a positive number for the test to run.

The p-value looks wrong or too extreme

A surprising p-value often traces to the expected counts, not the function. If you built expected values incorrectly, the whole test shifts. Recheck each expected cell against the row-times-column over grand-total rule. Also confirm you compared observed against expected, not observed against observed. Another cause is very small expected counts, which can distort the result. When several cells fall below 5, the p-value becomes unreliable. Combining sparse categories or gathering more data gives a more trustworthy answer.

Frequently Asked Questions

What does the CHISQ.TEST function do in Excel?+

CHISQ.TEST compares a table of observed counts against a table of expected counts and returns a p-value. Specifically, it computes the chi-square statistic internally, works out the degrees of freedom from the table shape, and reports the probability in one step. Notably, you write it as =CHISQ.TEST(actual_range, expected_range), where both ranges must be the same size. A resulting p-value below 0.05 usually means the observed data differs significantly from expectation, signalling either a relationship between variables in a test of independence, or a poor match in a goodness-of-fit test. In short, it saves you from building the statistic by hand.
What is the difference between a test of independence and goodness of fit?+

Both use the chi-square method but answer different questions. A test of independence uses a two-way contingency table and asks whether two category variables are related, such as region and product choice. Its expected counts come from the row and column totals. By contrast, a goodness-of-fit test uses a single row of counts and asks whether they match a claimed distribution, such as whether a die is fair. Instead, its expected counts come from the assumed pattern, often a uniform split. Furthermore, the degrees of freedom differ: independence uses (rows minus one) times (columns minus one), while goodness of fit uses categories minus one.
How do I calculate expected counts for a chi-square test?+

For a test of independence, each expected count is the row total multiplied by the column total, divided by the grand total. In Excel, that is =(row_total*column_total)/grand_total for each cell. Essentially, this gives the count you would expect if the two variables were unrelated. For a goodness-of-fit test with an assumed uniform distribution, each expected count is simply the overall total divided by the number of categories. Therefore, build the full expected table first, then pass both the observed and expected ranges to CHISQ.TEST. Accurate expected counts are essential, because the entire test depends on them.
Why should expected counts be at least 5?+

In practice, the chi-square test relies on an approximation that works well only when expected counts are reasonably large. Generally, a widely used guideline says most cells should have an expected count of at least 5. However, when expected counts are very small, the approximation breaks down and the p-value becomes unreliable, sometimes appearing more extreme than it should. For example, if several cells fall below 5, you can combine related categories to raise the counts, or collect more data. For very small tables, an alternative such as the Fisher exact test may be more appropriate. Checking this assumption keeps your conclusions trustworthy.