LARGE and SMALL in Excel: how to use it
LARGE gives the second, third or nth highest value. SMALL does the same from the bottom. Ideal for a top 3.
Try the first exercises for freeIn the lesson LARGE, SMALL and RANK.EQ
What do LARGE and SMALL do?
LARGE gives the nth highest value from a list: with 1 the highest, with 2 the second highest, with 3 the third. SMALL does the same from the bottom: with 1 the lowest.
That's how you make a top 3, or see the second-best score, something MAX and MIN can't do. RANK.EQ turns it around: you give a number, and Excel tells you which place it's in.
When do you use LARGE and SMALL?
- The second-highest revenue: =LARGE(C2:C50,2).
- A top 3, with 1, 2 and 3 in a helper column: =LARGE($C$2:$C$50,E2).
- The fastest time after the winner, where the lowest is the best: =SMALL($B$2:$B$30,2).
- Each salesperson's place in the ranking: =RANK.EQ(C2,$C$2:$C$50).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The nth highest value.
Filled in: =LARGE(C2:C6,2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
LARGE | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | LARGE |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
array | The numbers you choose from. | C2:C6 |
k | Which one: 1 is the highest or the lowest, 2 the second, and so on. | 2 |
, | A comma separates the arguments from each other. | , |
LARGE and SMALL step by step
E1 contains =LARGE(C2:C6,2). The second highest revenue. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1 and type LARGE with a bracket.
=LARGE( - Select the numbers, C2 to C6, and type a comma.
=LARGE(C2:C6, - Type which one you want: 2 for the second highest.
=LARGE(C2:C6,2 - Close the bracket and press Enter: 980, because only 1200 is higher.
=LARGE(C2:C6,2)
More examples
The second lowest
E1 contains =SMALL(C2:C6,2). Only 75 is lower than 340.
The place in the ranking
E1 contains =RANK.EQ(C5,C2:C6). 980 is in second place: only 1200 is higher.
The lowest gets place 1
D1 contains =RANK.EQ(B3,B2:B5,1). With 1 as the third argument, the lowest value gets place 1, like the fastest time in a race.
Making a top 3
Put 1, 2 and 3 in a helper column and refer to it. That way you can simply fill the formula down.
Where it often goes wrong
- If k is larger than the number of values, you get #NUM!.
Practise this in the lesson LARGE, SMALL and RANK.EQ. The first exercises are free.
Frequently asked questions
How do I work out a ranking?
With RANK.EQ.
Read next
ROUNDCEILING and FLOORMOD and INTMEDIAN and MODESEQUENCE and RANDBETWEEN