Excel Large Function

What this does

LARGE returns the kth largest value in a range, with k=1 equivalent to MAX. It is the function behind every "top 5" list: combine it with ROWS or a spilled SEQUENCE to produce a ranked block, and pair it with INDEX and MATCH to pull the label that belongs to each value. Duplicates each occupy a position, so two identical top values mean k=1 and k=2 return the same number. A k larger than the count of numeric values returns #NUM!. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. For “excel large function”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

Sales figures in B2:B100: =LARGE(B2:B100, 1) is the best month and =LARGE(B2:B100, 3) the third best. A live top-five list, =LARGE($B$2:$B$100, ROW()-1) filled down five rows, ranks automatically. To show which month it was, wrap it: =INDEX(A2:A100, MATCH(LARGE(B2:B100,1), B2:B100, 0)). LARGE turns "show me the top five" from a sort-and-copy chore into a formula that updates itself. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “excel large function” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• A k greater than the number of numeric values in the range, which returns #NUM!.
• Forgetting to anchor the range with $ when filling down, so the range slides and each row ranks a different set.
• Expecting duplicates to be skipped — identical values occupy consecutive positions.

Frequently asked questions