Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
How to create beautiful table formatting instantly...
Why to rethink the way you do VLOOKUPs...
Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...
Using Excel's LARGE Function
If you're familiar with Excel's MAX and MIN functions, you know how to find the largest and smallest values in a range. But what if you want to find the second or third largest value rather than the absolute maximum? To solve this problem, we use Excel's LARGE function.
The simple LARGE function
The syntax for the LARGE function is very simple:
=LARGE(range, k)
Given these inputs, LARGE will find the kth largest value within the specified range of values.
Let's look at a simple example of LARGE in action. SnackWorld has a list of product sales by category, and wants to know what its second-best performing category was. How can it do this using LARGE?
=LARGE(C3:C6, 2) Output: $4,000,000
The above function outputs $4,000,000, because the 2nd largest number in the range C3:C6 is $4,000,000.
Let's try another example; say that this time, SnackWorld wants to find the third-best performing category. To modify our sheet, we just need to change the k argument to 3 from 2:
=LARGE(C3:C6, 3) Output: $3,000,000
This formula outputs $3,000,000, because the 3rd largest value in the range C3:C6 is $3,000,000.
Using LARGE with INDEX MATCH
Of course, SnackWorld probably wants to know the name of these categories, not just the dollar sales. To get category names as well as dollar sales values, we can combine the LARGE function with INDEX MATCH, a set of lookup functions, like so:
If you aren't familiar with INDEX MATCH and the formula above is confusing, head on over to our INDEX MATCH tutorial to read up on this useful function. Come back afterwards and try to implement LARGE and INDEX MATCH together!
Now you know how to use LARGE to extract the kth largest value from a range. Questions or comments on the above tutorial? Let us know in the comments section below!
Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
How to create beautiful table formatting instantly...
Why to rethink the way you do VLOOKUPs...
Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...