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.

img1

 

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.

img2

 

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.

img3

 

 

Users are saying about us...

  1. 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?

  2. 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

  3. 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

    • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube