Sorting Numbers in Ascending or Descending Order in Microsoft Excel

To sort the numbers in ascending or descending order, we use the formulas “LARGE”, “SMALL” and “ROW” in Microsoft Excel.

To sort in ascending order use the “SMALL” function along with the “ROW” function. And to sort in descending order use the “LARGE” function along with the “ROW” function.

To understand how you can sort numbers in ascending or descending order, let’s take an example.

Example 1: We have a list of fruitsalong with the sold quantity;Column “A” contains the names of the fruits and column “B” the sold quantity.

In column D we need to return the 3 Smallest sold quantity and in column E we need to return the 3 largest sold quantity.

How arrange the data in ascending order through formula?

To sort the data in ascending order through formula follow the below mentioned steps:-

Select the cell D2 and write the formula
=SMALL($B$2:$B$8,ROWS(D$2:D2))
Press Enter on the keyboard.
The function will return the first smallest sold quantity
To return the 2nd and 3rd smallest quantity, copy the same formula by pressing the key “CTRL+C” and select the range D3:D4 and paste by pressing the key “CTRL+V” on the keyboard.

How arrange the data in descending order through formula?

To sort the data in descending order through formula follow the below mentioned steps:-

Select the cell E2 and write the formula
=LARGE($B$2:$B$8,ROWS(E$2:E2))
Press enter on the keyboard.
The function will return the first largest sold quantity
To return the 2nd and 3rd largest quantity, copy the same formula by pressing the key “CTRL+C” and select the range E3:E4 and paste by pressing the key “CTRL+V” on the keyboard.

Noni Campbell
March 13, 2016 at 11:33 pm

I have 1200 numbers that I need to formulate in either descending or ascending order.
Please help!

Reply ↓
- Admin
  March 14, 2016 at 5:54 am
  
  Hi Noni Campbell,
  
  To sort the numbers in descending or ascending order, please use the shortcut ALT+D+S, which will open up a sorting window. This window will enable you to sort the numbers as per your requirement from smallest to largest or largest to smallest.
  
  Happy Learning,
  Site Admin
  
  Reply ↓
  - Olam Patrick
    February 23, 2020 at 10:52 am
    
    Please when i press Alt+ranges given, it does selects the last one automatically which is a wrong position for an individual/Item
    
    Reply ↓
ripudaman
July 28, 2015 at 9:24 am

How will get the Item name with Rank or Large Formula?

Reply ↓
ripudaman
July 28, 2015 at 9:23 am

How to get name of item with Rank or Large formula...

Reply ↓
B.Sivakumar
December 12, 2014 at 11:41 am

I would like to get the PO numbers(C) for a given DrawID(A) & Material(B) in column F,G,H.

A B C E F G
Draw ID Material PO No
LMV/123456 PICS163011436 2345678
LMV/000000 PICS163011436 2345679
LMV/123456 PICS163000000 2345680
LMV/123456 PICS163011436 2345681

Reply ↓
Yasmine
November 10, 2014 at 5:49 pm

Please help - I am trying to do the same thing as this EXCEPT for your exmple I would be trying to find out which item (text) is has the highest value next to it.

I cannot do it manually by selecting my data and sorting it because I need it to do this automatically.

Anyone can help?

Thanks,
Yasmine

Reply ↓
- Nisha Dhawan
  November 11, 2014 at 3:41 am
  
  Hi Yasmine,
  
  Kindly post you query @ www.excelforum.com . You will get the appropriate reply immedialtely.
  
  Regards
  Nisha
  
  Reply ↓
Nikhil Oswal
June 24, 2014 at 6:40 am

I Have stress Strain data (some 5000 rows in excel) & want to sort it in ascending order. Both columns ( for stress - Strain) are inter related. Ex. Val in 8th row strain column corresponds to value in 8th row stress column.

IMPORTANT: -- I want to ignore rows in between for which values are going in descending order & jump to next row with ascending order to form two new columns with ascending order.

Is it possible with excel formulation?

Reply ↓