Skip to main content
v2026.11,986 entries · CC-BY 4.0

Fisher’s Exact Test in Excel: Step-by-Step Guide

Learn how to perform Fisher’s exact test in Excel using HYPGEOM.DIST and contingency table formulas for small sample categorical research data.

Written and maintained by CASRAI Editorial Board

Published

On this page

To run Fisher’s exact test in Microsoft Excel, use the hypergeometric distribution function HYPGEOM.DIST to calculate exact cell probabilities for a 2×2 contingency table, or run scipy.stats.fisher_exact through the native Python in Excel feature. Because Excel has no single-click Fisher test button, analysts calculate the probability of the observed contingency table and sum the probabilities of all equally or more extreme configurations to obtain the exact two-tailed p-value for small sample research data.

What Excel is used for in research

Microsoft Excel serves as a fundamental computational tool across academic laboratories, clinical research facilities, and institutional research offices. Investigators use Excel for data collection, experimental recording, variable transformation, and exploratory data analysis. In clinical trials and experimental biology, observational outcomes are commonly cross-tabulated into contingency tables to evaluate whether two categorical variables share an association.

When analyzing categorical data, investigators frequently turn to the asymptotic chi-square test of independence. However, Pearson’s chi-square test relies on a large-sample continuous distribution approximation that fails when sample sizes are small. Under Cochran’s criteria, the chi-square approximation becomes unreliable when any expected cell count is zero or when more than 20 percent of contingency cells have expected counts below 5. In pilot trials, rare disease registries, and specialized animal experiments where total sample size is small, exact tests are required.

Fisher’s exact test solves this limitation by calculating the exact hypergeometric probability of observing a given 2×2 contingency table under the null hypothesis of independence, conditional on fixed row and column marginal totals. Instead of approximating a theoretical chi-square curve, Fisher’s test computes the combinatorial probabilities of all possible cell allocations. While dedicated statistical suites provide single commands for this test, Excel users can achieve identical precision using built-in distribution functions.

A frequent error among researchers is confusing Excel’s built-in FISHER function with Fisher’s exact test. In Microsoft Excel, the formula =FISHER(x) computes the Fisher z-transformation of Pearson’s correlation coefficient, calculating 0.5 × ln((1 + x) / (1 – x)). It does not evaluate contingency tables. Applying =FISHER() to contingency cell counts generates calculation errors because correlation coefficients must fall between -1 and 1. Researchers must instead apply Excel statistics functions tailored to discrete probability distributions.

How to install or access Excel

Microsoft Excel is accessed through official desktop installations and web environments provided by Microsoft. Research personnel typically obtain Excel through enterprise or academic volume licensing, Microsoft 365 enterprise subscriptions, or standalone Office desktop releases on Windows and macOS.

Configuring Excel for statistical research involves verifying key computational features:

  1. Official Portal Access: Download and install the desktop software directly from Microsoft’s administration portal at office.com or microsoft.com. Desktop versions provide full processing capacity for array evaluations and external add-ins.
  2. Analysis ToolPak Activation: Excel includes a built-in statistical add-in. In Windows, navigate to File → Options → Add-ins (or Tools → Excel Add-ins on macOS). In the Manage drop-down list, select Excel Add-ins, click Go, select Analysis ToolPak, and click OK. The ToolPak provides regression, t-tests, and ANOVA, though it does not provide exact tests for contingency tables.
  3. Python in Excel Setup: In Microsoft 365 on Windows, the =PY() formula connects worksheets to a cloud-hosted Python container. This integration provides pre-installed scientific libraries, including NumPy, pandas, and SciPy, allowing researchers to run exact statistical functions without managing local Python installations.
  4. Third-Party Add-In Installation: For desktop installations lacking Python access, free add-ins such as the Real Statistics Resource Pack provide custom worksheet functions like =FISHERTEST() and =FISHER2x2().

With Excel configured, researchers can implement Fisher’s exact test via native worksheet formulas, dynamic arrays, or containerized Python scripts.

