The formula
Top 3 values (any Excel)Excel
=LARGE(B2:B7,{1;2;3})In D2, spills: [420], [310], [275]
Top 3 names (Excel 365)Excel
=INDEX(SORTBY(A2:A7,B2:B7,-1),SEQUENCE(3))In F2, spills: [Ana], [Dev], [Eli]
Top 3 names and values (Excel 365)Excel
=INDEX(SORTBY(A2:B7,B2:B7,-1),SEQUENCE(3),{1,2})In H2, spills: [Ana, 420], [Dev, 310], [Eli, 275]
Google SheetsGoogle Sheets
=SORTN(A2:B7,3,0,2,FALSE)In K2, spills: [Ana, 420], [Dev, 310], [Eli, 275]
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Sales | ||||||||||
| 2 | Ana | 420 | 420 | Ana | Ana | 420 | Ana | 420 | ||||
| 3 | Ben | 150 | 310 | Dev | Dev | 310 | Dev | 310 | ||||
| 4 | Cara | 90 | 275 | Eli | Eli | 275 | Eli | 275 | ||||
| 5 | Dev | 310 | ||||||||||
| 6 | Eli | 275 | ||||||||||
| 7 | Fay | 130 |
How it works
- LARGE(B2:B7,{1;2;3})
- 1st, 2nd and 3rd largest values.
- SORTBY(A2:B7,B2:B7,-1)
- The table sorted by sales, high to low.
- SEQUENCE(3)
- Rows 1 to 3 of the sorted table. Change 3 to any N.
- SORTN(A2:B7,3,0,2,FALSE)
- Sheets: top 3 rows, ties mode 0, by column 2, descending.
Common errors and fixes
Ties show the same value twice?
That's expected: LARGE returns duplicates. The SORTBY version returns each row once.
Why does the formula return #REF!?
N is larger than the number of rows.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.