Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

RANK and PERCENTILE Formulas in pandas

Match RANK.EQ with method='min' and ascending=False, rank within groups in one call, and replace LARGE, PERCENTILE and QUARTILE with nlargest, quantile and qcut.

Ranking is where two implementations of the same report most often disagree, because the interesting part is not the ordering but what happens to ties — and Excel has three ranking functions with different answers. pandas puts the choice in one argument, which makes the decision explicit rather than a consequence of which formula somebody reached for. This guide is part of Excel Formula Equivalents in pandas.

Three answers to the same tie With two values tied for first, the minimum method gives both rank one and skips rank two, the average method gives both one and a half, and dense ranking gives both rank one and continues at two. method='min' matches RANK.EQ 1, 1, 3 next rank skipped method='average' matches RANK.AVG 1.5, 1.5, 3 pandas default method='dense' no formula equivalent 1, 1, 2 no gaps the default is not the one the formula means

Prerequisites

Bash
pip install pandas openpyxl
Python
import pandas as pd

reps = pd.DataFrame({
    "Rep": ["Ana", "Ben", "Cara", "Dev", "Eve", "Fin"],
    "Region": ["North", "South", "North", "West", "South", "North"],
    "Revenue": [24500.0, 15320.0, 24500.0, 9800.0, 31200.0, 15320.0],
})

Two pairs of tied values — Ana with Cara, and Ben with Fin — which is exactly what makes a ranking interesting.

RANK, and choosing what a tie means

Python
# =RANK(C2, C:C) / =RANK.EQ — ties share the lowest rank, the next is skipped
reps["Rank_EQ"] = reps["Revenue"].rank(method="min", ascending=False).astype(int)

# =RANK.AVG — ties share the average of the ranks they span
reps["Rank_AVG"] = reps["Revenue"].rank(method="average", ascending=False)

# Dense: no gaps after a tie — no Excel equivalent without a helper column
reps["Rank_Dense"] = reps["Revenue"].rank(method="dense", ascending=False).astype(int)

print(reps.sort_values("Revenue", ascending=False))

ascending=False is required to match Excel's default of ranking largest first; pandas ranks smallest first unless told otherwise, which is the single most common source of an inverted ranking. method="min" is what RANK and RANK.EQ do, and it is not the pandas default — leaving the argument out gives averaged ranks and half-integer values that look like a bug to anyone comparing against the spreadsheet.

method="dense" has no simple formula equivalent and is often what a report actually wants: two first places are followed by second, not by third.

Ranking within a group

Ranking inside each group Grouping by region and ranking within each group produces a position per row that aligns back to the original frame, replacing an array formula that counts how many rows in the same category score higher. grouped ranking groupby('Region') one ranking per region rank(method='min') position within the group aligned to rows filter on rank == 1 an array formula in Excel; one call here

Ranking each region separately is an array formula in Excel and one call here.

Python
reps["Rank_In_Region"] = (
    reps.groupby("Region")["Revenue"]
        .rank(method="min", ascending=False)
        .astype(int)
)
print(reps.sort_values(["Region", "Rank_In_Region"]))

The result aligns back to every row, so the frame keeps its shape — the same transform-style behaviour that makes grouped totals easy. Filtering to the top performer per region is then a comparison rather than a sort-and-slice:

Python
top_per_region = reps[reps["Rank_In_Region"] == 1]
print(top_per_region[["Region", "Rep", "Revenue"]])

Using the rank rather than groupby().head(1) keeps every member of a tie, which is usually correct for a "top performer" list and is the sort of detail that only surfaces when two people genuinely tie.

LARGE, SMALL and the top N

Python
# =LARGE(C:C, 2) — the second largest value
print(reps["Revenue"].nlargest(2).iloc[-1])

# The top three rows, not just the values
print(reps.nlargest(3, "Revenue"))

# =SMALL(C:C, 1)
print(reps["Revenue"].nsmallest(1).iloc[0])

nlargest on a DataFrame returns whole rows, which is what a report needs and what LARGE cannot give — the formula returns a value, and getting the name beside it requires an INDEX/MATCH back into the column. Ties at the boundary are kept or dropped according to the keep argument, with keep="all" returning every row tied at the cutoff.

PERCENTILE, QUARTILE and PERCENTRANK

Python
# =PERCENTILE.INC(C:C, 0.9)
print(reps["Revenue"].quantile(0.9))

# =QUARTILE(C:C, 1) and =MEDIAN(C:C)
print(reps["Revenue"].quantile([0.25, 0.5, 0.75]))

# =PERCENTRANK.INC(C:C, C2) — each row's position as a proportion
reps["Percentile"] = reps["Revenue"].rank(pct=True)

# Assign quartile labels in one call
reps["Quartile"] = pd.qcut(reps["Revenue"], 4, labels=["Q1", "Q2", "Q3", "Q4"])
print(reps[["Rep", "Revenue", "Percentile", "Quartile"]])

quantile accepts a list and returns all the requested percentiles in one pass, which is the five-number summary in a line. qcut is the one with no formula equivalent: it splits the data into equal-sized buckets and labels each row, where the spreadsheet version needs a PERCENTILE call per boundary plus a nested IF to assign the label.

Reproducing a leaderboard exactly

Putting it together, a ranked report with ties handled deliberately and a share-of-total column:

