Top and Bottom With LARGE and SMALL

MAX gives you the single largest value and MIN the single smallest. But what if you want the 2nd highest, or the 3rd lowest?

That is exactly what LARGE and SMALL do. They let you pull the nth biggest or nth smallest value from a range, which is the foundation of top-N and bottom-N reports.

LARGE(range, k) returns the kth largest value. The second argument k picks the position: 1 is the largest, 2 the second largest, and so on.

So LARGE(A2:A20,1) equals MAX(A2:A20), while LARGE(A2:A20,2) gives the runner-up.

SMALL(range, k) mirrors LARGE from the bottom. SMALL(A2:A20,1) equals MIN(A2:A20), and SMALL(A2:A20,3) returns the third smallest value.

Use SMALL to surface the worst performers, the cheapest options, or the earliest dates in a dataset.

To list the top three values, stack three LARGE formulas with k = 1, 2, 3. A cleaner approach is to reference a helper number so you can fill the formula down.

If cell D2 holds 1, D3 holds 2, and D4 holds 3, this single formula filled down produces the full top-three list.

You can generate k automatically with the ROW function so you do not need a helper column. ROW()-1 yields 1 in the first row, 2 in the next, and so on.

Filled down, this builds a ranked list without any extra setup. Adjust the offset to match where your list starts.

In modern Excel and Google Sheets you can spill an entire top-5 in one formula by feeding SEQUENCE as the k argument. SEQUENCE(5) produces 1, 2, 3, 4, 5.

LARGE then returns five values at once, spilling down automatically. One formula, a full ranked list.

A common business question: what do my top 3 customers contribute? Wrap LARGE in SUM with a SEQUENCE (or an array constant) to total the top values.

The formula below adds the three largest values in the range, giving you the combined top-3 contribution in a single cell.

If k is less than 1, or larger than the count of numbers in the range, LARGE and SMALL return the #NUM! error.

For example, asking for the 30th largest value in a list of only 19 numbers fails. Guard against this in reports by wrapping the call in IFERROR.

A value alone is less useful than knowing who earned it. Combine LARGE with INDEX and MATCH to fetch the name attached to the top value.

This finds the largest sales figure, locates its row, and returns the matching name from column A, so your report reads "Top seller: Maria."

Like the other statistical functions, LARGE and SMALL count only numbers. Text and blank cells in the range are skipped, so they do not affect which value is nth largest.

This means you can safely point them at a column that includes a header row. Just watch for numbers accidentally stored as text, since those are ignored and can change which value LARGE or SMALL returns.

You have monthly sales in B2:B20. Management wants the best and worst three months.

• Best three: =LARGE($B$2:$B$20,{1,2,3})
• Worst three: =SMALL($B$2:$B$20,{1,2,3})

Place each in a cell that can spill, and you instantly have a top-and-bottom snapshot driving your dashboard.

Test your understanding of LARGE and SMALL.

You can now reach beyond MAX and MIN:

• LARGE(range,k) returns the kth largest; SMALL(range,k) the kth smallest.
• k = 1 matches MAX / MIN; increase k for runners-up.
• Feed SEQUENCE or an array like {1,2,3} to build or sum a top-N list in one formula.
• Pair with INDEX-MATCH to label the value, and wrap in IFERROR to handle out-of-range k.