Beware of Excel's rank() function, or nonparametric tests will be incorrect.

Excel does not have any nonparametric tests built in, so you'd need to set up the calculations yourself. If you do that, beware of Excel’s rank() function. It doesn’t work as nonparametric ranking work when there are ties (identical values in the data set, making ranking ambiguous).

Nonparametric tests give all tied values share the average rank. In Excel, all tied values share the lowest of the ranks they tie for, and the other ranks are not used.

Here is an example. The data are 3, 9, 2, 4, 2, 0.  The value ‘2’ appears twice.

Nonparameric tests would assign each of those values the rank of 2.5, since they tie for the 2nd and 3rd rank. Then the next highest value (3) gets the rank of 4.

Excel’s rank() function give both ‘2’ values the rank of 2 (since there is one lower value), and then gives the next highest value (3) the rank of 4. If you then use those Excel ranks to compute a nonparametric test, you’ll get the wrong result. This article from Microsoft, explains how to coax Excel to do the calculations properly.