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:

1. Sort the data 
2. RANK() function: this gives the rank of a number in a list of numbers
3. Syntax: RANK(number, reference, order)
   1. number: the number to which the rank is to be assigned
   2. reference: the list of numbers in which the number is to be ranked
   3. 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

• Select the data to be ranked

Step 2 – Open the Sort box

• Go to Data > Sort & Filter
• Click on the Sort button

Step 3 – Set the parameters

• 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 

• Select Column A
• Right-click and choose Insert from the context menu

Step 5 – Create the ranks

• 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

• 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

• 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

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. 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

• Select the data
• Go to Data > Sort & Filter
• Click on the Sort button

Step 4 – Set the parameters

• 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

• The rows are now ranked from highest to lowest in terms of ‘worldwide_grosss_USD’