Fisher’s Exact Test

When the conditions for Pearson’s chi-square test are not met, especially when one of more of the cells have expi < 5, an alternative approach with 2 × 2 contingency tables is to use Fisher’s exact test. Since this method is more computationally intense, it is best used for smaller samples.

Example 1: Repeat Example 2 from Independence Testing using the data in range A5:D8 of Figure 1; i.e. determine whether the cure rate is independent of the therapy used.

Chi-square test Excel

Figure 1 – Data and Chi-square test for Example 1

As you can see from Figure 1, the expectation for two of the cells is less than 5. Since we are dealing with a 2 × 2 contingency table with relatively small sample size, it is better to use Fisher’s exact test.

The approach is to determine how many different ways the above marginal frequencies can be achieved and then determine the probability that the above observed cell configuration can be obtained merely by chance.

We can restrict our attention to any one of the cells since once the frequency for one cell is determined the frequencies for the other cells can be determined from the marginal totals. We choose cell B6 since it has the smallest marginal total (namely 9 in cell D6) and it is smaller than the other element that makes up this marginal total (namely 7 in cell C6).

Now cell B6 can take any value between 0 and 9; once this value is set the values of the other three cells can be adjusted to maintain the marginal totals.

The probability that cell B6 takes on a specific value x is equivalent to the probability of getting x successes in a sample of size 9 (cell D6) taken without replacement from a population of size 21 (cell D8) which contains 11 (cell B8) successful choices. This can be calculated by the hypergeometric distribution. Here cells D6 and B8 are cells with the marginal totals corresponding to cell B6 and cell D8 contains the grand total.

Figure 2 contains a table of the probabilities for each possible value of x.

Fisher exact test Excel

Figure 2 – Fisher exact test for Example 1

Thus, e.g., cell L11 contains the formula


Our test consists of determining whether the probability that at most 2 of those taking therapy 1 are cured (the observed count in cell B6) is less than .05. From Figure 2, we see that the probability of count 0 is 3.4E-05, the probability of count 1 is .001684 and the probability of count 2 is .022454 for a cumulative probability of .024172 < .05 = α, and so we reject the null hypothesis and conclude there is a significant difference between the cure rates for the two therapies.

There are one-tail and two-tail versions of the test. The p-value for the one tail test (cell L17) is given by the formula =SUM(L6:L8) or equivalently (for versons of Excel starting with Excel 2010)


The p-value for the two tail test (cell L18) given by the formula


where K14 is the leftmost cell in the right tail that has a pdf value ≤ L8 (since .005614 ≤ .022454, but .050522 > .022454). Equivalently, we can use the formula (for versons of Excel starting with Excel 2010)


Real Statistics Excel Function: The following function is provided in the Real Statistics Resource Pack:

FISHERTEST(R1, tails) = the probability calculated by the Fisher exact test for the  2 × 2 contingency table contained in range R1 where tails = the number of tails = 1 or 2 (default).

The range R1 must contain only numeric values.

For Example 1, FISHERTEST(B6:C7,1) = .024172 and FISHERTEST(B6:C7, 2) = .029973.

