LARGE Function Examples in Excel, VBA, & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 9, 2023
Download Example Workbook

Download the example workbook

This tutorial demonstrates how to use the LARGE Function in Excel to calculate the nth largest value.


How to use the LARGE Function

The LARGE Function returns the k-th largest number from a range of values. It is the opposite of the SMALL Function

To use the LARGE function, use the following formula:

=LARGE(B2:B9, 3)

Example 01

Here we are looking for the 3rd largest number (3rd oldest student) in the range B2:B9.

Out of Range Error

If you enter a number k, greater than the number of items in the range, the LARGE Function will return #NUM! error

=LARGE(B2:B8, 8)

Example 02

Notice here we are looking for the 8th largest number in a range of only 7 numbers. So the LARGE Function returns #NUM!.

Non-numeric Data

The LARGE Function will completely ignore non-numeric data as if that data is not part of the data range.

=LARGE(B2:B8, 4)

Example 03


We can use the LARGE Function with criteria as well to perform a “large if”. For example, let’s say we want to find the 2nd oldest male student.

We will use this array formula:

{=LARGE(IF(B2:B8 = "M", C2:C8), 2)}

Example 04

Note: When building array functions, you must press CTRL + SHIFT + ENTER instead of just ENTER after creating your formula.

You’ll notice how the curly brackets appear. You can not just manually type in the curly brackets; you must use CTRL + SHIFT + ENTER.

To learn more about how the LARGE “IF” formula works, read our tutorial on LARGE If and LARGE IF.

Sort with the LARGE Function

A quite useful use case for the LARGE Function is to sort data in descending (largest to smallest) order. To sort data, we combine the LARGE function with the ROW function.

=LARGE($A$2:$A$8, ROW()-1)

Example 05

This function works by using the ROW Function to define the k-value. Notice how row 2 has the largest number (k = 1), row 3 has the second largest (k = 2), so on & so forth. Thus, sorting the range from largest to smallest.

To learn more read our tutorial on Sorting with the SMALL and LARGE Functions.

Note: The above formula uses absolute references (the $ signs) to lock cell references when copying formulas. If you aren’t familiar with this, please read our Excel References Guide.

LARGE Function in Google Sheets

The LARGE function works exactly the same in Google Sheets as in Excel.

LARGE Google

LARGE Examples in VBA

You can also use the LARGE function in VBA. Type:


AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions