10 Ways To Rank in Microsoft Excel

If you wish to learn the must-have skill of how to rank in Excel, follow along with the exercises outlined in this elaborate Microsoft Excel tutorial.

For data analysis and comparison, ranking in Excel is a powerful tool. It allows you to easily identify the top performers, and allocate resources based on performance, benchmark strategies, and so on.

Ranking helps you understand where a number falls within a group, be it sales figures, inventory levels, or exam scores.

In this tutorial, I’ll introduce you to various Excel ranking functions, and specialized formulas that deal with ties, PivotTable, and PowerQuery. For automation capabilities, you’ll also learn Excel VBA and Office Scripts for data ranking in Excel.

Using the Sort & Filter Tool

The Sort & Filter tool is the simplest solution for ranking certain people, items, etc., on your dataset.

You simply apply the sorting function using user interface buttons and Excel will sort the input data in ascending or descending order.

However, if you need a rank number, like 1, 2, 3, etc., you’ll need to create that manually in an adjacent column of the input dataset.

Sort & Filter drop down menu
Sort & Filter drop-down menu

To use this tool, select the entire input dataset and click on the Sort & Filter drop-down arrow in the Editing commands block.

Sort dialog box
Sort dialog box

Click on the Custom Sort option in the drop-down menu. You’ll see the Sort dialog box.

Customize sorting conditions
Customize sorting conditions

This dialog box allows you to customize the sorting parameters at granular levels. For example, here are the modifications you can do:

  • Column: It allows you to choose the column by which sorting must be applied. In this exercise, it’s the Sales column.
  • Sort On: You can choose sorting categories, like Cell Values, Cell Color, Font Color, and Conditional Formatting Icon. Let’s choose Cell Values now.
  • Order: It offers three sorting order options, like Largest to Smallest, Smallest to Largest, and Custom List.

Once all the customizations are complete, click OK on the Sort dialog.

Excel sorted dataset
Excel sorted dataset

Excel will sort the input data as per the supplied sorting configurations.

Created the Rank column manually
Created the Rank column manually

You can now create a new column to the right of the sorted dataset named Rank.

Type 1 and 2 in the first two cells below the new column. Now use the fill handle to generate the remaining rankings.

You’ve successfully ranked data using the Sort & Filter tool.

A major drawback of this method is its inability to handle ties while sorting.

Using the SORTBY Function

The SORTBY function allows you to rank a dataset by multiple columns in an easy way.

Sample dataset 1

Suppose, you want to rank the top sales agents across various sites in a dataset. Your worksheet could look similar to the one shown above.

Since the sample size is tiny in this example, you can easily spot the top sellers across sites by looking at the sales figures. However, when the sample size is huge, you can’t rely on a manual review of sales figures.

Then, you must use a programmatic approach like the SORTBY function. It’ll allow you to sort the target cell range by multiple arrays within the same or different worksheet and workbook.

For example, you want to rank the top sellers by site in the above dataset.

Created ad-hoc columns
Created ad-hoc columns

Firstly, create the necessary column headers where you want the ranked data to be populated.

Enter SORTBY formula
Enter SORTBY formula

Then, in the first cell of the destination cell range, like E2 in the current exercise, enter the following RANKBY formula:

=SORTBY(A2:C9,C2:C9,-1)

In the above formula, you’re sorting the whole dataset by the sales figures in descending order.

Get sorted ranges
Get sorted ranges

Hit Enter to populate the entire sorted dataset.

Rank formula in Excel using SORTBY
Rank formula in Excel using SORTBY

So, you’ve ranked the names of sales agents by sales figures in different sites. You can now assign rankings if needed using an additional rank column.

An advantage of this method is it doesn’t change the source dataset.

Using the RANK Function

The RANK function is the default tool to rank datasets in Excel. By default, it assigns the same rank to values that appear multiple times (ties).

By using this function, you don’t need to sort data in descending order and then assign ranks manually. Instead, you directly derive the ranks in a new column adjacent to the dataset. Or, you can choose the destination for another worksheet or workbook.

Sample dataset 2

Suppose, you’ve got a dataset containing sales agents and their sales figures. Now, you want to quickly rank the top three sales agents from that dataset. Refer to the sample dataset shown above.

The RANK formula
The RANK formula

Now, to create a ranking for the sales agents in column D enter the following formula in D2:

=RANK(C2,$C$2:$C$9,0)

In this formula, you’ll rank the value C2 by referring to all values in the cell range C2:C9. After evaluation, Excel will rank the input value in descending order, which is denoted by the numerical 0 in the formula.

Calculate RANK function
Calculate RANK function

Hit Enter to calculate the rank for C2.

Using the fill handle
Using the fill handle

Now, select C2 and drag down the fill handle to populate ranks for the rest of the rows.

The ranks assigned in this method are scrambled and not in ascending or descending order.

Sort smallest to largest
Sort smallest to largest

You can use the Sort & Filter tool, mentioned earlier, to restructure the dataset in ascending order.

Thus, you get a list of top 10, top 100, etc., ranks from the input dataset.

