The SORT function leaves the original table unchanged and instead creates a sorted copy of it in your spreadsheet. Read on to learn what the SORT function is and how you can use it in Excel.
What Is the SORT Function?
SORT is a core function in Excel that takes an array, then sorts it based on the parameters you’ve entered for it. The SORT function can sort columns and rows, and you can define which column or row it should sort your array on. The syntax for the SORT function is as below:
array: The array that you want to sort using this function. sort_index: The row or column that the function should take as the sort basis. This will be a single number counting from the start point of the array. sort_order: This will determine whether the array should be sorted in ascending or descending order. 1 is ascending, -1 is descending. by_col: Indicates whether columns should be sorted or rows. Setting this to TRUE will sort columns, which is used in horizontal arrays. Setting it to FALSE will sort rows, which is used in vertical arrays.
The only compulsory parameter here is the array itself. All the other parameters are optional, and you can have the sort function work with just the array. By default, the function will assume sort_index as 1, sort_order as 1, and by_col as FALSE.
If you’re more of a macro person, you can also use Excel macros for sorting data, rather than functions.
How to Use the SORT Function in Excel
In this sample spreadsheet, we have the total points each player has amassed in a board game. This spreadsheet contains two tables, one vertical and one horizontal, including the names of the players and their points.
The goal is to sort the tables based on the scores in descending order. The player with the highest score will be at the top of the sorted table. Download this spreadsheet to get started.
Sorting Rows with the SORT Function
First, let’s sort the vertical table. Before getting started, let’s get a rundown of how we want the sorted table to be. The goal is to sort the table (A2:B11) based on the points (the second column from left). This table will be sorted in descending order, and the rows will be sorted.
Now we can sort the table:
Select the cell where you want to place the new sorted table. That will be cell D2 in this example. In the formula bar, enter the formula below: =SORT(A2:B11, 2, -1, FALSE) This formula calls on the SORT function and tells it to sort the array in A2:B11. It then instructs it to sort the array based on the second column (2) , and to sort them in descending order (-1). Finally, the formula instructs SORT to sort the rows, because the by_col parameter is set to FALSE. Press Enter. Excel will now create a sorted copy of your table!
Sorting Columns With the SORT Function
The second part of this task is to sort the horizontal table. The goal is to sort the second table (H1:Q2) based on the points (second row from top) and to sort them in ascending order.
Select the cell where you want to show the sorted table. This will be cell H6 in this example. Enter the formula below in the formula bar: =SORT(H1:Q2, 2, 1, TRUE) This formula summons the SORT function to sort the array in H1:Q2. The array is sorted by the values in the second row (2) and it is in ascending order (1). Since the array is horizontal, the columns are sorted. Thus, by_col is set to TRUE. Press Enter. Excel will now sort the horizontal table.
If you want to sort a table by date without creating a new copy of it, you can use the Sort & Filter tool in Excel. This way, you won’t need to use any functions, and the changes will be applied to the original table.
Sort Your Spreadsheets With the SORT Function
There are many methods for organizing your spreadsheets, and one is to sort your arrays with the SORT function. The SORT function leaves the original array unchanged and instead creates a sorted copy of the array.
Now that you know how to use the SORT function to sort your arrays, take it to the next level by learning to organize your spreadsheets in Excel.
