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.

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

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

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 will sort the input data as per the supplied sorting configurations.

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.

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.

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

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.

Hit Enter to populate the entire sorted dataset.

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.

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.

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.

Hit Enter to calculate the rank for C2.

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.

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.

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.

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

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

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.

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.

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

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

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.

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.

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

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))

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

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

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.

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

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

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.

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

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)

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

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

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

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.

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

This should form the PivotTable needed for ranking.

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.

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.

Click OK on the Show Values As dialog box.

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

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.

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.

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:

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 range

• Output range

• 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.