This function is only available to Excel editions prior to Excel 2010.

Using the RANK.EQ Function

The RANK.EQ function works similarly to the RANK function. However, it provides a more accurate ranking, especially for larger datasets. The syntax part EQ stands for equal, meaning it’ll rank equal values the same, and if there are ties, the next number will jump accordingly.

The RANK.EQ function
The RANK.EQ function

Enter the following formula into a cell where you’d like to get the rank of a value and its reference dataset:

=RANK.EQ(C2,$C$2:$C$9,0)

The formula arguments are exactly the same as the ones explained previously for the RANK function.

Calculate the RANK.EQ function
Calculate the RANK.EQ function

Hit Enter to get the rank for the first cell in the sample dataset.

Copy the formula downwards
Copy the formula downwards

Use the fill handle to populate ranks for the rest of the cells under the Rank column.

Managing ties
Managing ties

When there’s a tie, like in the 5th place for the current dataset, the function assigns the rank 5 to both the qualifying cells and moves on from the 7th position. The 6th rank is omitted.

Using the RANK.AVG Function

The RANK.AVG function is more appropriate for multiple ties during a ranking process.

It assigns a rank (position) to a number within a list, considering ties. If multiple numbers share the same spot, it awards them the average rank instead of skipping positions. This is helpful when you have data with duplicates, like test scores where several students might get the same grade.

For example, if three students scored 90 on an exam (ranks 1, 2, and 3), RANK.AVG wouldn’t give the next student a rank of 4. Instead, it would assign an average rank of 2 to all three 90 scorers by solving the following expression:

( (1 + 2 + 3) / 3 )

Then it’d rank the next student as the number 3.

You can enjoy the benefit of this Excel function when ranking a truly large dataset with random numbers.

RANK.AVG formula
RANK.AVG formula

You can use the following formula in the destination cell to get a rank. Of course, you’ll need to adjust the references according to your own worksheet.

=RANK.AVG(B2,$B$2:$B$11,0)

The arguments used in the above formula are as outlined below:

  • B2 is the number being ranked.
  • $B$2:$B$11 is the whole dataset of numbers where the evaluated number exists.
  • 0 is for descending order of ranking, meaning 1 is the topper, 2 is the runner up, and so on.
Calculate value
Calculate value

Now, press Enter to calculate the rank for the selected number.

Copying formula
Copying formula

To rank the rest of the numbers in the dataset, use the fill-down handle to apply the formula to all the cells.

Sorting a rank table
Sorting a rank table

As the rankings aren’t in the top to-bottom order, you can select the Rank column and press Ctrl + Shift + L to activate the Sort & Filter tool.

Then, click on the Sort & Filter drop-down menu in the Rank column and choose the Sort Smallest to Largest option.

Using RANK.AVG for Excel ranking
Using RANK.AVG for Excel ranking

The sorted rank list will show the number 1 at the top, then the number 2, and so on.

This rank function in Excel does skip ranks for ties. When multiple values are tied, RANK.AVG assigns the average rank to each tied value, and the next rank is incremented by the number of tied values.

Rank With Array Formulas

You can use a combination of Excel functions SUMPRODUCT and COUNTIF to rank duplicates. In this process, the ranking sequence won’t skip positions or ranking numbers.

Sample dataset 3

The sample dataset I’m using here to demonstrate this formula of ranking in Excel is as shown above.

Array rank formula in Excel
Array rank formula in Excel

Since I need the first rank in C2, I’d enter the following formula in the same cell:

=SUMPRODUCT((B2<=$B$2:$B$8)/COUNTIF($B$2:$B$8,$B$2:$B$8))
Calculate array formula
Calculate array formula

After pressing the Enter key, I get the rank for the first cell.

Fill down formulas
Fill down formulas

Then, I can easily replicate the formula across column C until the last cell where reference data exists in the adjacent columns to the left using the fill handle.

Conditional Formatting With Rank

You can use Conditional Formatting rules with a formula of rank in Excel to highlight cells with distinctive colors by tiers of ranks.

Find below two RANK formulas and their usage in this context:

Highlight the Top N Values

Sample dataset 4

Suppose, you’ve got the above score sheet for a few students and you’d like to highlight the top 3 performers in the class.

Start New Rule
Start New Rule

Select the scores and click on the Conditional Formatting drop-down menu. Select New Rule from the context menu that opens.

New Formatting Rule
New Formatting Rule

Choose the Use a formula to determine… option in the New Formatting Rule dialog.

Enter the following formula inside the Format values where this formula is true field:

=RANK(B2, $B$2:$B$11) <= 3
Format Cells
Format Cells

Click on the Format button and choose a cell formatting from the Format Cells dialog box. There, you can use the Font, Border, and Fill tabs to create formatting of your own choice. Click OK to save.

Save formatting
Save formatting

Select the OK button on the New Formatting Rule dialog box.

Highlight top values using RANK
Highlight top values using RANK

Excel will highlight the top 3 values of the input dataset.

If you need to highlight more or less than 3 numbers, change this value when creating the Conditional Formatting rule.

