How to rank in Excel from highest to lowest
You can watch a video tutorial here.
Excel is popular as a spreadsheet application but is also frequently used as a database and to create lists. Very often, when you are looking at a set of data, you would be interested in knowing the rank or position of each row with respect to a particular value. For example, you need to find the top ten branches in terms of sales.
In Excel, there are 2 ways of doing this:
- Sort the data
- RANK() function: this gives the rank of a number in a list of numbers
- Syntax: RANK(number, reference, order)
- number: the number to which the rank is to be assigned
- reference: the list of numbers in which the number is to be ranked
- order: (optional) this specifies whether the numbers are to be considered in ascending or descending order
Option 1 – Sort the data
Step 1 – Select the data
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-1-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x617.png)
- Select the data to be ranked
Step 2 – Open the Sort box
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-2-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x793.png)
- Go to Data > Sort & Filter
- Click on the Sort button
Step 3 – Set the parameters
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-3-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x477.png)
- Tick My data has headers
- Define the parameters for the sort
- Sort by: ‘worldwide_gross_USD’
- Order: Largest to Smallest
- Click OK
Step 4 – Insert a column
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-4-–-How-to-rank-in-Excel-from-highest-to-lowest--675x1024.png)
- Select Column A
- Right-click and choose Insert from the context menu
Step 5 – Create the ranks
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-5-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x574.png)
- Name the new column ‘Rank’
- Type the first 2 numbers of the rank sequence i.e. 1,2
- Select the numbers and drag the fill handle down to the end of the data
Step 6 – Check the result
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-6-–-How-to-rank-in-Excel-from-highest-to-lowest-.png)
- The rows are now ranked from highest to lowest in terms of ‘worldwide_grosss_USD’
Option 2 – Use the RANK() function
Step 1 – Create the formula
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-1-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x209.png)
- Select the cell in which the rank is to be displayed
- Type the formula using cell references:
=RANK(worldwide_gross_USD, range of worldwide_gross_USD)
- Press Enter
Step 2 – Copy the formula
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-2-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x566.png)
- Using the fill handle from the first cell, drag the formula to the remaining cells
OR
- Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
- Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
Step 3 – Open the Sort box
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-3-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x785.png)
- Select the data
- Go to Data > Sort & Filter
- Click on the Sort button
Step 4 – Set the parameters
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-4-–-How-to-rank-in-Excel-from-highest-to-lowest--1024x478.png)
- Tick My data has headers
- Define the parameters for the sort
- Sort by: ‘Rank’
- Order: Smallest to Largest
- Click OK
Step 5 – Check the result
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-5-–-How-to-rank-in-Excel-from-highest-to-lowest-.png)
- The rows are now ranked from highest to lowest in terms of ‘worldwide_grosss_USD’