Spreadsheet Practice

LARGE function

Quick answer

LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.

Syntax
=LARGE(array, k)
Most common use
=LARGE(B2:B8,1) returns $51,750 (The largest value, same as MAX)

It's the standard way to build a top 3 or top 10 list, and unlike MAX it can reach past the first place. Duplicate values each take their own position.

Available in
Every current version
Category
Statistical functions
Returns
The k-th largest number, or #NUM! if k is less than 1 or more than the number of values.
Google Sheets
Also available in Google Sheets.

Syntax

=LARGE(array, k)

ArgumentRequired?What it is
arrayRequiredThe range or list of numbers to look in.
kRequiredWhich position to return, counting from the largest (1 = largest).

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

CellFormulaResultWhat it shows
F2=LARGE(B2:B8,1)$51,750The largest value, same as MAX
F3=LARGE(B2:B8,2)$48,200The second-highest sales total
F4=SUM(LARGE(B2:B8,{1,2,3}))$146,750An array of k values sums the top three at once

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
RepSalesCheckResult
Ana$48,200Top seller$51,750
Raj$39,500Second place$48,200
Lee$51,750Top three combined$146,750
Kim$44,100
Mo$39,500
Ivy$28,900
Sam$46,800

Practice LARGE

From lesson 34, RANK, LARGE & SMALL. Graded instantly, then re-checked on changed data.

Top three values

In F2, return the k-th largest sale using the k in E2. Fill down to F4.

Common mistakes

What you seeWhy, and how to fix it
#NUM!k is bigger than the number of values in the range, or less than 1. Check the size of the list.
The same number appears twice in a top listDuplicates each take a position, so two reps tied at $39,500 fill places 5 and 6. That's correct.
The wrong range after filling downLock the array with $, such as $B$2:$B$8, when k comes from a cell that changes row by row.

Tips

  • Put 1, 2, 3 in a helper column and use =LARGE($B$2:$B$8,D2) to build a top-N table you can fill down.
  • Pair with XLOOKUP to return the name that goes with each top value.

Questions

How do I find the second highest value in Excel?

Use =LARGE(range,2).

How do I get the name of the top performer?

Look it up from the value: =XLOOKUP(LARGE(B2:B8,1),B2:B8,A2:A8). Ties return the first match.

Lessons that use LARGE

Answers that use LARGE

In other languages

LARGE is K.ESIMO.MAYOR in Spanish Excel and KGRÖSSTE in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .