There are times when you need to sort your large data in an ascending or descending order. By sorting your data in ascending or descending order, you can quickly find information on your list. You can use the expanding range to get this function done effectively.
How to sort numbers in ascending or descending order
To sort data in Excel numerically, text in alphabetical order, sort range in ascending or descending order, the SMALL, LARGE with an expanding range function can be used.
Sorting your data is vital in:
- Payroll processing
- Preparation of financial documents
How to Sort Data with the Small Function in Ascending Order
The SMALL Function with an expanding range is especially crucial for picking out the smallest value in your list of data.
However, a simple sort can do this for you, but the expanding range is excellent because it accommodates any changes you make in your data any time in the future and automatically changes the order to suit this change.
Basic formula=SMALL(data,ROWS (exp_rng))
- Example 1: Using the SMALL function to sort C6:C13
Figure 1 shows a given set of data sorted in ascending and descending order; we used a basic formula by incorporating the SMALL function using the expanding range in a simplified process:
How did we arrive at the data shown for the ascending order using the SMALL Function based on the expanding range?
- Enter your data into the Worksheet or spreadsheet.
- Now place your cursor at the adjacent cell, or like in the figure below on Cell D6. Click on fx as shown in figure 2 below.
- Select SMALL in the insert function pop up
- Click on the OK as shown in figure 2
- You will see this figure below
- Type in the Array as shown; $C6:$C13,
- Next, type in the K number; this should be several figures below your estimated small value
- If you pick your smallest value, then your data will be entered without selecting for your smallest value. Look at the figure below
- Click OK
- Your smallest figure is displayed as seen below.
- To further fill the next cell below, simply copy the text
- Now paste it in the next cell as shown below
- Now, take your cursor and place in the fx section, That is the =SMALL ($C7:$C14,1)
- Change the k value from 1 to 2 as shown below
- Press the Enter key and this should select your second value in ascending order
- To pick the next cells, simply copy the text again and paste in the next section, changing the K from 2 to 3 and so on
- Your formulas for each cell should look like this or you can manually use the formulas below:
As you copy the SMALL function formula down the table, the table expands in terms of the incrementing value.
Sort Data in descending order using the LARGE function
You can equally sort numbers in descending order as shown in figure 1. All you need do is replace the SMALL function with the LARGE function:
Basic formula:=LARGE(data,ROWS( exp_rng))
Example 2- Using the LARGE function to sort data in descending order
The LARGE function for figure 1 will be thus:
- Again, enter your data as shown in figure 1
- Next, place your cursor on Cell E6 or as shown and click the fx function simultaneously
- Select the LARGE function shown above
- Enter the Array value; $C6:$C13
- Enter the K value, again this should be 1, so the highest value will be selected. All of this is shown below
- Click OK to enter the function
- In the same way as the SMALL function, copy the Cell E6
- Paste in Cell E7 as shown in the figure below
- Highlight the K value and change it to 2
- Next, click OK to enter to formula
- To select the next values in decreasing order, copy the LARGE function from the cell to the next blank cell
- Highlight and change your figures as you move further
It should look like this:
Like the SMALL function, the LARGE function extracts your nth value and arranges your values in nth largest or descending order.
Still need some help with Excel formatting or have other questions about Excel? Connect with a liveExcel experthere for some 1 on 1 help. Your first session is always free.