Highlight the Bottom N Values

To highlight the bottom 3 performers from the same dataset, use the following formula when you create the Conditional Formatting rule:

=RANK(B2, $B$2:$B$11, 0) >= (COUNT($B$2:$B$11) - 3)
Edit formatting rule
Edit formatting rule

The New Formatting Rule dialog box will be somewhat as shown above.

Highlighted bottom performers
Highlighted bottom performers

Use your own formatting ideas and apply the rule.

Excel will format the bottom 3 values by rank.

Using a PivotTable

If you often use a PivotTable to extract insights from your datasets, you should know that this tool also offers a built-in ranking system.

📒 Read More: 101 Advanced Pivot Table Tips And Tricks You Need To Know

Sample dataset 5

Let’s say you want to generate a PivotTable using the above database and rank the sales agent by their sales values.

PivotTable from table or range
PivotTable from table or range

Select the input dataset and click on the PivotTable command inside the Tables block of the Insert tab.

The PivotTable from table or range dialog will open.

Click on the Existing Worksheet option and select a cell on the active worksheet where you want to get the PivotTable.

The PivotTable Fields navigation pane will show up on the right.

Setup PivotTable
Setup PivotTable

Drag the Sales Agent to Rows and Sales to Values box.

This should form the PivotTable needed for ranking.

Populate duplicate field
Populate duplicate field

Now, again drag and drop Sales into the Values box to create a duplicate of the initial Sum of Sales column in the PivotTable. The duplicate will automatically be named to Sum of Sales2.

Rank in PivotTable
Rank in PivotTable

Right-click on the Sum of Sales2 column and hover the cursor over the Show Values As menu.

Choose the Rank Largest to Smallest option in the overflow menu.

Show Values As
Show Values As

Click OK on the Show Values As dialog box.

Ranking in Excel using PivotTable
Ranking in Excel using PivotTable

The Sum of Sales2 column will change to a list of ranks of sales agents according to their sales achievements.

Using Data Analysis Toolpak

The Rank and Percentile function in the Data Analysis Toolpak also allows you to rank numbers in a dataset. However, this tool shows a structured graphical user interface so you don’t need to create any formula.

The Data Analysis Toolpak should be in the Analysis commands block of the Data tab. If you don’t see it there, go through this quick Excel tutorial:

📒 Read More: How to Install Data Analysis Toolpak in Microsoft Excel

Data analysis toolpak
Data analysis toolpak

Once you’ve got this tool, go to the target worksheet and click on the Data Analysis command inside Analysis block of the Data tab on the ribbon.

The Data Analysis Toolpak will open. Scroll down and select the Rank and Percentile function.

Click OK to run the function.

Output Range
Output Range

On the Rank and Percentile dialog box, the Input Range should be set to the numbers you want to rank in the dataset.

Now, select the Output Range field and select a cell in the active worksheet for populating the rank table.

Ranking in Excel using Data Analysis Toolpak
Ranking in Excel using Data Analysis Toolpak

Click OK to perform the analysis and populate the table as shown above.

Using Excel VBA

If you don’t wish to go through the challenges of creating and perfecting a rank formula in Excel, you can use Excel VBA to automate the number ranking process.

First, practice creating a VBA macro using a VBA script by going through this Excel tutorial:

📒 Read More: How To Use The VBA Code You Find Online

Now, use the following script to create a macro:

VBA script 1
Sub RankNumbers()
    Dim inputRange As Range
    Dim destRange As Range
    Dim rankColumn As Range
    Dim lastRow As Long
    
    ' Prompt user to select input range
    On Error Resume Next
    Set inputRange = Application.InputBox("Select the input range:", Type:=8)
    On Error GoTo 0
    
    If inputRange Is Nothing Then
        MsgBox "Input range selection canceled. Exiting."
        Exit Sub
    End If
    
    ' Prompt user to select destination cell range
    On Error Resume Next
    Set destRange = Application.InputBox("Select the destination cell range:", Type:=8)
    On Error GoTo 0
    
    If destRange Is Nothing Then
        MsgBox "Destination range selection canceled. Exiting."
        Exit Sub
    End If
    
    ' Calculate ranks using RANK.EQ
    lastRow = inputRange.Rows.Count
    Set rankColumn = destRange.Resize(lastRow, 1)
    
    rankColumn.Formula = "=RANK.EQ(" & inputRange.Address & "," & inputRange.Address & ",0)"
    
    ' Convert formulas to values
    rankColumn.Value = rankColumn.Value
    
    MsgBox "Ranking completed!"
End Sub

This macro will show the following prompt boxes so you can rank values visually:

Input of range for ranking
Input of range for ranking
  • Input range
Destination for ranks
Destination for ranks
  • Output range
Ranking done
Ranking done
  • Ranked values

Conclusions

So far, you’ve explored how to rank in Excel using various built-in functions, user interface tools, and Excel VBA.

You can choose the method that suits you depending on your Excel expertise level and the scenario you’re trying to solve.

You can use the comment box to acknowledge this tutorial or submit suggestions.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