📎 This article includes 1 downloadable practice file ↓
In this article
LARGE returns the k-th largest value; SMALL returns the k-th smallest. Together they build top-N lists in any version.
Syntax
=LARGE(array, k)| Argument | What it means |
|---|---|
k |
1 = largest, 2 = second largest… |
Examples
Example 1
=LARGE(E2:E500, 1)Largest sale (same as MAX).
Example 2
=LARGE(E$2:E$500, ROWS(E$2:E2))Fill down 10 rows for a top-10 list.
Power combo
=INDEX(B:B, MATCH(LARGE(E:E, 2), E:E, 0))Customer with the second-largest sale (ties return the first match).
LARGE in practice
Where you will use it
- Top 3 sales
- Second highest score
- Sum of the top 5: SUM(LARGE(range,{1,2,3,4,5}))
Worked example
| k | Formula | Result |
|---|---|---|
| 1 | =LARGE({45000,62000,38000,71000,52000},A2) | 71000 |
| 2 | =LARGE({45000,62000,38000,71000,52000},A3) | 62000 |
| 3 | =LARGE({45000,62000,38000,71000,52000},A4) | 52000 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Duplicates | Ties return the same value twice |
| k larger than count | Gives #NUM! |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Statistics basics22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 34 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong