# How to make a ranked leaderboard with a formula in Excel

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.

**Excel:** `=SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1,TRUE))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Team | Points |  | Rank | Team | Points |
| Owls | 87 |  |  |  |  |
| Foxes | 92 |  |  |  |  |
| Bears | 78 |  |  |  |  |
| Hawks | 92 |  |  |  |  |
| Wolves | 85 |  |  |  |  |
| Otters | 78 |  |  |  |  |
| Lynx | 95 |  |  |  |  |
| Crows | 81 |  |  |  |  |

Formula in D2: `=SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1)`

Result (spills 8 × 3): 1, Lynx, 95; 2, Foxes, 92; 2, Hawks, 92; 4, Owls, 87; 5, Wolves, 85; 6, Crows, 81; 7, Bears, 78; 7, Otters, 78

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

## Other ways

- `=SORTBY(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),B2:B9,-1)`: Sort by the points instead of the rank column. Same order.
- `=TAKE(SORT(HSTACK(RANK.EQ(B2:B9,B2:B9),A2:B9),1),3)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/make-a-ranked-leaderboard