The LARGE Function In Google Sheets

The LARGE function in Google Sheets returns the n-th largest value from a dataset.

For example, you could use large to determine the 5th largest value in a dataset, the 10th largest, the 50th largest, etc.

The formula to find the 5th largest value in this example is:

🔗 Get this example and others in the template at the bottom of this article.

LARGE Function Syntax

It takes two arguments:

• The range
• n, the rank of the element to return

LARGE Function Notes

• LARGE ignores empty cells, TRUE, FALSE, and text values.
• The value of n must be less than or equal to the number of elements in the range, otherwise you’ll get a #NUM! error. I.e. you cannot find the 11th largest value in a dataset that only contains 10 values.

LARGE Function Examples

1. Using LARGE Function With A Range

With data in the range A1:A10, this formula will find the largest value:

Then this will find the second largest:

and this the third largest:

and so on.

2. Using LARGE Function With A 2-D Range

The LARGE function works equally well with 2-D ranges as single rows or columns.

For example, this formula finds the largest value in the data range A1:D6

3. With Rank In Separate Cell

The rank value does not have to be hard-coded into the LARGE formula.

You can reference another cell containing a number (one that could be from a calculation even).

For example, with the data in A1:A10 and the rank number in cell D2, this formula will calculate the value:

which looks like this in our Sheet:

4. With Arrays

LARGE can be used with arrays such as other formula outputs or array literals.

This particular formula will output 350.

Advanced Use Cases

The LARGE function is used in this example to highlight the top 5 values in a datset.

LARGE Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

The LARGE function is also covered in the Day 24 lesson of my free Advanced Formulas 30 Day Challenge course.

It’s part of the Statistical family of functions in Google Sheets. You can read about it in the Google Documentation.

See Also

How To Rank Data In Google Sheets With The RANK Function