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.
- Enter sample data
In Column A, enter the following sales figures:
A1: Sales
A2: 5000
A3: 7500
A4: 3200
A5: 9100
A6: 4800
- Write the RANK formula
In Column B, enter:
B1: Rank
B2: =RANK(A2,$A$2:$A$6)
- 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.
- Use the default syntax
=RANK(value, range)
- Apply to your dataset
For cell B2: =RANK(A2,$A$2:$A$6)
- Handle tied values When values are equal, RANK assigns the same rank to both:
|
Score |
Rank |
|---|---|
|
95 |
1 |
|
95 |
1 |
|
90 |
3 |
|
85 |
4 
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.
- Modify the formula
=RANK(value, range, 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.
