Skip to content

Excel function · Statistics

Excel LARGE function

LARGE returns the k-th largest value in a range: the top three sales, the second most expensive quote, the total of the best three months. k = 1 is the largest value and gives the same result as MAX.

In Turkish Excel: BÜYÜK

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

English Excel=LARGE(array, k)
Turkish Excel=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

ArgumentRequiredDescription
arrayRequiredThe range to search. Empty cells and text are skipped.
kRequiredWhich 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.

F2fx=LARGE($C$2:$C$7,E2)
List the top three sales and their reps: example table
ABCDEFG
1RepRegionSalesRankSalesRep
2EmmaWest48,500161,200Liam
3LiamNorth61,200257,800Ava
4OliviaWest52,300352,300Olivia
5NoahSouth29,750
6AvaNorth57,800
7MiaSouth44,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.

D2fx=LARGE(B2:B7,1)+LARGE(B2:B7,2)+LARGE(B2:B7,3)
Total of the best three months: example table
ABCD
1MonthSalesBest Three Months
2January84,000289,100
3February91,500
4March78,200
5April102,300
6May88,200
7June95,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.

E2fx=LARGE($B$2:$B$5,D2)
LARGE with equal values: example table
ABCDE
1StudentScoreRankScore
2Mia90190
3Lucas85290
4Ella90385
5Jack78
  • 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

Type this in Nexell

Show the top three sales and the reps who made them in a separate table

Convert to table

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

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free