18 Responses to Fisher’s Exact Test

  1. EMM says:

    I downloaded the Real Statistics Resource Pack, but I an unable to use perform a fisher test as advertised. It is not in the list of functions when I open Realstats, and it doesn’t work when I try entering the function directly into the cell either (=FISHERTEST(R1,t)). when I gry this I get #VALUE!. I am selecting only the 2×2 table for my data range and it is definitely only numeric data. Other functions work ( I tested the chi square function). FYI I have the 2007 version of excel.

    Any suggestions?

    Thank you.

    • Rachel says:

      I’m having this problem too – for some reason it will only allow me to perform the one-tailed Fisher’s Exact Test. I would really like to use the two-tailed test, if someone can shed some light on this…

      • Charles says:

        I just retested the Real Statistics function =FISHERTEST(R1, tails) and it works properly for tails = 1 or 2. There was a problem several months ago, but it was resolved several releases ago. If you are not using a recent release I suggest that you download the latest release of the software.

        • kristin says:

          I just downloaded the latest version of the software and am using it on some data in Excel. Interestingly, it worked on the first 2 x 2 table I used it on. However, on the next 2×2 table I tried it on, it gives me the #VALUE! error message. I wonder if it’s something to do with the numbers?

          My first 2 x 2 table had the following numbers (from left to right top row, then bottom row): 82, 248, 75, 28

          My second 2 x 2 table had the following numbers: 12, 179, 145, 97.

          Any thoughts? I’m doing a two-tailed analysis. Thanks!

          • Charles says:

            It worked on my computer. I got 3.16E-18 for the first problem and 2.76262E-34 for the second problem.

  2. Colin says:

    It is very creative to use hypergometric distribution to solve this problem, you are very cool! But I think there is a typo in the following paragraph. Maybe you should change “with replacement” to “without replacement”. Is that right?

    “ The probability that cell B6 takes on a specific value is equivalent to the probability of getting successes in a sample of size 9 (cell D6) taken with replacement from a population of size 21 (cell D8) which contains 11 (cell B8) successful choices. This can be calculated by the hypergeometric distribution. ”

  3. Colin says:

    You wrote : “The probability that cell B6 takes on a specific value is equivalent to the probability of getting successes in a sample of size 9 (cell D6) taken with replacement from a population of size 21 (cell D8) which contains 11 (cell B8) successful choices. This can be calculated by the hypergeometric distribution. ”
    According to above, should the Excel formula “=HYPGEOMDIST(K11,$B$8,$D$6,$D$8)” change to “=HYPGEOMDIST(K11,$D$6,$B$8,$D$8)”

  4. Colin says:

    You should change ” .023172 < .05 = α" to "0.024172", and "FISHERTEST(B6:C7,1) = .023172" should be "0.024172".
    BTW, I cannot understand the two-tailed test formula "=2*MIN(SUM(L6:L8),SUM(L8:L15),.5)."

    • Charles says:

      Thanks for catching another error. I have now corrected the typo. The two-tailed test is just twice the one-tailed test; the .5 entry is there just in case in order to make sure that the probability value is not larger than 1.

  5. Guest student says:

    Thank you so much for your help and your brilliant add-in!

  6. Gerald Schoenknecht says:

    The function FISHERTEST does not seem to work for large values (I tried 4916/201/68/8)?

    • Charles says:

      The problem is not with large values. In fact it even occurs for 3/2/2/3. The problem is that in the two-tailed test when the right and left critical regions overlap a p-value greater than 1 occurs since the overlapped area is counted twice. In these cases the correct p-value = 1. I will correct this in the next release, which I expect to deliver in the next day or two. Thanks again for finding the problem.

  7. Maarten says:

    Dear Charles,

    I have downloaded the Real Statistics Resource Pack today – I have Excel 2011 for Mac. Like EMM, Rachel and Kristin, I do not seem to have FISHERTEST in the list of Realstats functions. When I directly type =FISHERTEST(B2:C3,1) or =FISHERTEST(B2:C3,2) for a 2×2 contingency table, I also get #VALUE! as a result. However, when I omit the tail and merely type in =FISHERTEST(B2:C3) I do get a result. Could you tell me how I can find out whether 1 or 2 tails have been used in the calculation? And how can I control the number of tails when I fail in the way described above?

    Kind regards,


    • Charles says:

      If you leave out the second argument, it defaults to two-tails (which is the preferred test). I don’t know why inserting the second parameter results in an error in the Mac version of the software. I just checked and this is not the case for the Windows version of the software. Unfortunately, I don’t have access to a Mac computer at present so that I can resolve this problem. Once I get access to a Mac I will issue a new release which provides access to the 1-tailed test result.

      • Maarten says:


        Many thanks for your quick reply. I am also able to run Windows separately on my Mac via VMWare Fusion. I have downloaded the Real Stats Resource Pack in Windows, and, as you mentioned, it does indeed work fine in there.

        On another note, could you please tell me which method you used to compute the 2-tail P value? In my understanding there are three different methods to compute this?


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>