LARGE and SMALL functions in Excel

LARGE returns the nth largest value in a range and SMALL returns the nth smallest.

MAX gives the top value and MIN the bottom. LARGE and SMALL reach the places in between: the second highest salary, the three best scores, the fifth cheapest quote.

Syntax

=LARGE(array, k)

=SMALL(array, k)

Argument	What it is
`array`	The range of numbers.
`k`	The position wanted: 1 for the largest (or smallest), 2 for the second, and so on.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=LARGE(C2:C9,1)
Result: 92
The largest Maths mark, the same as MAX.
=LARGE(C2:C9,2)
Result: 88
The second largest.
=LARGE(C2:C9,3)
Result: 78
The third largest. Two students have 78, and each takes a place, so the third largest is also 78.
=SMALL(C2:C9,1)
Result: 33
The smallest, the same as MIN.
=SMALL(C2:C9,2)
Result: 45
The second smallest.
=SUM(LARGE(C2:C9,{1,2,3}))
Result: 258
The total of the top three. The curly brackets ask for three positions at once.
=AVERAGE(SMALL(C2:C9,{1,2,3}))
Result: 44
The average of the lowest three.
=LARGE(C2:C9,9)
Result: #NUM!
There are only eight marks, so a ninth does not exist: #NUM!.

How they work

LARGE puts the numbers of the range in order from largest to smallest in its head and returns the one in position k. SMALL does the same from the other end. The range itself is not sorted or changed.

Text and empty cells in the range are ignored.

Ties take up places

LARGE counts positions, not distinct values. In the sample, two students scored 78. The marks in order are 92, 88, 78, 78, 67 and so on. So the 3rd largest is 78 and the 4th largest is also 78.

This matters for questions such as "the second highest different mark". For that, remove the repeats first. In Excel 2021 and Microsoft 365: =LARGE(UNIQUE(C2:C9),2).

A top-three list

In three cells one under another, write LARGE with k as 1, 2 and 3. A neater way that can be copied down is to let the row number supply k:

=LARGE($C$2:$C$9,ROWS($1:1))

ROWS($1:1) is 1 in the first cell and grows by one in each cell below. In Excel 2021 and later, one formula spills the whole list: =LARGE(C2:C9,SEQUENCE(3)).

To show names beside the values, look each value up with INDEX and MATCH. Where ties are possible that returns the first name twice, and the cleaner method is SORT or FILTER.

Several positions at once

A list of positions in curly brackets makes LARGE return several values, which another function can then add or average:

=SUM(LARGE(range,{1,2,3})) is the total of the top three.
=AVERAGE(LARGE(range,{1,2,3,4,5})) is the average of the best five, as used when a result takes "best five of six subjects".

Leaving out the extremes

To average the marks without the single highest and lowest, a method some judging panels use: =(SUM(range)-MAX(range)-MIN(range))/(COUNT(range)-2). The function TRIMMEAN does something similar by percentage.

The nth smallest that meets a condition

With FILTER, a condition is easy: =SMALL(FILTER(C2:C9,B2:B9="8A"),2) is the second lowest Maths mark in class 8A.

LARGE or sorting?

If you want to see the list in order, sort it, or use the SORT function. Use LARGE and SMALL when a formula needs one particular place, or when the original order of the data must stay untouched.

Common mistakes

#NUM! when k is larger than the number of values, or is zero or negative.
Expecting ties to share a place.
Mixing up the two: for "third cheapest" you want SMALL, not LARGE.

In Google Sheets

LARGE and SMALL are the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.