Excel RANK Function Tutorial: Create Perfect Rankings in 5 Minutes (2025)

Published: December 6, 2024 - 2 min read

Ashley Lenz

Need to create rankings in your Excel spreadsheet? The RANK function automatically calculates the position of values in a dataset, saving you hours of manual work. Let’s explore how to use this versatile function through practical examples and step-by-step instructions.

RANK Function Components

Before we start, let’s take a moment to under stand the RANK function’s basic syntax:

=RANK(number, ref, [order])

Parameters:

  • number: The value to rank
  • ref: The range containing all values
  • order: Optional. 0 for descending (default), 1 for ascending

How to Create Basic Rankings in Excel

First, let’s set up a simple ranking system using sales data.

  1. Enter sample data

In Column A, enter the following sales figures:

A1: Sales

A2: 5000

A3: 7500

A4: 3200

A5: 9100

A6: 4800

  1. Write the RANK formula

In Column B, enter:

B1: Rank

B2: =RANK(A2,$A$2:$A$6)

  1. Apply and extend the formula
  • Select cell B2
  • Click and drag the fill handle down to B6

Your spreadsheet should now look like this:

Sales

Rank

5000

3

7500

2

3200

5

9100

1

4800

4

Rank Data from Highest to Lowest

The RANK function defaults to descending order (highest to lowest). Here’s how to work with this default behavior effectively.

  1. Use the default syntax

=RANK(value, range)

  1. Apply to your dataset

For cell B2: =RANK(A2,$A$2:$A$6)

  1. Handle tied values When values are equal, RANK assigns the same rank to both:

Score

Rank

95

1

95

1

90

3

85

4

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Create Ascending Rankings (Lowest to Highest)

To rank from lowest to highest, add the optional order parameter.

  1. Modify the formula

=RANK(value, range, 1)

  1. Compare results

Descending (Default):

Value

Rank

10

1

8

2

6

3

Ascending (With order=1):

Value

Rank

10

3

8

2

6

1

Rank Values Based on Multiple Criteria

Combine RANK with other functions for more complex rankings, for example:

Rank with conditions

=RANK(IF(B2>100,A2,0),$A$2:$A$10)

RANK Function in Excel

Want to automate your Excel rankings with live data? Try Coefficient to connect your spreadsheets directly to your data sources. Get started with Coefficient and eliminate manual data updates forever.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Ashley Lenz Product Researcher @ Coefficient
As a product researcher at Coefficient, Ashley taps into the power of data to create intuitive solutions that save users valuable time. By working closely with users, Ashley helps to uncover key insights that shape product features, enabling teams to streamline workflows and boost productivity. Her passion for data-driven research and optimizing user experiences fuels her work, ensuring the product delivers maximum efficiency and value.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies