Analyzing Data with SMALL and LARGE in Google Sheets

August 7th, 2026

MIN and MAX answer one question: what is the extreme value? Real analysis often needs more — second-highest sale, third-lowest score, top 5 revenue lines. That is what SMALL and LARGE are for: they return the nth smallest or largest value in a range without sorting the whole sheet. This guide shows how to use them, how to build top-N lists with INDEX or SORTN, and when RANK is a better fit.

SMALL and LARGE in one sentence each

• SMALL(range, k) — the kth smallest value in the range (k = 1 is the minimum).
• LARGE(range, k) — the kth largest value in the range (k = 1 is the maximum).

=SMALL(B2:B20, 1)
=SMALL(B2:B20, 2)
=LARGE(B2:B20, 1)
=LARGE(B2:B20, 3)

If scores in B2:B20 are 12, 8, 15, 8, 20, then SMALL(..., 1) is 8, SMALL(..., 2) is also 8 (ties keep their place in the ordered list), LARGE(..., 1) is 20, and LARGE(..., 3) is 12.

Contrast with MIN and MAX

=MIN(B2:B20)
=SMALL(B2:B20, 1)
=MAX(B2:B20)
=LARGE(B2:B20, 1)

These pairs are equivalent. Prefer MIN/MAX when you only need the extreme — shorter, clearer, and what most readers expect. Switch to SMALL/LARGE as soon as you need the 2nd, 3rd, or kth value, or when k itself is a cell input.

=LARGE(B2:B20, E1)

Put 1, 2, or 5 in E1 and the formula becomes a reusable “show me the kth best” control without rewriting anything.

Pulling a single nth extreme

Second-highest value

=LARGE(C2:C100, 2)

Third-lowest value

=SMALL(C2:C100, 3)

Avoid errors when k is too large

If k exceeds the number of numeric values, SMALL/LARGE return #NUM!. Guard with COUNT:

=IF(E1>COUNT(C2:C100), "n/a", LARGE(C2:C100, E1))

Top-N lists without sorting the sheet

You often want not just the 1st largest, but a short table of the top 5. Three common patterns:

Pattern 1 — LARGE with a fixed k column

In E2:E6 put 1 through 5, then in F2:

=LARGE($C$2:$C$100, E2)

Fill down. Column F shows the five largest values in order. This does not return the matching names yet — only the metrics.

Pattern 2 — INDEX + MATCH with LARGE (value → row label)

If names are in A2:A100 and scores in C2:C100:

=INDEX($A$2:$A$100, MATCH(LARGE($C$2:$C$100, 1), $C$2:$C$100, 0))

That returns the name of the top scorer. For 2nd, 3rd, change the 1 to 2 or 3, or reference a helper column of ranks. Caveat: with tied scores, MATCH finds the first occurrence only — duplicates need a more careful formula (unique keys, RANK helper, or SORTN).

Pattern 3 — SORTN (cleanest for full rows)

SORTN returns the first n rows after sorting:

=SORTN(A2:C100, 5, 0, 3, FALSE)

This keeps columns A–C, takes 5 rows, sorts by column 3 (the third column of the range) descending (FALSE). You get top 5 full records in one formula — usually better than hand-rolling LARGE + INDEX when you need entire rows.

Bottom 5 ascending:

=SORTN(A2:C100, 5, 0, 3, TRUE)

Bottom-N and “almost extremes”

SMALL is the natural tool for lowest values: worst response times, smallest order sizes, earliest dates (dates are numbers under the hood).

=SMALL(D2:D50, 1)
=SMALL(D2:D50, 2)

For “all values except the max,” or trimmed extremes, you can combine with filters — but start with SMALL/LARGE for single nth picks; they stay readable.

Working with ties

Ordered statistics count multiplicity. In {10, 10, 9}:

• LARGE(range, 1) → 10
• LARGE(range, 2) → 10
• LARGE(range, 3) → 9

Both top slots are 10. If you need distinct values only, wrap the range in UNIQUE first:

=LARGE(UNIQUE(C2:C100), 2)

That second-largest distinct value may differ from the second-largest with ties.

Optional: RANK for position, not value

RANK answers a different question: given a value, where does it sit in the list?

=RANK(C2, $C$2:$C$100, 0)

0 (or omitted in the usual default) ranks descending — largest value gets rank 1. Use RANK on a helper column when every row needs its standing. Use LARGE/SMALL when you start from k and want the value (or a lookup from that value). They complement each other:

| Question | Function | | --- | --- | | What is the 3rd highest value? | LARGE(range, 3) | | What is the 3rd lowest value? | SMALL(range, 3) | | What rank is this row’s value? | RANK(value, range, …) | | What are the top 5 full rows? | SORTN(...) | | What is only the max/min? | MAX / MIN |

Practical examples

Top 3 product revenues

Revenues in D2:D200:

=LARGE(D2:D200, 1)
=LARGE(D2:D200, 2)
=LARGE(D2:D200, 3)

Threshold: is this in the top 10% by value?

Approximate with LARGE and COUNT:

=C2>=LARGE($C$2:$C$100, ROUND(COUNT($C$2:$C$100)*0.1, 0))

For formal percentile cutoffs, prefer PERCENTILE (see a dedicated percentile guide). LARGE is fine for “top k items” thinking.

Dynamic k from a dropdown

Name a cell k and write:

=LARGE(Scores, k)

Analysts can change k without touching the formula — handy on dashboards.

Common mistakes

• k = 0 or k negative. Invalid; SMALL/LARGE need k ≥ 1.
• k larger than the count of numbers. #NUM! — non-numeric cells are ignored in the count of eligible values.
• Text that looks like numbers. If a column is stored as text, SMALL/LARGE may skip those cells. Coerce with VALUE or fix types upstream.
• Using LARGE when you needed a full sorted table. For many rows, SORT or SORTN is clearer than dozens of LARGE formulas.
• MATCH + LARGE on ties. Duplicate metrics make INDEX/MATCH return the first match repeatedly; use SORTN or a unique rank helper.

Going further

SMALL and LARGE turn “what are the extremes?” into “what is the nth extreme?” — a small shift that unlocks leaderboards, exception lists, and flexible k controls. Use MIN/MAX for simple endpoints, LARGE/SMALL for ordered picks, SORTN for full top-N tables, and RANK when every row needs a standing. Full references: SMALL, LARGE, INDEX, SORTN.