How to run Fisher’s exact test in Excel step by step

Performing Fisher’s exact test in Excel involves setting up a 2×2 contingency table, calculating marginal totals, determining individual table probabilities via HYPGEOM.DIST, and summing the probabilities of extreme configurations.

Step 1: Set up the 2×2 contingency table in the worksheet

Arrange your categorical counts into a 2×2 table with adjacent row and column sums:

  • Cell B2 (Count a): Treatment group, Success.
  • Cell C2 (Count b): Control group, Success.
  • Cell B3 (Count c): Treatment group, Failure.
  • Cell C3 (Count d): Control group, Failure.
  • Cell D2 (Row 1 Total, R₁): Formula =B2+C2 (sum of a + b).
  • Cell D3 (Row 2 Total, R₂): Formula =B3+C3 (sum of c + d).
  • Cell B4 (Column 1 Total, C₁): Formula =B2+B3 (sum of a + c).
  • Cell C4 (Column 2 Total, C₂): Formula =C2+C3 (sum of b + d).
  • Cell D4 (Grand Total, N): Formula =D2+D3 (sum of all observations).

Step 2: Map contingency parameters to HYPGEOM.DIST

In a 2×2 table where marginal totals are held constant, the count in cell a follows the hypergeometric distribution. Excel’s built-in HYPGEOM.DIST function takes five arguments:

=HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)

Map the cell references to the function arguments:

  • sample_s: The count in cell a (cell B2).
  • number_sample: The row total R₁ (cell D2).
  • population_s: The column total C₁ (cell B4).
  • number_pop: The grand total N (cell D4).
  • cumulative: Set to FALSE to calculate the probability of this specific configuration.

Entering =HYPGEOM.DIST(B2, D2, B4, D4, FALSE) computes the exact probability of observing this table.

Step 3: Identify the range of possible outcomes

Under fixed margins, cell a is constrained by non-negative cell requirements:

  • Minimum possible a: =MAX(0, D2 + B4 - D4), representing max(0, R₁ + C₁ – N).
  • Maximum possible a: =MIN(D2, B4), representing min(R₁, C₁).

To obtain an exact two-tailed p-value, calculate the hypergeometric probability for every integer value of a across this interval, and sum the probabilities of all outcomes that are as extreme as or more extreme than the observed data.

Step 4: Worked example with complete arithmetic

Consider a preclinical study comparing an active therapy to control across eight animals (N = 8):

  • Treatment: 3 responses (cell a), 1 non-response (cell c). Column total C₁ = 4.
  • Control: 0 responses (cell b), 4 non-responses (cell d). Column total C₂ = 4.
  • Row totals: Responses R₁ = 3; Non-responses R₂ = 5.
  • Grand total: N = 8.

The possible values for cell a range from max(0, 3 + 4 – 8) = 0 to min(3, 4) = 3. There are four possible configurations. The total number of ways to select 4 units from 8 is the combination 8 choose 4, equal to (8 × 7 × 6 × 5) / (4 × 3 × 2 × 1) = 70. The probability for each value of a is calculated using the formula [choose(3, a) × choose(5, 4 – a)] / 70:

  • Configuration a = 0: Cells [[0, 3], [4, 1]]. Probability = [choose(3, 0) × choose(5, 4)] / 70 = (1 × 5) / 70 = 5/70 ≈ 0.071429.
  • Configuration a = 1: Cells [[1, 2], [3, 2]]. Probability = [choose(3, 1) × choose(5, 3)] / 70 = (3 × 10) / 70 = 30/70 ≈ 0.428571.
  • Configuration a = 2: Cells [[2, 1], [2, 3]]. Probability = [choose(3, 2) × choose(5, 2)] / 70 = (3 × 10) / 70 = 30/70 ≈ 0.428571.
  • Configuration a = 3 (Observed): Cells [[3, 0], [1, 4]]. Probability = [choose(3, 3) × choose(5, 1)] / 70 = (1 × 5) / 70 = 5/70 ≈ 0.071429.

