LARGE and SMALL in Excel - Cell-by-Cell Formula Trace

Sample Data

This table shows a list of scores in cells A2 to A6. Column B contains formulas to find the 2nd largest and 3rd smallest scores.

Cell	Value
A1	Scores
A2	50
A3	80
A4	70
A5	90
A6	60
B1	Formula
B2	=LARGE(A2:A6, 2)
B3	=SMALL(A2:A6, 3)

Formula Trace

=LARGE(A2:A6, 2)

Step 1: A2:A6

Step 2: Sort values in descending order

Step 3: Select 2nd value in sorted list

Cell Reference Map

    A      B
1 Scores  Formula
2  50     -> LARGE(A2:A6, 2)
3  80     -> SMALL(A2:A6, 3)
4  70
5  90
6  60

The formulas in B2 and B3 refer to the range A2:A6 which contains the scores.

Result

    A      B
1 Scores  Formula
2  50     80
3  80     70
4  70
5  90
6  60

Cell B2 shows 80, the 2nd largest score. Cell B3 shows 70, the 3rd smallest score.

Sheet Trace Quiz - 3 Questions

What does the formula =LARGE(A2:A6, 2) return?

A 70

B 80

C 90

D 60

Key Result

LARGE(range, k) returns the kth largest value; SMALL(range, k) returns the kth smallest value.