Excel Top N Reports: RANK, LARGE and SMALL Explained

Why Top N reports are harder than they look

Ask for the top 5 products by revenue and most people reach for AutoFilter, sort descending, copy the first five rows, paste somewhere else. It works — once. The moment the source data changes, the report is stale, and nobody remembers to rebuild it.

Three functions fix this permanently: LARGE and SMALL pull the k-th value out of a range, and RANK tells you where a value sits in the pecking order. Combine them with INDEX/MATCH and you get a Top N report that refreshes itself the second the numbers change.

The three functions at a glance

A naming note first: RANK still works in every current version of Excel, but Microsoft recommends RANK.EQ, which behaves identically. There is also RANK.AVG, which returns the average rank when values tie.

The order argument of RANK trips people up constantly. Omit it (or pass 0) and Excel ranks descending, so the largest number gets rank 1. Pass 1 and the ranking flips to ascending.

Sample data

Note the deliberate tie at 61,500 — that single detail is where most Top N reports break.

Step 1 — Pull the values with LARGE

In E2, enter:

=LARGE($C$2:$C$11, ROWS($E$2:E2))

ROWS($E$2:E2) returns 1, and grows to 2, 3, 4 as you drag the formula down. One formula, ten different k values, no manual editing, no helper column of 1-2-3-4-5. Fill down to E6 and you have the Top 5 revenue figures.

For a bottom-N report — worst-performing stores, slowest tickets — swap in SMALL($C$2:$C$11, ROWS($E$2:E2)).

Step 2 — Attach the labels with INDEX + MATCH

Raw numbers are not a report. In F2:

=INDEX($A$2:$A$11, MATCH(E2, $C$2:$C$11, 0))

Fill down and it looks perfect — until it reaches the tie. Because MATCH returns the *first* match it finds, both 61,500 rows resolve to Bravo Gadget. Delta Sensor silently disappears and your Top 5 quietly becomes a Top 4 with a duplicate.

The fix is a tie-breaker that skips names already printed above. Replace F2 with:

=INDEX($A$2:$A$11, MATCH(1, (E2=$C$2:$C$11)*(COUNTIF($F$1:F1, $A$2:$A$11)=0), 0))

The COUNTIF($F$1:F1, ...) part counts how many times each product name has already appeared in the output column above. Multiplying the two logical tests gives 1 only for a row that both matches the target revenue and has not been used yet, so the second 61,500 correctly resolves to Delta Sensor. In Excel 365 this is a normal formula; in Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

Step 3 — Rank every row with RANK

When you need a position column rather than a separate Top N block, put this in D2:

=RANK(C2, $C$2:$C$11)

You get Alpha Widget 5, Bravo Gadget 1, Charlie Module 9, Delta Sensor 1, Golf Bracket 10, and so on. Because of the tie, both 61,500 rows receive rank 1 and the next value is ranked 3 — there is no rank 2. RANK.AVG would give both rows 1.5 instead. Neither result is wrong: pick RANK.AVG when you need a gap-free sequence, and plain RANK for competition-style standings.

RANK accepts no criteria, so ranking *within* a region needs COUNTIFS:

=COUNTIFS($B$2:$B$11, B2, $C$2:$C$11, ">"&C2)+1

That counts how many rows share the same region and beat the current row, then adds 1 to produce a rank of 1, 2, 3 inside each region.

Step 4 — Top N inside one group

Top 3 revenue in the North region only:

=AGGREGATE(14, 6, $C$2:$C$11/($B$2:$B$11="North"), ROWS($E$2:E2))

Function number 14 makes AGGREGATE behave like LARGE, and option 6 tells it to ignore errors. Dividing the revenue range by the region test produces #DIV/0! for every non-North row, and AGGREGATE discards those — no Ctrl+Shift+Enter required, which is why this beats an array-entered LARGE(IF(...)).

Step 5 — Highlight the Top N with conditional formatting

Select C2:C11, then add a rule based on a formula:

=C2>=LARGE($C$2:$C$11, 5)

Everything at or above the 5th largest value lights up, and the highlight follows the data because the threshold is computed, not typed. Swap LARGE for SMALL with <= to shade the bottom N.

Common pitfalls

• #NUM! means k is larger than the count of numeric values — =LARGE(range, 15) on ten rows. Wrap the formula in IFERROR if the list can shrink.
• Text and blanks are ignored by LARGE, SMALL and RANK. That is usually helpful, but it makes k fragile when rows are added or removed. Use =COUNT($C$2:$C$11) to see how many real numbers exist.
• Duplicate values are the number one cause of missing rows. Always add the tie-breaker.
• Top by percentage, not by count: =LARGE($C$2:$C$11, ROUNDUP(COUNT($C$2:$C$11)*0.1, 0)) gives the top 10% threshold.
• Hard-typed results go stale. Keep the whole report formula-driven, or a manual paste will undo everything above.

Which function when

Wrapping up

LARGE and SMALL fetch the values, INDEX/MATCH with a COUNTIF tie-breaker fetches the names, and RANK supplies the position column that makes the whole thing sortable and filterable. That trio covers nearly every Top N request you will ever receive: best-selling SKUs, worst-performing branches, highest-value customers, longest open tickets. Build it once as a template, point it at a table, and next quarter's Top 10 is already finished before anyone asks for it.