The sum of all four probabilities is 5/70 + 30/70 + 30/70 + 5/70 = 1.000000.

For a one-tailed test evaluating whether treatment increases response (a ≥ 3), the p-value is 5/70 ≈ 0.071429. In Excel, this can be calculated directly with =1 - HYPGEOM.DIST(2, 3, 4, 8, TRUE).

For a two-tailed test under Fisher’s criterion, sum the probabilities of all configurations whose probability is less than or equal to the observed table’s probability (P ≤ 0.071429):

  • P(a = 3) = 0.071429 (observed table).
  • P(a = 0) = 0.071429 (equally extreme opposite tail).

Summing these gives: Two-tailed p-value = 5/70 + 5/70 = 10/70 = 1/7 ≈ 0.142857. Researchers comparing software outputs can review Fisher’s exact test principles for additional background.

Step 5: Dynamic array formula for automated calculations

In Microsoft 365 and Excel 2021+, the complete two-tailed test can be computed in a single cell without helper rows. Enter the following formula:

=LET(
  obs_a, B2,
  r1, D2,
  c1, B4,
  n, D4,
  min_a, MAX(0, r1 + c1 - n),
  max_a, MIN(r1, c1),
  possible_a, SEQUENCE(max_a - min_a + 1, 1, min_a),
  all_probs, HYPGEOM.DIST(possible_a, r1, c1, n, FALSE),
  obs_prob, HYPGEOM.DIST(obs_a, r1, c1, n, FALSE),
  SUM(FILTER(all_probs, ABS(obs_prob + 0.0000000001 - all_probs) = obs_prob + 0.0000000001 - all_probs))
)

This formula generates all permissible integer values for cell a, calculates their hypergeometric probabilities, filters for outcomes with probability less than or equal to the observed table, and sums them, returning 0.142857.

Step 6: Executing via Python in Excel

Users with Python in Excel enabled can execute the test directly through SciPy. In an empty cell, type =PY, press Tab, and enter:

import scipy.stats as stats
table = [[xl("B2"), xl("C2")], [xl("B3"), xl("C3")]]
res = stats.fisher_exact(table, alternative="two-sided")
res.pvalue

Press Ctrl + Enter. Excel executes the code in the cloud container and returns 0.142857 directly to the worksheet.

How to read the output

Interpreting Fisher’s exact test requires evaluating the p-value against significance thresholds, examining the odds ratio, and adhering to reporting standards.

The primary result is the p-value, representing the probability of observing a table as extreme as or more extreme than the collected data under the null hypothesis:

  • Evaluating Significance: Compare the p-value to the chosen significance level (typically α = 0.05). In the worked example, the two-tailed p-value is 0.1429. Because 0.1429 exceeds 0.05, the investigator fails to reject the null hypothesis of independence. There is insufficient statistical evidence to conclude that treatment and control response rates differ in this sample.
  • One-Tailed vs. Two-Tailed Hypotheses: A one-tailed p-value tests a directional claim (such as treatment superiority), whereas a two-tailed test evaluates any association. Standard scientific reporting requires justifying directional hypotheses prior to data inspection to avoid bias.
  • Evaluating Effect Size via the Odds Ratio: The sample odds ratio is calculated as OR = (a × d) / (b × c). In our example, cell b equals zero, making the raw odds ratio undefined. Applying the Haldane-Anscombe correction adds 0.5 to each cell: OR_adj = [(3 + 0.5) × (4 + 0.5)] / [(0 + 0.5) × (1 + 0.5)] = 15.75 / 0.75 = 21.0.
  • Academic Reporting: In research publications, report the test type, sample size, contingency layout, and exact p-value. For example: “A two-tailed Fisher’s exact test revealed no statistically significant association between treatment intervention and response (p = .143, N = 8).” For larger cohorts, researchers can apply the two proportion z-test.

Troubleshooting Excel

