Working With Rank Function in Excel

Rank function is a very useful function in case of ranking numeric values within a list. It will return rank of a number against a range of numbers and will ignore other sorts of values including text, character etc.

In this short tutorial, we’ll learn how you can use rank function and what sort of result you will get by using this function. Hopefully, this beginner-level tutorial will give you a clear understanding of rank function and you will be able to work with it independently.

Let’s start,

Basic Syntax

= RANK(number, ref, [order])

As you can see from the syntax, Rank function passes three arguments which are number, ref & order.

• number: number is the first argument of rank function and it means the number we would like to check within a list of numbers.
• ref: this argument is used to compare within the list of numbers. We could select the entire cell by referencing the cell or we could also select some values within the cell manually.
• order: this argument is optional but it’ll help you to maintain the order of the list and rank them according to descending or ascending order.

-         0 or leave the argument empty: It will rank the list in default descending order.

-         1 : for ascending order please type 1 in the last argument field of the rank function.

Example

Suppose, you have a worksheet which contains data like the below screenshot and from those data you want to rank them based on Total Profit. 

So, now at first, choose the blank cell E2 and start typing “=Rank” and click on the suggested Rank function, like in the below image,

Then just pass the arguments according to the format of the function and every argument will be separated by a comma.

For our case, we just select the D2 cell as the first argument of the rank function, then select all other cells of the Total Profit header which contains numeric values as reference (the second argument) and at last, choose descending order so we select ‘0’ as rank functions final argument.

And, we get the rank for that number within the list.

Finally, just drag your mouse to the last value of the worksheet

and you will get the ranks of those numeric values within the list. Like the below screenshot,

Finally, we are adding a GIF image for you so that you can cope with the total process of the Rank function.

Isn’t it simple! Just try to choose the other ways of passing the arguments on your own and see the difference. It will help you to understand the Rank function more precisely. Thanks for staying with tutorialabc.com!

If you have any queries, please let us know through our contact page.