What p-value threshold should I use to decide if my observed data significantly differs from expected?

The standard significance level in most fields is p < 0.05, meaning there is less than a 5% probability that the observed distribution occurred by random chance under your hypothesis. However, context matters—some industries use 0.01 or 0.10 depending on risk tolerance and domain conventions.

Can I use CHISQ.TEST to compare two sets of observed data without a pre-defined hypothesis?

Not directly. CHISQ.TEST requires an expected_range based on a hypothesis. If you want to compare two observed datasets, you must first define expected frequencies—either from one group's observed data, or from a theoretical distribution like uniform or normal.

What is the minimum sample size or expected frequency for CHISQ.TEST to be statistically valid?

Statistical guidelines recommend that expected frequencies be at least 5 in each category, with a total sample size of at least 20–30 observations. Smaller samples or very small expected frequencies can produce unreliable p-values and inflate Type I error rates.

How does CHISQ.TEST differ from PEARSON in Excel?

PEARSON measures correlation between two continuous numeric variables and returns a coefficient from -1 to 1; CHISQ.TEST tests independence of categorical frequencies and returns a p-value from 0 to 1. Use CHISQ.TEST for count data (like employees per department), and PEARSON for relationships between measurements (like salary vs. years of service).

CHISQ.TEST function

Q: Can I use CHISQ.TEST to compare two sets of observed data without a pre-defined hypothesis?

Not directly. CHISQ.TEST requires an expected_range based on a hypothesis. If you want to compare two observed datasets, you must first define expected frequencies—either from one group's observed data, or from a theoretical distribution like uniform or normal.

Q: What is the minimum sample size or expected frequency for CHISQ.TEST to be statistically valid?

Statistical guidelines recommend that expected frequencies be at least 5 in each category, with a total sample size of at least 20–30 observations. Smaller samples or very small expected frequencies can produce unreliable p-values and inflate Type I error rates.

Q: How does CHISQ.TEST differ from PEARSON in Excel?

PEARSON measures correlation between two continuous numeric variables and returns a coefficient from -1 to 1; CHISQ.TEST tests independence of categorical frequencies and returns a p-value from 0 to 1. Use CHISQ.TEST for count data (like employees per department), and PEARSON for relationships between measurements (like salary vs. years of service).

Returns the probability that observed and expected categorical values are independent, testing whether observed frequencies significantly differ from expected.

=CHISQ.TEST(actual_range, expected_range)

Generate a CHISQ.TEST formula

Describe what you need. The generator will reach for CHISQ.TEST where CHISQ.TEST is the right tool, and tell you when it is not.

Arguments

How CHISQ.TEST reads its arguments

Argument	Required	Description
actual_range	Required	Required. Array or range containing observed frequencies for each category. Must be numeric and match the structure and size of expected_range.
expected_range	Required	Required. Array or range containing expected frequencies under the null hypothesis. Must be the same size as actual_range and contain only positive numbers; zero or negative values trigger #NUM!.

Returns

A p-value between 0 and 1; values closer to 0 indicate a statistically significant difference between observed and expected frequencies.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Test if employee department distribution matches an even split hypothesis

Department	Observed Count	Expected Even Split
Sales	3	2
IT	2	2
HR	2	2
Finance	1	2

=CHISQ.TEST(B2:B5, C2:C5)

Result: 0.5776

The eight employees are distributed across four departments with observed counts 3, 2, 2, 1. Under a null hypothesis of equal distribution (2 employees per department), the chi-squared test returns a p-value of 0.5776. This high p-value (>0.05) indicates no statistically significant difference—the observed distribution is consistent with the expected even split.

2. Test if employment status matches a 75/25 active-to-inactive hypothesis

Status	Observed Count	Expected 75/25
Active	7	6
Inactive	1	2

=CHISQ.TEST(B7:B8, C7:C8)

Result: 0.3173

From the HR roster, 7 employees are active and 1 is inactive. A hypothesis predicts 75% active (6 employees) and 25% inactive (2 employees) across the total of 8. The p-value of 0.3173 suggests the observed status distribution does not significantly differ from this 75/25 expectation, so the hypothesis cannot be rejected.

3. Test if salary levels match a 50/50 split between below and above $67k

Salary Band	Observed Count	Expected 50/50
Below $67k	3	4
$67k and above	5	4

=CHISQ.TEST(B12:B13, C12:C13)

Result: 0.3473

Salary data shows 3 employees earning below $67k and 5 earning $67k or more. If you hypothesized a 50/50 distribution (4 each), the chi-squared test yields 0.3473. This p-value above 0.05 means the observed salary split is not significantly different from the expected even distribution—you fail to reject your hypothesis.

Common errors

Which CHISQ.TEST error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The expected_range contains a zero, negative number, or value so small that the chi-squared calculation becomes invalid. The formula internally divides by expected frequencies, making zero impossible.	Review your expected_range to ensure all values are positive and realistic. If a category should have zero employees expected, reconsider whether the hypothesis is well-formed or exclude that category entirely.
#VALUE!	The actual_range or expected_range contains text, blank cells treated as text, or error values (like #DIV/0!) instead of numeric data. CHISQ.TEST cannot perform statistical calculations on non-numeric inputs.	Check both ranges for text entries, leading apostrophes, or formula errors. Ensure all values in both ranges are numeric. Convert text-formatted numbers using VALUE() if needed.
#REF!	The actual_range or expected_range references cells that have been deleted or moved, breaking the formula's link to the data.	Re-enter the formula with corrected range references, pointing to valid cells containing your observed and expected frequencies. Verify the ranges exist before running CHISQ.TEST.

Tips and when to use something else

Interpret p-values: a p-value below 0.05 typically signals that observed and expected distributions differ significantly; above 0.05 suggests they are consistent and your hypothesis is not contradicted.
Use CHISQ.TEST for categorical data like department, employment status, or region. For continuous measurements like salary comparisons, use PEARSON or other correlation functions instead.
Always define your expected frequencies *before* collecting or observing the data. Designing expected values after seeing observed counts invalidates the statistical test and produces misleading results.
Chi-squared tests assume each observation is independent; if your HR roster contains repeated records or interdependent entries, CHISQ.TEST results will be unreliable.

Related functions

COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.COUNTIFSCOUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.

Frequently asked questions

What p-value threshold should I use to decide if my observed data significantly differs from expected?: The standard significance level in most fields is p < 0.05, meaning there is less than a 5% probability that the observed distribution occurred by random chance under your hypothesis. However, context matters—some industries use 0.01 or 0.10 depending on risk tolerance and domain conventions.
Can I use CHISQ.TEST to compare two sets of observed data without a pre-defined hypothesis?: Not directly. CHISQ.TEST requires an expected_range based on a hypothesis. If you want to compare two observed datasets, you must first define expected frequencies—either from one group's observed data, or from a theoretical distribution like uniform or normal.
What is the minimum sample size or expected frequency for CHISQ.TEST to be statistically valid?: Statistical guidelines recommend that expected frequencies be at least 5 in each category, with a total sample size of at least 20–30 observations. Smaller samples or very small expected frequencies can produce unreliable p-values and inflate Type I error rates.
How does CHISQ.TEST differ from PEARSON in Excel?: PEARSON measures correlation between two continuous numeric variables and returns a coefficient from -1 to 1; CHISQ.TEST tests independence of categorical frequencies and returns a p-value from 0 to 1. Use CHISQ.TEST for count data (like employees per department), and PEARSON for relationships between measurements (like salary vs. years of service).

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17