When executing exact tests in spreadsheet environments, common computational and operational errors can arise:

  • Misuse of the FISHER Function: Entering contingency cell values into =FISHER() yields #NUM! errors. As defined in Microsoft documentation, FISHER calculates the logarithmic transformation of Pearson’s r, not contingency testing. Use HYPGEOM.DIST or containerized Python for contingency testing.
  • Incorrect Parameter Sequencing in HYPGEOM.DIST: Supplying a single cell count where a marginal total is expected produces invalid probabilities. Verify that sample_s is cell a, number_sample is row total R₁, population_s is column total C₁, and number_pop is grand total N.
  • Floating-Point Comparison Failures: Excel represents numbers using IEEE 754 floating-point standards. Minor differences in binary representations can cause exact tail probabilities to fail strict equality comparisons. The formula above uses an absolute difference tolerance (0.0000000001) to ensure symmetric tail probabilities are correctly included.
  • Inappropriate Application to r×c Tables: The single-draw hypergeometric formula applies strictly to 2×2 contingency tables. Evaluating tables with three or more rows or columns requires the Freeman-Halton extension or network algorithms, which must be performed in dedicated tools like R or SciPy.
  • Silent CHISQ.TEST Inaccuracies: The native formula =CHISQ.TEST(actual, expected) does not warn users when expected cell counts fall below 5. When sample sizes are small, reliance on CHISQ.TEST can distort statistical conclusions. Consult statistical analysis in Excel for general testing guidelines.

Free alternatives to Excel

While Microsoft Excel can perform Fisher’s exact test through formula construction, specialized statistical packages execute exact tests with single commands and support larger contingency dimensions. The table below compares verified free and open-source alternatives.

Software Platform Interface Type Command / Syntax Contingency Dimensions Primary Advantages
R (stats package) Command line / Scripting fisher.test(table) 2×2 and arbitrary r×c Includes exact odds ratio confidence intervals and Monte Carlo simulation for large tables.
Python (SciPy) Scripting / Library scipy.stats.fisher_exact(table) 2×2 tables Direct integration into data pipelines, pandas DataFrames, and Jupyter notebooks.
Jamovi Graphical Desktop GUI Analyses → Frequencies → Contingency Tables 2×2 and arbitrary r×c Free point-and-click interface built on R; displays Fisher’s exact test alongside Chi-square.
JASP Graphical Desktop GUI Frequencies → Contingency Tables 2×2 and arbitrary r×c Open-source academic GUI offering both frequentist Fisher exact tests and Bayesian contingency tables.
Real Statistics Add-in Excel Add-in Dialog =FISHERTEST(R1) 2×2 tables Operates directly inside Excel worksheets without requiring manual dynamic array formulas.

For laboratory teams conducting routine analyses, graphical tools like Jamovi and JASP provide reproducible outputs without coding requirements. For programmatic data workflows, R and Python provide optimal throughput.

The data table below compares contingency table testing functions across Excel, Python, and R environments, detailing parameter requirements and small-sample constraints.

Frequently asked questions

How do you calculate Fisher’s Exact Test in Excel?

To calculate Fisher’s exact test in Excel without add-ins, use the hypergeometric distribution function HYPGEOM.DIST to find the probability of the observed 2×2 contingency table, where sample_s is cell a, number_sample is row total 1, population_s is column total 1, number_pop is the grand total, and cumulative is FALSE. Then sum the probabilities of all possible table configurations with fixed margins that have a probability less than or equal to the observed table to calculate the two-tailed p-value. In modern Excel, this calculation can be automated using a dynamic array formula combining LET, SEQUENCE, FILTER, and HYPGEOM.DIST.

Are there add-ins or shortcuts to run Fisher’s Exact Test in Excel?

Yes. The most common free academic add-in is the Real Statistics Resource Pack, which adds the custom worksheet functions =FISHERTEST(range) and =FISHER2x2(range, tails) directly into Excel. Additionally, Microsoft 365 users can run Fisher’s exact test natively in worksheet cells using the Python in Excel feature with the command scipy.stats.fisher_exact.

