RANK function in Excel - Step-by-Step Guide

Introduction

The RANK function helps you find the position of a number in a list compared to others. It tells you if a number is the highest, lowest, or somewhere in between. This is useful when you want to see who scored the most or least in a group.

When you want to see who has the highest sales in a list of sales numbers.

When you need to find the top scorer in a class test.

When comparing daily temperatures to find the hottest day.

When ranking products by customer ratings.

When you want to order expenses from largest to smallest.

Steps

Step 1: Click

- cell where you want the rank result

The cell is selected and ready for input

Step 2: Type

- the selected cell

Formula input starts

💡 Start by typing =RANK(

Step 3: Enter the number to rank

- inside the parentheses of the formula

The first argument of the formula is set

Step 4: Type a comma and select the range of numbers to compare against

- inside the parentheses after the first argument

The second argument of the formula is set

Step 5: Optionally, type a comma and enter 0 for descending rank or 1 for ascending rank

- inside the parentheses after the second argument

The order of ranking is set; if omitted, descending is default

Step 6: Type a closing parenthesis and press Enter

- at the end of the formula

The rank number appears in the cell showing the position of the number in the list

Before vs After

Before

A column with sales numbers: 200, 450, 300, 450, 100

After

A new column showing ranks: 4, 1, 3, 1, 5

Settings Reference

Number

📍 First argument in the RANK formula

The number you want to find the rank for

Default: None

Ref

📍 Second argument in the RANK formula

The list of numbers to compare against

Default: None

Order

📍 Third argument in the RANK formula

Defines if ranking is from largest to smallest or smallest to largest

Default: 0 (descending)

Common Mistakes

Forgetting to include the range of numbers in the formula

Without the range, Excel cannot compare the number to others and returns an error

Always include the range of numbers as the second argument, like =RANK(A2, A$2:A$6)

Not fixing the range with $ signs when copying the formula down

The range shifts when copying, causing wrong ranks

Use absolute references for the range, e.g., $A$2:$A$6, to keep it fixed

Using order argument incorrectly

Entering 1 when you want descending rank will give unexpected results

Use 0 or omit order for descending (largest is rank 1), use 1 for ascending (smallest is rank 1)

Summary

The RANK function shows the position of a number in a list compared to others.

You must provide the number, the list of numbers, and optionally the order (descending or ascending).

Remember to fix the range with $ when copying the formula to avoid errors.

Practice

1. What does the Excel RANK function do?

easy

A. It adds all numbers in a list.

B. It finds the position of a number in a list.

C. It counts how many numbers are in a list.

D. It finds the average of numbers in a list.

Solution

1. Step 1: Understand the purpose of RANK

   The RANK function returns the position of a number compared to others in a list.

2. Step 2: Compare with other options

   Options A, C, and D describe sum, count, and average, which are different functions.

3. Final Answer:

   It finds the position of a number in a list. -> Option B

4. Quick Check:

   RANK = position in list [OK]

Hint: RANK shows where a number stands in a list [OK]

Common Mistakes:

• Confusing RANK with SUM or COUNT functions
• Thinking RANK calculates averages
• Assuming RANK sorts the list

2. Which of these is the correct syntax for the RANK function in Excel?

easy

A. =RANK(number, ref, order)

B. =RANK(ref, number, order)

C. =RANK(number, order, ref)

D. =RANK(order, number, ref)

Solution

1. Step 1: Recall RANK function syntax

   The correct syntax is =RANK(number, ref, order), where 'number' is the value to rank, 'ref' is the list, and 'order' is optional.

2. Step 2: Check each option

   Only =RANK(number, ref, order) matches the correct order of arguments.

3. Final Answer:

   =RANK(number, ref, order) -> Option A

4. Quick Check:

   Syntax = RANK(number, ref, order) [OK]

Hint: Remember: number first, then list, then order [OK]

Common Mistakes:

• Swapping number and list arguments
• Placing order argument before list
• Forgetting order is optional

3. Given the list {50, 80, 80, 30, 90}, what is the result of =RANK(80, A1:A5, 0) if these values are in cells A1 to A5?

medium

A. 2

B. 3

C. 1

D. 4

Solution

1. Step 1: Understand RANK with order 0 (descending)

   Order 0 ranks highest number as 1. The list sorted descending is 90 (1), 80 (2), 80 (2), 50 (4), 30 (5).

2. Step 2: Find rank of 80

   Both 80s share rank 2 because they tie. So RANK(80) = 2.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   80 is second highest, so rank = 2 [OK]

Hint: Order 0 means highest number gets rank 1 [OK]

Common Mistakes:

• Assuming ranks increase by 1 after ties
• Confusing ascending and descending order
• Ignoring ties and giving different ranks

4. You used the formula =RANK(A2, A1:A5, 1) but got unexpected ranks. What is the likely error?

medium

A. RANK function does not accept the order argument.

B. The order argument should be 0 for ascending rank.

C. The range A1:A5 includes the cell A2 itself.

D. The order argument 1 means ascending rank, but you expected descending.

Solution

1. Step 1: Understand order argument meaning

   Order 1 means ascending rank (lowest number is rank 1). If you expected highest number as rank 1, order should be 0.

2. Step 2: Identify mismatch in expectation

   The unexpected ranks happen because order 1 ranks lowest first, opposite of what was expected.

3. Final Answer:

   The order argument 1 means ascending rank, but you expected descending. -> Option D

4. Quick Check:

   Order 1 = ascending, check expectation [OK]

Hint: Order 1 ranks lowest first; use 0 for highest first [OK]

Common Mistakes:

• Confusing order 0 and 1 meanings
• Thinking order argument is optional but needed
• Including the cell itself in the range causes no error

5. You have scores in cells A1:A6: 85, 92, 85, 70, 92, 60. You want to rank the score in A3 using =RANK(A3, A1:A6, 0). What rank will it show and why?

hard

A. 1, because 85 is the highest score

B. 2, because 85 is the second highest unique score

C. 3, because 92 is rank 1, 85 is rank 3 with ties sharing rank

D. 4, because ties increase rank numbers

Solution

1. Step 1: Sort scores descending and assign ranks

   Scores sorted: 92 (rank 1), 92 (rank 1), 85 (rank 3), 85 (rank 3), 70 (rank 5), 60 (rank 6).

2. Step 2: Find rank of score in A3 (85)

   Since 85 appears twice, both get rank 3. Ties share the same rank, and ranks after skip numbers.

3. Final Answer:

   3, because 92 is rank 1, 85 is rank 3 with ties sharing rank -> Option C

4. Quick Check:

   Ties share rank, next rank skips numbers [OK]

Hint: Ties share rank; next rank skips numbers [OK]

Common Mistakes:

• Assuming ranks increase by 1 after ties
• Ignoring ties and giving different ranks
• Confusing rank order with ascending instead of descending