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.