How to make a ranked leaderboard with a formula in Excel
Excel formula
=SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1)Add a rank column with HSTACK and sort by it: =SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1). Tied scores share a rank.
- Result on the example
- a 8-row list that spills from D2 in
D2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
RANK.EQ ranks every team's points against the whole list at once, giving a column of ranks. HSTACK puts that column in front of the teams and points, and SORT orders the result by its first column. Foxes and Hawks both have 92, so both are 2nd and the next team is 4th, the usual competition ranking.
=SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1)Result{8 rows × 3 cols} 1, "Lynx", 95, 2, …
B2:B98 rows × 1 col: 87, 92, 78, 92, …A range: 8 cells in one column.
RANK.EQ(B2:B9,B2:B9){8 rows × 1 col} 4, 2, 7, 2, …RANK: Rank of a number in a list. 0 or omitted = largest is 1.- number
B2:B98 rows × 1 col: 87, 92, 78, 92, … - ref
B2:B98 rows × 1 col: 87, 92, 78, 92, …
- number
A2:B98 rows × 2 cols: "Owls", 87, "Foxes", 92, …A range: 8 rows × 2 columns.
HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9){8 rows × 3 cols} 4, "Owls", 87, 2, …HSTACK: Places ranges side by side.- array1
RANK.EQ(B2:B9,B2:B9){8 rows × 1 col} 4, 2, 7, 2, … - array2
A2:B98 rows × 2 cols: "Owls", 87, "Foxes", 92, …
- array1
SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1){8 rows × 3 cols} 1, "Lynx", 95, 2, …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9){8 rows × 3 cols} 4, "Owls", 87, 2, … - sort_column
1
- range
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1,TRUE)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORTBY(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),B2:B9,-1)Excel only | spills 8 × 3 | Sort by the points instead of the rank column. Same order. |
| =TAKE(SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1),3)Excel only | spills 3 × 3Different result | Keep only the top three rows. Only Lynx, Foxes and Hawks. |
Common mistakes
- Ties are listed in the order they appear in the data. If ties need a fair order, sort by a second column:
SORT(…,{1,2},{1,1})puts tied teams in A to Z order. - In Google Sheets, wrap the whole formula in ARRAYFORMULA so RANK.EQ ranks every row, as in the Sheets version above.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Make a ranked leaderboard with a formula
D2, return a leaderboard with three columns: the rank, the team and its points, best rank first. Tied teams share a rank.Questions
How do I rank without gaps after a tie (1, 2, 2, 3)?
Rank against the unique scores instead: =XMATCH(B2:B9,SORT(UNIQUE(B2:B9),,-1)) gives a dense rank.
How do I rank lowest first, as for times or golf scores?
Give RANK.EQ an order of 1: RANK.EQ(B2:B9,B2:B9,1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.