Finding The Second Largest Value in Excel

After my Advanced Excel workshop in Singapore, one of the participants, Lai Thim asked me if there is a way to find the second largest value, or the third largest value given in a data set.

Finding the Largest Value

This is extremely easy. Just use the Max() function. It will give you the highest value. Similarly, use the Min() function to get the smallest value.

But to get the second largest value, using a formula, you can’t use Min() or Max() functions.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Using LARGE Function

Microsoft Excel has a hidden function, often unknown singe the late nineties, which can do the job perfectly.

Let’s say you have values from cell A1 to A5.

To get the Second Largest value, you could write the following formula in Excel:

=LARGE(A1:A5, 2)

This will give you the second largest value, without any fuss.

The format is =LARGE(array, k), and it picks up the Kth largest value from the array.

A Tiny Cousin Brother – SMALL

Just like Large, SMALL function will pick up the Second Smallest value for that matter.

=SMALL(A1:A5, 3) would give you the third smallest value.

That’s it. Give it a try. This is a quick and dirty way to find such small or large values. These are not new functions… Have been there in Excel since 1995… 🙂

Have fun!

Cheers
Vinai Prakash

Vinai loves to share new and funky ways to find information from our data, using everyday Excel techniques.

Vinai also conducts in-house and public runs of his famous Advanced Excel Techniques, and Excel Dash-boarding Techniques in Singapore, Malaysia, Indonesia, Dubai, Hong Kong, and many other countries around the globe. Connect with Vinai at Vinai@ExcelChamp.net.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!