This guide will explain how to use the LARGE function in Excel.

##### Table of Contents

We often need to find the largest or maximum value when working with datasets. However, there may be cases where you need to return the 2nd largest value, 10th largest value, and so on.

Microsoft Excel comes with a built-in `LARGE`

function that allows us to return the nth largest element in a given dataset.

In this guide, we will provide a step-by-step tutorial on how to use the `LARGE`

function in Excel. We’ll also cover other use cases that combine the `LARGE`

function with other Excel functions.

**The Anatomy of the LARGE Function**

The syntax of the `LARGE`

function is as follows:

=LARGE(data, n)

Let’s look at each argument to understand how the `LARGE`

function works in Excel.

**LARGE()**refers to our`LARGE`

function. This function computes the nth largest element in a dataset.**data**refers to the array or range of cells to consider when returning the nth largest element.**n**refers to the rank (from largest to smallest) of the element to return. The value for n must be an integer from 1 to the size of the array or range provided.- The formula
**LARGE(array, 1)**will always return the largest element. - If n is equal to the size of the range,
**LARGE(array,n)**will return the smallest element.

**A Real Example of Using the LARGE Function in Excel**

Let’s explore a few basic examples that show how to use the `LARGE`

function in Excel.

**Using LARGE Function with Arrays **

Suppose we want to find the second largest value in the array **{12,25,46,24,44,72,171}**.

We can input our array directly into the `LARGE`

function as the first argument and set the second argument to 2.

We’ll use the following formula:

=LARGE({12,25,46,24,44,72,171},2)

After evaluating our formula, we find out that the second highest value in our array is 72.

**Using LARGE Function with Cell Ranges**

We can also use the `LARGE`

function to find the nth largest value within a specific cell range.

In our example above, we have a dataset of 15 values in the range **A2:A16**. We want to find the fifth largest value in this range.

We can use the following formula to find the fifth-largest value in our range:

=LARGE(A2:A16,5)

After evaluating our formula, we’ve determined that the fifth largest value in our dataset is 737.

**Using LARGE Function with Other Functions**

We can also use the `LARGE`

function with other built-in functions in Excel. In these cases, the `LARGE`

function typically serves as a way to create criteria for other operations to use.

In the example shown, we used the formula below to return “Yes” if the corresponding value in column A is one of the top 10 values of that range:

=IF(A2>=(LARGE($A$2:$A$16;10)),"Yes","")

The formula uses the `LARGE`

function to find the 10th largest value in the range **A2:A16**. The `IF`

function then compares the current cell to see if it’s greater than or equal to the 10th largest value. The IF function returns “Yes” if the condition is true and returns an empty string if false.

We can also use the `LARGE`

function with a formula like `SUMIF`

. For example, you may want to find the sum of the top 5 values in a particular range.

We can use the following formula to add the top 5 values in the range A2:A16:

=SUMIF(A2:A16,">="&LARGE(A2:A16,5))

To add just the top five values in the range, we’ll use the `LARGE`

function to return the fifth largest number in the range. `SUMIF`

will then use the criteria **“>=”&LARGE(A2:A16,5)** to ensure that we only add up numbers that are greater than or equal to the fifth largest number. In our example, we find out that the sum of our top five values is 4384.

In the example above, we used the `LARGE`

function to help create a criteria argument that checks whether the value is greater than or equal to the fifth largest value before including it in the total.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to start using the `LARGE`

function in Excel.

**How to Use LARGE Function in Excel**

- Select the cell where you want to use the
`LARGE`

function. - Type the
`LARGE`

function and enter the two required arguments. The first argument should be the array or cell range that you want to find the nth largest value. The second argument should be an integer that determines the rank of the value you want to return.

In the example above, our formula should return the largest value in the range**A2:A16**since we set our rank to 1. - Hit the
**Enter**key to evaluate the function. - We can set the range
**A2:A16**to a fixed absolute reference by typing it as “$A$2:$A$16”. This will allow the user to copy the formula down the rest of the column with the AutoFill feature.

**FAQs**

**Why does the LARGE function return an error?**

If you are encountering a #NUM! Error, your n value may be higher than the number of values in the provided range, or the provided range does not contain any numerical values.**How do I return the smallest nth number in an array?**If you want to find the nth smallest number in an array in Excel, you can use the`SMALL`

function, which works similarly to the`LARGE`

function but uses a reverse rank. Providing a rank of 1 will return the smallest number in the array.

To learn more about using the `LARGE`

function, you can read our post on how it can be used alongside the `VLOOKUP`

function in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!