How to Use LARGE/SMALL in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner · 5 min read · includes 2 free exercises

In short: LARGE returns the nth largest value in a data set and SMALL returns the nth smallest. The formulas are =LARGE(data, n) and =SMALL(data, n).

What LARGE and SMALL do

MAX and MIN give you the top and bottom value. LARGE and SMALL go further: you pick the rank. Use them for the second-highest score, the third-cheapest price, or a top-5 list built one cell at a time.

Syntax

=LARGE(data, n)
=SMALL(data, n)

Google's help pages show that n can also come from a cell, as in LARGE(A2:B100, C2).

A step-by-step example

Here are five test scores in cells A1:B6.

AB
1NameScore
2Ann82
3Ben64
4Cara91
5Dev77
6Eli58

To find the second-highest score:

=LARGE(B2:B6, 2)

This returns 82. Here is why:

  1. Sorted from largest to smallest, the scores are 91, 82, 77, 64, 58.
  2. The second value in that order is 82.

For the lowest scores, SMALL counts from the bottom. In the same list, =SMALL(B2:B6, 3) returns 77 (58, 64, then 77).

Build a top list

Put the ranks 1, 2 and 3 in a helper column and point n at those cells. Copying one formula down gives you the top three scores without any sorting.

Common mistakes

Try it yourself

Use the score table above (A1:B6). Type your formula into the box, starting with =, then press Check.

Exercise 1: Second highest

Write a formula that returns the second largest score in B2:B6.

Hint: the second argument is the rank, 2.

Exercise 2: Third lowest

Write a formula that returns the third smallest score in B2:B6.

Hint: SMALL counts from the bottom.

What to learn next

Syntax and rules in this guide were checked against Google's LARGE/SMALL help page. See all guides.