Wauvel

Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

SMALL / LARGE

Aggregation

The 2nd, 3rd … nth smallest or largest — the top-N list MIN/MAX can't do.

Difficulty

Amateur
Excel file

1What is it?

SMALL returns the kth-smallest value in a range and LARGE the kth-largest — the generalization of MIN and MAX to any rank. SMALL(range, 1) is exactly MIN and LARGE(range, 1) is exactly MAX, but pass 2, 3, … and you get the runners-up MIN/MAX can't reach. It's how you build a "top 5 customers" or "three largest variances" list, pull the second-largest invoice, or grab the k smallest to trim outliers — and because it skips text and blanks, it works straight off a messy column.

2What it looks like

LARGE(array, k)
array
The range of numbers to rank.
k
Which position: 1 = the most extreme (MIN/MAX), 2 = the next, and so on.

3When you use it

  • Top-N lists: the 1st, 2nd, 3rd largest — LARGE(range, 1), (…, 2), (…, 3).
  • The second-largest or second-smallest value, which MIN/MAX can't give you.
  • Spot-check the tails of a column for a miscategorized outlier.

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

LARGE pulls the kth-biggest value and SMALL the kth-smallest — so k = 2 gets the runner-up MIN/MAX can't reach. Edit the numbers, then switch the function or k.

C2
fx
=LARGE(B2:B5, 2)$150,000
ABC
1CustomerRevenueResult
2Acme$150,000
3Globex
4Initech
5Umbrella

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

5Common errors

#NUM!k is 0, negative, or larger than the count of numbers in the range.

Fix: Use a k between 1 and how many numeric values the range holds.

Surprised by tiesDuplicate values each take their own rank.

Fix: Two $5,000s are ranks 1 and 2, not a single rank — SMALL/LARGE count them separately.

#VALUE!k isn't a number.

Fix: Point k at a number or a cell that holds one.

6Better functions & alternatives

  • MIN / MAX — The single smallest or largest — SMALL/LARGE with k = 1.
  • SORT — Spill the whole range in order instead of pulling one rank at a time (Microsoft 365).
  • RANK — The reverse: given a value, find what position it holds.

Want SMALL / LARGE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →