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.

## 0 Comments