Python
board = reps.sort_values("Revenue", ascending=False).copy()
board["Rank"] = board["Revenue"].rank(method="min", ascending=False).astype(int)
board["Share"] = board["Revenue"] / board["Revenue"].sum()
board["Cumulative"] = board["Share"].cumsum()
board["Gap_To_Top"] = board["Revenue"].max() - board["Revenue"]

print(board[["Rank", "Rep", "Revenue", "Share", "Cumulative", "Gap_To_Top"]].to_string(index=False))

Each of those columns is a separate formula in a spreadsheet, and three of them reference an absolute range that has to be maintained as rows are added. Here they are four expressions over the same frame, and adding a row changes nothing.

Ranking on more than one column

A leaderboard usually has a tie-break rule — highest revenue, then most units, then alphabetically — and expressing it in a spreadsheet means a composite helper column with weights chosen so the parts cannot interfere. pandas ranks a sorted frame instead, which states the rule directly.

Python
ordered = reps.sort_values(
    ["Revenue", "Rep"],
    ascending=[False, True],
).reset_index(drop=True)
ordered["Position"] = ordered.index + 1
print(ordered[["Position", "Rep", "Revenue"]])

Sorting by the tie-break columns and numbering the result gives a strict ordering with no ties at all, which is what a published leaderboard normally wants. When genuine ties should remain visible, keep the rank column alongside the position so the report can show both — Rank for the sporting answer and Position for the row order.

The distinction matters more than it sounds. A "top ten" built from row order silently drops the eleventh person who tied for tenth; one built from a rank includes them. Deciding which is intended is a business question, and having both columns available makes it one somebody can answer.

Percentiles on grouped data

Comparing each row against its own group's distribution — a rep against their region rather than the company — is another array formula in Excel and a transform here.

Python
reps["Region_Percentile"] = (
    reps.groupby("Region")["Revenue"].rank(pct=True).round(3)
)
reps["Region_Median"] = reps.groupby("Region")["Revenue"].transform("median")
reps["Above_Region_Median"] = reps["Revenue"] > reps["Region_Median"]

print(reps[["Rep", "Region", "Revenue", "Region_Percentile", "Above_Region_Median"]])

The median-per-group column is the useful one in practice, because a percentile on a group of three rows is not a meaningful statistic while "above or below the regional median" is. Guarding for small groups is worth doing explicitly rather than letting the report imply precision it does not have:

Python
sizes = reps.groupby("Region")["Revenue"].transform("size")
reps.loc[sizes < 5, "Region_Percentile"] = pd.NA

Blanking a statistic that the sample cannot support is the sort of judgement a spreadsheet makes hard and a script makes trivial, and it is the same instinct behind the checks in Validate an Excel Report Before Sending It.

Common pitfalls

SymptomCauseFix
Ranks are invertedpandas ranks ascending by defaultPass ascending=False to match Excel
Ranks come out as 1.5, 3.5method="average" is the defaultPass method="min" to match RANK.EQ
Ranks differ from the sheet after a tieExcel skips the next rank; dense does notChoose min or dense deliberately
qcut raises about duplicate edgesToo many identical values for that many bucketsPass duplicates="drop", or use fewer buckets
Percentiles differ slightly from ExcelPERCENTILE.EXC uses a different definitionCompare against PERCENTILE.INC, or accept the difference
Ranking includes missing valuesNaN is ranked last by defaultPass na_option="keep" to leave them NaN

Performance and scale

Ranking 200,000 rows Excel's RANK scans the whole column once per row, while a pandas rank sorts the column once and nlargest avoids the full sort entirely when only the top rows are needed. RANK filled down scan per row Series.rank one sort nlargest(10) partial selection relative cost for a top-ten list, do not sort the whole frame

Ranking sorts, so it costs more than a sum but far less than the Excel equivalent, which for RANK is a scan of the whole column per row. On 200,000 rows a pandas rank is a fraction of a second; the formula version is the kind of thing that makes a workbook take a minute to recalculate.

nlargest is worth preferring over a full sort when only the top few rows are needed — it uses a partial selection rather than ordering everything, which is a meaningful saving on large frames:

Python
# Better than sort_values(...).head(10) on a large frame
top_ten = reps.nlargest(10, "Revenue")

Grouped ranking costs proportionally more because it sorts within every group, but it is still one pass over the data rather than the nested scanning an array formula performs.

Conclusion

Match Excel by passing both arguments explicitly: ascending=False for the direction and method="min" for RANK.EQ semantics. Beyond that, pandas offers what the spreadsheet does not — dense ranking without gaps, ranking within groups in one call, nlargest returning whole rows rather than values, and qcut assigning quartile labels without a nested IF.

Frequently asked questions

Which rank method matches Excel's RANK? method='min' matches RANK and RANK.EQ: tied values all take the lowest rank in the tie and the next rank is skipped. RANK.AVG corresponds to method='average', which is pandas' default — so the default does not match the formula most people mean.

How do I rank within a group? groupby(key)col.rank(...), which ranks inside each group and aligns back to every row. Excel needs an array formula counting how many rows in the same category exceed the current one.

Does PERCENTILE.INC or PERCENTILE.EXC match quantile? quantile with interpolation='linear' matches PERCENTILE.INC, which is the inclusive definition. PERCENTILE.EXC uses a different formula that pandas has no direct flag for; it is close but not identical at the extremes.

What is the difference between rank(pct=True) and PERCENTRANK? They agree in spirit: both express a rank as a proportion. PERCENTRANK.INC scales so the lowest value is 0 and the highest is 1, while rank(pct=True) divides the rank by the count, so the highest is 1 and the lowest is 1/n.