LARGE Function in Excel: (Easy How-To Guide)

In this article, you will learn how to use the LARGE formula in Excel.

‍

What is the LARGE function in Excel?

The LARGE function in Excel is a statistical function used to return the k-th largest value in a data set. In other words, it can be used to find out the 1st, 2nd, 3rd (or any other position) largest value in a range of cells in an Excel workbook.

‍

When is it beneficial to use the LARGE formula in Excel?

The LARGE function in Excel can be beneficial in several scenarios, such as:

• Data Analysis: The LARGE function can help you glean insights from your data such as identifying the extremes of your dataset. For example, you can use it to find the top 5 sales values, the top 3 test scores, the top 10 customers in terms of revenue, etc.
• Establishing Thresholds: You can use the LARGE function in Excel to set criteria or thresholds in your data. For example, in sales, you might want to set a threshold for the top 10% of values.
• Outlier Detection: If you're trying to find extremely high values (outliers) in your data, the LARGE formula can be useful. By retrieving the top few values, you might be able to identify data points in your Excel spreadsheet that are significantly higher than the rest.

‍

How do you use the LARGE function in Excel?

The syntax of the LARGE function is the following:

=LARGE(array, k)

Array: The required argument array refers to the range of cells that you want to consider in your data set.

K: The required argument ‘k’ is the position from the largest value. So, if ‘k’ is 1, the function will return the largest value. If ‘k’ is 2, it will return the second largest value, and so on.

‍

Note: The LARGE function will return the “#NUM!” Error if:

• The array is empty or contains no numeric values.
• k≤0 or k is greater than the number of data points.

‍

A few examples of using the LARGE function in Excel can be seen below. For instance, let's say you had a bake sale and wanted to know which items performed the best, you can do so as below:

‍

How to use the LARGE formula with other Excel formulas?

The LARGE function can be combined with other Excel functions to create more complex formulas and achieve specific outcomes. For instance, continuing to consider the data in the screenshot above, suppose you wanted to identify the specific product that made the largest sale. You could then type the following:

=INDEX(B3:B7,MATCH(LARGE(C3:C7,1),C3:C7,0))

• This formula will return “croissants” as the highest sale (in accordance with the example in the screenshot above).

‍

By using the INDEX and MATCH functions along with the LARGE function we have a more complex formula in Excel, but one that can be beneficial in creating a deeper understanding of the data in your spreadsheet.