{"id":130,"date":"2013-12-17T23:42:05","date_gmt":"2013-12-17T15:42:05","guid":{"rendered":"http:\/\/excelchamp.net\/?p=130"},"modified":"2023-12-03T21:38:00","modified_gmt":"2023-12-03T13:38:00","slug":"finding-the-second-largest-value-in-excel","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/finding-the-second-largest-value-in-excel\/","title":{"rendered":"Finding The Second Largest Value in Excel"},"content":{"rendered":"

After my Advanced Excel workshop in Singapore<\/a>, one of the participants, Lai Thim had a unique problem in Excel. Her boss was always asking her to show the Top 3 products, Bottom 3 countries, or Who came in the second or Third spot in the competition.<\/strong> She found it quite challenging to do this in Excel, and had to resort to sorting the data, manually going to pick the top 3 or bottom 2, and then copy and paste the values in the report.\u00a0<\/em><\/p>\n

And this manual process had to be done each week, and each month, several times. So this was a big time waster. And she is not the only one… most Excel users are not aware of the simple functions that already exist in Excel, which can do the job in just a click.<\/p>\n

So she asked me if there is a way to find the second largest value, or the third largest value given in a data set in Excel.<\/p>\n

Join Vinai’s Data Analysis With Excel Training Course<\/a> to Up Your Skills in Data Analytics.<\/p>\n

<\/div>\n

Finding the Largest Value or the Smallest Value in Excel is Easy<\/strong><\/h2>\n

You can easily find the highest value in any Excel data using the MAX Function, and similarly, find the smallest or minimum value by using the MIN function in Excel.<\/p>\n

But try to find the Second highest value, or the Third Lowest in Excel, and these two MAX & MIN functions can not deliver this simple thing. They were meant for only the highest and the lowest values. This is extremely easy. Just use the Max()<\/strong> function. It will give you the highest value. Similarly, use the Min() function to get the smallest value.<\/p>\n

However, to get the second largest value using this same formula, you can’t use Min()<\/strong> or Max()<\/strong> functions of Excel.
\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Want to Improve Your Excel Skills?<\/strong><\/span><\/p>

Learn the Key Features of Excel Quickly & Easily, by <\/span>Joining the
Online Training<\/strong> on <\/u>Basic \/ Intermediate Excel<\/u>.<\/strong><\/a><\/span><\/p>

To Get Most out of Excel, Learn the\u00a0 Pivot Table techniques in our
Pivot Table Masterclass Training<\/a><\/u><\/b>!<\/strong><\/a><\/span><\/p><\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t<\/p>\n

Using LARGE Function in Excel To Find Any Rank in Excel (including Second Highest)<\/strong><\/h2>\n

Microsoft Excel has a hidden function, often unknown singe the late nineties, which can do the job perfectly. It is called LARGE.<\/p>\n

Let’s say you have values from cell A1 to A5.<\/p>\n

To get the Second Largest value, you could write the following formula in Excel:<\/strong><\/p>\n

=LARGE(A1:A5, 2)<\/em><\/strong><\/p>\n

This will give you the second largest value, without any fuss.<\/p>\n

The format of the LARGE Function is =LARGE(array, k)<\/strong><\/em>, and it picks up the Kth largest value from the array.<\/em><\/strong><\/p>\n

So to find the fifth largest country in terms of sales, you need to write the following in your Excel formula cell.<\/p>\n

=LARGE(sales, 5)<\/strong><\/em><\/p>\n

What if you want to find the second smallest values? Well, we have a formula for that too.<\/p>\n

Using SMALL Function To Find the Second Smallest Values in Excel<\/strong><\/h2>\n

Just like Large, SMALL function will pick up the Second Smallest value<\/em> from a range of cells. So you can use it for finding the Second Smallest, Third Smallest etc.<\/p>\n

=SMALL(A1:A5, 3) would give you the third lowest value in the range of these 5 cells in Excel.<\/strong><\/p><\/blockquote>\n

To find the second lowest values in terms of Sales, you can write<\/p>\n

=SMALL(Sales, 2)<\/strong><\/em><\/p>\n

With this, you can find any lowest or highest, smallest or largest values in Excel easily.<\/p>\n

Conclusion:<\/strong><\/h2>\n

You can use SMALL & LARGE to pick the smallest, second smallest, largest, second largest<\/strong> etc. to your choice. They are pretty easy to use, and are already available in your version of Excel.<\/p>\n

So if you use Excel 2007, or Excel 2010, Excel 2013, Excel 2016, Excel 2019 or even Microsoft Office 365<\/em>, you can immediately use SMALL & LARGE Functions in Excel.<\/strong><\/p>\n

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… \ud83d\ude42<\/em><\/p>\n

Have fun!<\/p>\n

Related Posts:<\/strong><\/h3>\n
    \n
  1. How To Count Cells With a Certain Value in Excel<\/a><\/li>\n
  2. Learn the Multiple Ways to Count in Excel<\/a><\/li>\n
  3. Simple Functions in Excel [Video]<\/a><\/li>\n
  4. FREE COURSE ON PIVOT TABLES TO ANALYZE DATA<\/strong><\/a> – No Signup is required. Click & Start watching Videos For Free and improve your Pivot Table Skills.<\/strong><\/li>\n<\/ol>\n

    Cheers
    \nVinai Prakash
    \nConnect With Me on LinkedIn:     https:\/\/www.linkedin.com\/in\/vinaiprakash\/<\/a><\/p>\n

    Vinai loves to share new and funky ways to find information from our data, using everyday Excel techniques.<\/p>\n

    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<\/a>.
    \n\t\t

    \n\t\t\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t

    Are you facing any problem in using Excel? Any Question?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    You have come to the right place<\/span>. Tell us your needs. We’ll be glad to help you!<\/span><\/strong><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

    \n\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t\n\t\t\t\n\t\t\t\n\n\t\t\t\n\t\t\t
    \n\t\t\t\t\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t\t\t\t