Excel to Python: RANK Function - A Complete Guide | Mito
Home Icon






How to Use Excel's RANK Function in Pandas

Excel's RANK function assigns a rank to a number from a list. The rank can be either in ascending or descending order and can handle ties and missing values.

This page demonstrates how to replicate the RANK function from Excel in Python using pandas

In pandas, the `rank()` method offers similar functionality to Excel's RANK function. Below are some common implementations and how they correspond to Excel's operations.

Excel's RANK formula: ranks numbers in ascending order by default, gives ties the same ranking, let's the presence of ties affect subsequent rankings, and ignores blank cells

To implement this logic in Pandas, use the `rank()` function.

df['Rank'] = df['Column1'].rank(ascending=False)

To flip the order of the ranking, set the `ascending` parameter to True, or omit it.

df['Rank'] = df['Column1'].rank()

By default, NaN values are assigned a rank of NaN in pandas, similar to how Excel's RANK treats blank cells.

However, if you wanted to include the NaN values in the ranking so that they count as the minimum value in the data, you can set the na_option parameter to 'bottom'. Or, if you wanted to if you wanted the NaN values to count as the maximum in the data, you can set the na_option parameter to 'top'.

df['Rank'] = df['Column1'].rank(ascending=False, na_option='bottom')

While ranking data in pandas can be intuitive, there are pitfalls that users may encounter. Here are some common mistakes and their solutions.

By default, pandas ranks in ascending order. If you want to rank in descending order, you must explicitly set the `ascending` parameter.

In Excel, the default if you dont' set the order parameter is `descending`. It is reverse in Pandas, so you must set the `ascending` parameter to False to get the same behavior.

Ensure you set the `ascending` parameter appropriately in pandas.

df['Rank_Desc'] = df['Column1'].rank(ascending=False)

Pandas offers different methods to handle ties. Not specifying the method can lead to unexpected ranking behavior.

In Excel, ties are ranked by giving the same rank to duplicate values and adjusting subsequent ranks.

Ensure you set the `method` parameter in pandas to handle ties as desired.

df['Rank_Ties'] = df['Column1'].rank(method='min')

In pandas, ranks start from 1, similar to Excel. If you're expecting 0-based indexing, this can cause confusion.

df['Rank'] = df['Column1'].rank()

In pandas, NaN values in the data result in NaN ranks. This is similar in Excel to how blank cells or #N/A values are given #N/A ranks.

The RANK function in Excel provides the rank of a number within a list of numbers.

By default, Excel's RANK formula: ranks numbers so that smallest number has the largest rank, gives ties the same ranking, let's the presence of ties affect subsequent rankings, and ignores blank cells

=RANK(number, ref, [order])

RANK Excel Syntax

ParameterDescriptionData Type
numberThe number whose rank you want to find.number
refAn array or range of numbers to find the rank within.range
order(Optional) Specify how to rank the number. Use 0 for descending and anything else for ascending.number


=RANK(3, A1:A10})Returns the rank of 3 in the range A1: A10.number between 1 and 10

Don't re-invent the wheel. Use Excel formulas in Python.

Install Mito

Don't want to re-implement Excel's functionality in Python?

Automate analysis with Mito