Large Function - Feature Image

LARGE() Function in Excel

Joseph Basic, Excel, Formulas 0 Comments

Sometimes you need to take a set of data and get the first, second, or third top values. You can do that with the LARGE() function in Excel. Here’s a quick article to help explain how to use the LARGE() function.

LARGE() Function Defined

LARGE(array, k)

The LARGE() function will take an array and return the k-th largest value from the data. You can think of this as Excel sorting the data from largest to smallest and then you can say “give me the top largest item” by setting k to 1 and the formula would return the highest value from the list.

Parameter Definition
array The data to search the largest values from.
k This is the number that you give the formula to say "I was the top largest number" (which you would use 1). If you wanted the second highest value, you would use 2 for k.

Large() Function Example

For this example we’ll be using the following spreadsheet:

Large Function - Random Data

You can also copy and paste this table into Excel to follow along:

Random Data More Random Data
92 87
16 79
46 69
63 77
4 47
38 90
74 35
58 89
3 87

Finding the Top N Largest Value

To find the top largest value in the data, we can use this formula:

=LARGE($A$2:$B$10,1)

This will return 92.

Here is the formula breakdown:

=LARGE($A$2:$B$10, 1)

If we expand out the array $A$2:$B$10, we get the following:

=LARGE({92,87;16,79;46,69;63,77;4,47;38,90;74,35;58,89;3,87}, 1)

Then Excel sorts the list from largest to smallest

=LARGE({92,90,89,87,87,79,77,74,69,63,58,47,46,4,38,35,3,16}, 1)

Finally, Excel takes the k-th largest value. In this case, it’s 1, so it takes the 92 from the list and returns that.

If we want to find the second largest value, change the k parameter to 2:

=LARGE($A$2:$B$10, 2)

This returns 90.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!