The Excel SORT function is a powerful tool that lets you organize data in ascending or descending order, improving efficiency when working with large datasets. Understanding how to use the SORT function can save time and simplify data analysis.
How to Write the SORT Function
To use the SORT function, follow this format:
- array: The range you want to sort.
- sort_index (optional): The column or row index to sort by, with 1 as the default.
- sort_order (optional): Choose 1 for ascending or -1 for descending order; ascending is the default.
- by_col (optional): Sorts by columns if set to TRUE or rows if set to FALSE. The default is row sorting.
=SORT(array, [sort_index], [sort_order], [by_col])
How to Use the SORT Function
The SORT function is simple to use:
- Enter the SORT function in a cell.
- Choose the range of cells you want to sort.
- Press Enter, and the data will be sorted in ascending (alphabetical) order by default.
Whenever you update a cell within the source range, the sorted list will automatically refresh to reflect the new values.
You can also select multiple columns, but you can only specify one column as the key for sorting. In the following example:
- We sort by the first column in the table. If no sort_index is specified, Excel defaults to the first column as the sorted column.
- Next, we specify sorting by the Date column (third column of the table).
- Then, we sort by the Fruit column (second column of the table).
- Finally, we arrange the data in descending alphabetical order by setting the third argument to -1.
This function is particularly effective when combined with dynamic arrays, like UNIQUE, as it automatically updates sorted data when you change the source values.
SORT Function with non-adjacent columns
To return a table with non-adjacent columns in Excel, you can combine the SORT function with the CHOOSECOLS function. The CHOOSECOLS function allows you to specify which columns to include from a given array,
