How to Perform a Wilcoxon Signed Rank Test in Excel (Step-by-Step)


The Wilcoxon Signed-Rank Test is the non-parametric version of the paired samples t-test.

It is used to test whether or not there is a significant difference between two population means when the distribution of the differences between the two samples cannot be assumed to be normal.

This tutorial provides a step-by-step example of how to conduct a Wilcoxon Signed-Rank Test in Excel.

Step 1: Create the Data

Suppose an engineer want to know if a new fuel treatment leads to a change in the average miles per gallon of a certain car. To test this, he measures the mpg of 12 cars with and without the fuel treatment.

We’ll create the following data in Excel to hold the mpg values for each car with the fuel treatment (group1) and without the fuel treatment (group 2):

Step 2: Calculate the Difference Between the Groups

Next, we’ll calculate the difference between the groups:

Step 3: Calculate the Absolute Differences

Next, we’ll calculate the absolute difference between the groups, returning a blank if the absolute difference is zero:

Step 4: Calculate the Rank of the Absolute Differences

Next, we’ll use the RANK.AVG() function to calculate the rank of the absolute differences between the groups, returning a blank if the absolute difference is zero:

Step 5: Calculate the Positive & Negative Ranks

Next, we’ll calculate the positive ranks:

And we’ll calculate the negative ranks:

Step 6: Calculate the Test Statistic & Sample Size

Lastly, we’ll calculate the test statistic which is simply the smaller of the sum of the positive ranks or the sum of the negative ranks:

And we’ll calculate the sample size, which is the total number of ranks that aren’t equal to zero:

Wilcoxon Signed Rank Test in Excel

The test statistic turns out to be 10.5 and the sample size is 11.

In this example, the Wilcoxon Signed-Rank Test uses the following null and alternative hypotheses:

H0The mpg is equal between the two groups

HAThe mpg is not equal between the two groups

To determine if we should reject or fail to reject the null hypothesis, we can find the critical value that corresponds to α = .05 and a sample size of 11 in the following Wilcoxon Signed Rank Test Critical Values Table:

The critical value that corresponds to α = .05 and a sample size of 11 is 10.

Since the test statistic (10.5) is not less than the critical value of 10,  we fail to reject the null hypothesis.

We do not have sufficient evidence to say that the mean mpg is not equal between the two groups.

Bonus: Feel free to use this Wilcoxon Signed-Rank Test Calculator to automatically calculate the test statistic for a Wilcoxon Signed-Rank Test.

8 Replies to “How to Perform a Wilcoxon Signed Rank Test in Excel (Step-by-Step)”

  1. Where can I obtain the critical values of n > 30? I need to analyse data where n is approximately 12K. Is there a formula for the critical value?

  2. I think you have a typo in your conclusion statement in that you left out the word “not” in your conclusion statement. In your example here, you state H0 is the means of the two groups are equal. The result of the test is “fail to reject H0” Then you state we “don’t have enough evidence to say the mean is equal. ” I think since we fail to reject H0″ and H0 is “they are equal” then I think the conclusion is supposed to be “we don’t have enough evidence to say the means are NOT equal.”

  3. Thank you so much Zack! Great explanation!
    Question! What if sample sizes are unbalanced between groups? Do we proceed as you showed above? If sample sizes are unbalanced, I do not know how to deal with the fact that we will not be able to calculate differences and ranks between all samples taken from each group. What can we do in this scenario?

  4. Hello,

    Thank you for this method, it really helped me.
    I have a question about the presentation of the results. What kind of plot would you use to represent the difference in data using this test ?

  5. Thanks this was really useful. One Q – SPSS kicks out p values for Wilcoxon tests, whereas with this method, I can only say reject hypothesis p0.05 (or whatever alpha value you are using). How can I get a p value?

  6. So much double negatives in this article. You could have just said that you accept the null hypothesis rather than saying “we fail to reject the null hypothesis”, you troglodytes. it took me 3 hours to figure out what this article was tryna say

Leave a Reply

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