RANK function in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see the ranking of salespeople based on their total sales for the month.

📊 Data: You have a list of salespeople and their total sales amounts for the month.

🎯 Deliverable: Create a ranking column that shows each salesperson's rank based on their sales, with 1 being the highest sales.

Sample Data

Salesperson	Total Sales
Alice	8500
Bob	9200
Charlie	7800
Diana	9200
Edward	6700
Fiona	7300
George	8500
Hannah	6000

1

Step 1: Add a new column header next to Total Sales named 'Rank'.

Expected Result

A new column 'Rank' appears next to 'Total Sales'.

2

Step 2: In the first cell under 'Rank' (assume cell C2), enter the formula to rank the sales in descending order.

=RANK(B2, $B$2:$B$9, 0)

Expected Result

The formula calculates the rank of the sales amount in B2 compared to all sales in B2:B9, with 1 being the highest.

3

Step 3: Copy the formula in C2 down to all rows in the 'Rank' column to rank all salespeople.

Drag the fill handle from C2 down to C9.

Expected Result

Each salesperson has a rank number from 1 to 4 based on their sales.

4

Step 4: Check that salespeople with the same sales amount have the same rank.

Expected Result

Bob and Diana both have sales of 9200 and both show rank 1; Alice and George both have 8500 and both show rank 3.

Final Result

Salesperson | Total Sales | Rank
------------|-------------|-----
Alice       | 8500        | 3
Bob         | 9200        | 1
Charlie     | 7800        | 5
Diana       | 9200        | 1
Edward      | 6700        | 7
Fiona       | 7300        | 6
George      | 8500        | 3
Hannah      | 6000        | 8

✓Bob and Diana are tied for the highest sales with rank 1.

✓Alice and George share the same sales and rank 3.

✓Hannah has the lowest sales and is ranked 8.

Bonus Challenge

Create a new ranking that ranks salespeople with the lowest sales as rank 1.

Show Hint

Change the third argument in the RANK function to 1 to rank in ascending order.