finding 3 largest and 3 smallest numbers in a spreadsheet

Occasional Contributor

Hi. Im relatively new to excel formulas. Im trying to pull out the 3 largest and 3 smallest numbers in my spreadsheet. How do I do this/

4 Replies
best response confirmed by staciehamilton2370 (Occasional Contributor)


You can use the LARGE and SMALL functions for this.

To return the 3 largest values in the range A1:Z100:


And the three smallest:


Warning: if there are ties, the results might be unexpected.

Thank you so much!!

Hi @staciehamilton2370,

For this purpose there are the SMALL() and LARGE() functions. Together with the SEQUENCE() function, you can output all three values simultaneously or calculate them further.


 I have assumed that the data is available in A1:A20.

thank you!! And yes, you assumed correctly. It's a huge file