Statistical

LARGE Function in Excel

Returns the k-th largest value in a data set.

Syntax

  • =LARGE(array, k)

Arguments

  • array (required): Range or array of values
  • k (required): Position from largest (1=largest)

Examples

  • =LARGE(A1:A100, 1) - Largest value - Result: Maximum
  • =LARGE(A1:A100, 3) - 3rd largest - Result: 3rd highest

Excel LARGE function documentation

  • LARGE returns the k-th largest value in a data set — 1 = maximum, 2 = second largest, etc.
  • Syntax: =LARGE(array, k). Useful for top-N analysis without sorting the source.
  • Pair with [SMALL](/functions/small/) for bottom-N and outlier review.
  • Related: [MAX](/functions/max/), [RANK](/functions/rank/), [SORT](/functions/sort/).

LARGE syntax and k argument

  • array: range of numbers — ignores text and logical values.
  • k: rank from largest (1 = max). k > count of numbers returns #NUM!.
  • LARGE includes duplicates — two 100s mean k=1 and k=2 can both be 100.
  • Use absolute references on array when copying k across a summary row.

Step-by-step: top 3 sales values

  • Step 1 — Sales amounts in B2:B500.
  • Step 2 — Largest: =LARGE(B2:B500, 1).
  • Step 3 — Second: =LARGE(B2:B500, 2); third: k=3.
  • Step 4 — Label with helper row k=1,2,3 and spill k across columns.
  • Step 5 — Match rep names with [XLOOKUP](/functions/xlookup/) on amount if unique.

LARGE vs MAX vs SORT

  • MAX — single maximum only.
  • LARGE — nth largest for top-N dashboards.
  • SORT — full ordered list spill in 365.
  • Statistical category: [/categories/statistical/](/categories/statistical/).

Worked examples to copy

  • Top value: =LARGE(A:A, 1).
  • 5th highest: =LARGE(scores, 5).
  • Dynamic k from cell: =LARGE(B2:B100, E1).
  • Exclude zeros: =LARGE(FILTER(B:B, B:B>0), 1) in 365.

People also ask

  • LARGE #NUM!? — k larger than count of numeric values.
  • LARGE ignore text? — Yes; text cells skipped.
  • LARGE vs PERCENTILE? — LARGE is discrete k-th value; percentile is interpolation.
  • LARGE with criteria? — Use FILTER first, then LARGE on result.

Common errors

  • #NUM! if k > number of values or k < 1

Use cases

  • Top N values
  • Ranking
  • Outlier detection

Frequently asked questions

  • How do I get the top 5 values from a list? Use LARGE with SEQUENCE or ROW: =LARGE(A1:A100, {1,2,3,4,5}) returns an array of top 5 values. In newer Excel: =LARGE(A1:A100, SEQUENCE(5)) spills the top 5 automatically.
  • How do I sum the top 3 values? Use SUMPRODUCT with LARGE: =SUMPRODUCT(LARGE(A1:A100, {1,2,3})) or =SUM(LARGE(A1:A100, {1,2,3})). This sums the 1st, 2nd, and 3rd largest values.
  • How do I find the 2nd largest ignoring duplicates? Combine with UNIQUE: =LARGE(UNIQUE(A1:A100), 2) gets the 2nd largest unique value. This ignores repeated values when determining rank.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use LARGE

  • Top N values — common Statistical scenario for LARGE.
  • Ranking — common Statistical scenario for LARGE.
  • Outlier detection — common Statistical scenario for LARGE.

LARGE in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • LARGE syntax: =LARGE(array, k)
  • array (required): Range or array of values
  • k (required): Position from largest (1=largest)
  • Confirm LARGE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm LARGE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • SMALL (/functions/small/): Returns the k-th smallest value in a data set.
  • MAX (/functions/max/): Returns the largest value in a set of values.
  • RANK.EQ (/functions/rank-eq/): Returns the rank of a number in a list of numbers.

Errors to watch for

  • #NUM! if k > number of values or k < 1 — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter LARGE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

LARGE worked examples to copy

  • =LARGE(A1:A100, 1) — Largest value. Expected result: Maximum.
  • =LARGE(A1:A100, 3) — 3rd largest. Expected result: 3rd highest.

LARGE reference summary for crawlers and offline review

  • LARGE belongs to the Statistical category in Excel. Returns the k-th largest value in a data set.
  • Full syntax: =LARGE(array, k). Open /functions/large/ for parameters, FAQs, and related pages.
  • Common mistakes: #NUM! if k > number of values or k < 1
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.