How to Use the RANK Formula in Excel

The RANK formula in Excel is normally used to show ranking (high to low, or low to high) of any set of values. This is useful if you are applying statistical analysis on a data set and you need to provide a ranking of how they compare against one another.

Formula Explanation:

• Number: It means the number for which you won’t find the rank. You can reference a cell here which contains a number.
• Reference: It is an array of, or reference to, a list of numbers. The reference must be numeric.
• Order: Order means the order about ranking either it will be highest to lowest (descending), or lowest to highest (ascending).

Example:

In this example, we will rank the sales amount ($) of the divisions of a car dealership. We will rank them in descending order and then in ascending order.

Solution:

For the rank analysis we set forth the following:

• Number is the individual sales amount to be evaluated for rank.
• Reference is the range of numbers where we are evaluating the rank.
• Order is given in both the ways i.e. Ascending (lowest one will be no.1 and highest one will be at the last number) by using 1, and Descending (highest one will be no.1 and lowest one will be at the last number) by using 0.

The results of the ranking are in columns F and G above.

In the descending rank column, the first rank is division 9 which has sales of $1.5M (the largest) and division 4 is the 12th rank.

In the ascending rank column, the first rank is division 4 which has the smallest amount of sales at $876k. The division 9 has the highest sales and is therefore ranked 12.

Overall, this is a handy formula for both ascending and descending ranks depending on what kind of analysis you want to perform. It does not require manually going through all the data or sort it and then put a rank beside it. You can simply use the rank formula to be able to compute their respective ranks against each other.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!