What it does
LARGE takes two arguments: the range of values and which largest value you want. =LARGE(C2:C20,3) returns the third largest. The range does not need to be sorted; empty cells and text are skipped.
Write rank numbers in a helper column and fill the formula down to build a "top three" list: =LARGE($C$2:$C$20,E2). Add INDEX and MATCH to return the owner of each value. In Microsoft 365 you can also sort the whole list from largest to smallest with SORT.
Equal values are counted separately: in the list 90, 90, 85 both the first and second largest values are 90. So when you look up the owners of equal values with MATCH, both rows return the first match. To count from the smallest instead, use SMALL.
Syntax
=LARGE(array, k)=BÜYÜK(dizi; k)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range to search. Empty cells and text are skipped. |
k | Required | Which largest value to return; 1 is the largest. It cannot be less than 1 or more than the number of values in the range. |
Examples
1List the top three sales and their reps
Column E holds the ranks 1, 2 and 3. Column F finds the sale at each rank, and column G uses MATCH and INDEX to return the rep's name.
=LARGE($C$2:$C$7,E2)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Rank | Sales | Rep | |
| 2 | Emma | West | 48,500 | 1 | 61,200 | Liam | |
| 3 | Liam | North | 61,200 | 2 | 57,800 | Ava | |
| 4 | Olivia | West | 52,300 | 3 | 52,300 | Olivia | |
| 5 | Noah | South | 29,750 | ||||
| 6 | Ava | North | 57,800 | ||||
| 7 | Mia | South | 44,100 |
- F2 (filled down to F4)
=LARGE($C$2:$C$7,E2) - G2 (filled down to G4)
=INDEX($A$2:$A$7,MATCH(F2,$C$2:$C$7,0))
First is Liam (61,200), second Ava (57,800) and third Olivia (52,300).
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Total of the best three months
The annual review adds up the sales of the best three months. Three LARGE functions return the first, second and third largest values, and they are added.
=LARGE(B2:B7,1)+LARGE(B2:B7,2)+LARGE(B2:B7,3)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | Best Three Months | |
| 2 | January | 84,000 | 289,100 | |
| 3 | February | 91,500 | ||
| 4 | March | 78,200 | ||
| 5 | April | 102,300 | ||
| 6 | May | 88,200 | ||
| 7 | June | 95,300 |
April, June and February add up to 289,100. In Microsoft 365, =SUM(LARGE(B2:B7,SEQUENCE(3))) gives the same result.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3LARGE with equal values
Mia and Ella got the same score. LARGE counts equal values separately, so the first and second largest values are the same.
=LARGE($B$2:$B$5,D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Rank | Score | |
| 2 | Mia | 90 | 1 | 90 | |
| 3 | Lucas | 85 | 2 | 90 | |
| 4 | Ella | 90 | 3 | 85 | |
| 5 | Jack | 78 |
- E2 (filled down to E4)
=LARGE($B$2:$B$5,D2)
Ranks 1 and 2 both hold 90; the third largest value is 85. Keep this in mind when you look up the owners of equal scores.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: k is less than 1 or larger than the number of values: the 6th largest was requested from a list of five.
Fix: Keep k within the number of values in the range;
=COUNT(C2:C20)tells you how many there are.- Same name twice
Cause: Two values are equal, and MATCH finds the first matching row for both.
Fix: In Microsoft 365, sort the list with SORTBY, or add a small helper column that breaks the tie.
Good to know
- For the total of the three largest values in Microsoft 365, use
=SUM(LARGE(C2:C20,SEQUENCE(3))); in older versions, add three LARGE functions. - Take k from a cell instead of typing it; to turn a "top 5" into a "top 10" you then change only that cell.
In Nexell
Get Nexell to do it from a sentence
Show the top three sales and the reps who made them in a separate table
Convert to tableBring your sales list into the tool and type this. Nexell puts the three highest sales and their reps into a separate table and shows it in a preview first; once you approve, you download it as .xlsx.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find the top 3 values in Excel?
Type =LARGE($C$2:$C$20,1), =LARGE($C$2:$C$20,2) and =LARGE($C$2:$C$20,3) in three cells, or put the k values in a column and fill one formula down. Return their owners with INDEX and MATCH.
What is the difference between LARGE and MAX?
MAX returns only the largest value. LARGE asks which largest you want; =LARGE(C2:C20,1) equals MAX and =LARGE(C2:C20,2) returns the second largest.