When should you use Fisher’s Exact Test instead of Chi-Square in Excel?

You should use Fisher’s exact test instead of the Chi-Square test (CHISQ.TEST in Excel) whenever your contingency table has small sample sizes, specifically when any cell has an expected frequency of less than 5, or when the overall sample size is below 30. Chi-square relies on an asymptotic approximation that becomes inaccurate with small counts, whereas Fisher’s test computes exact probabilities from the combinatorial hypergeometric distribution.

What does the FISHER function in Excel calculate?

The built-in FISHER function in Excel calculates the Fisher z-transformation of a Pearson correlation coefficient r, using the formula 0.5 × ln((1 + x) / (1 – x)). It does not perform Fisher’s exact test for 2×2 contingency tables. Entering contingency counts into =FISHER() produces calculation errors because the function requires input values between -1 and 1.

Can you run Fisher’s Exact Test using Python in Excel?

Yes. In Microsoft 365 versions with Python in Excel enabled, you can enter =PY into any cell, reference the 2×2 table range using the xl() function, and compute the test using scipy.stats.fisher_exact. Excel runs the script in a secure cloud container and returns the p-value and odds ratio directly into the spreadsheet cell.

Sources

Data: Contingency Table Test Implementations Across Statistical Software

Comparison of exact and asymptotic contingency table statistical functions, mathematical distributions, small-sample handling, and dimensional constraints across Microsoft Excel, R, and Python.

Period covered: 2024/2026. Coverage: Global.

Contingency Table Test Implementations Across Statistical Software
Software PlatformFunction or SyntaxMathematical DistributionTest ClassificationExpected Counts Under 5Contingency Dimensions
Microsoft Excel (Native HYPGEOM.DIST)HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)HypergeometricExact test via permutation summingValid (exact probability mass)2x2 tables only
Microsoft Excel (Native CHISQ.TEST)CHISQ.TEST(actual_range, expected_range)Chi-SquareAsymptotic approximationInvalid (requires expected counts >= 5)Arbitrary r x c tables
Microsoft Excel (Python in Excel / SciPy)scipy.stats.fisher_exact(table, alternative='two-sided')HypergeometricExact conditional testValid (exact probability mass)2x2 tables only
R Stats Package (stats::fisher.test)fisher.test(x, y = NULL, workspace = 200000, hybrid = FALSE)HypergeometricExact conditional testValid (exact probability mass)2x2 and arbitrary r x c
SciPy Stats (scipy.stats.fisher_exact)scipy.stats.fisher_exact(table, alternative='two-sided')HypergeometricExact conditional testValid (exact probability mass)2x2 tables only
Real Statistics Add-in for ExcelFISHERTEST(R1) or FISHER2x2(R1, tails)HypergeometricExact test via add-in functionValid (exact probability mass)2x2 tables only

Sources

Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.

Follow CASRAI

Research-administration guidance, standards updates and independent tool reviews.

Ask CASRAI · free to try

Ask about Fisher’s Exact Test in Excel: Step-by-Step Guide

2 free questions a day, no account.

An AI assistant for research: funder rules, compliance, methods and publishing. It links the sources behind its answers and labels answers that come from a web search.

Answers draw on CASRAI's guides and dictionary plus the federal and funder documents we index: Federal Register, Grants.gov, Regulations.gov and UKRI.

Works on this site and inside Claude, Cursor and the AI tools you already use.

Everything CASRAI publishes — this page, the dictionary, the guides and the news — stays open to read, with no account and no card.

Referenced across the research world

University of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logoUniversity of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logo
  • University of Cambridge logo
  • Columbia University logo
  • Crossref logo
  • University of Edinburgh logo
  • Harvard University logo
  • University of Oxford logo
  • Princeton University logo
  • Stanford School of Medicine logo
  • University College London logo
  • ORCID logo

View CASRAI adoption →