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

- 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

- 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

- 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’