LARGE function
Quick answer
LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.
- Syntax
=LARGE(array, k)- Most common use
=LARGE(B2:B8,1)returns $51,750 (The largest value, same as MAX)
It's the standard way to build a top 3 or top 10 list, and unlike MAX it can reach past the first place. Duplicate values each take their own position.
- Available in
- Every current version
- Category
- Statistical functions
- Returns
- The k-th largest number, or #NUM! if k is less than 1 or more than the number of values.
- Google Sheets
- Also available in Google Sheets.
Syntax
=LARGE(array, k)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range or list of numbers to look in. |
k | Required | Which position to return, counting from the largest (1 = largest). |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =LARGE(B2:B8,1) | $51,750 | The largest value, same as MAX |
| F3 | =LARGE(B2:B8,2) | $48,200 | The second-highest sales total |
| F4 | =SUM(LARGE(B2:B8,{1,2,3})) | $146,750 | An array of k values sums the top three at once |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Rep | Sales | Check | Result | ||
| Ana | $48,200 | Top seller | $51,750 | ||
| Raj | $39,500 | Second place | $48,200 | ||
| Lee | $51,750 | Top three combined | $146,750 | ||
| Kim | $44,100 | ||||
| Mo | $39,500 | ||||
| Ivy | $28,900 | ||||
| Sam | $46,800 |
Practice LARGE
From lesson 34, RANK, LARGE & SMALL. Graded instantly, then re-checked on changed data.
Top three values
F2, return the k-th largest sale using the k in E2. Fill down to F4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NUM! | k is bigger than the number of values in the range, or less than 1. Check the size of the list. |
| The same number appears twice in a top list | Duplicates each take a position, so two reps tied at $39,500 fill places 5 and 6. That's correct. |
| The wrong range after filling down | Lock the array with $, such as $B$2:$B$8, when k comes from a cell that changes row by row. |
Tips
- Put 1, 2, 3 in a helper column and use =LARGE($B$2:$B$8,D2) to build a top-N table you can fill down.
- Pair with XLOOKUP to return the name that goes with each top value.
Questions
How do I find the second highest value in Excel?
Use =LARGE(range,2).
How do I get the name of the top performer?
Look it up from the value: =XLOOKUP(LARGE(B2:B8,1),B2:B8,A2:A8). Ties return the first match.
Lessons that use LARGE
Answers that use LARGE
In other languages
LARGE is K.ESIMO.MAYOR in Spanish Excel and KGRÖSSTE in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .