SORT function

The SORT function in Excel is used to sort a range or array of data in ascending or descending order, based on one or more columns or rows. It is a dynamic array function, meaning it spills the sorted data into multiple cells when used with a range.

Syntax:

=SORT(array, [sort_index], [sort_order], [by_col])

• array: The range or array of data that you want to sort. This is a required argument.
• [sort_index] (optional): A number that specifies the column or row to sort by. The default is 1, which sorts by the first column or row.
• [sort_order] (optional): A value that specifies the order of the sort:
  • 1 (or omitted): Sorts in ascending order (smallest to largest, A to Z).
  • -1: Sorts in descending order (largest to smallest, Z to A).
• [by_col] (optional): A logical value that specifies whether to sort by columns or rows:
  • FALSE (or omitted): Sorts by rows (default).
  • TRUE: Sorts by columns.

Example 1: Basic Sort by Column

Suppose you have the following data in the range A1:A5:

To sort this data in ascending order, use the formula:

=SORT(A1:A5)

This will return:

Example 2: Sort by Column (Descending Order)

To sort the data in descending order (largest to smallest), use:

=SORT(A1:A5, , -1)

This will return:

Example 3: Sort by Multiple Columns

Suppose you have the following data in the range A1:B5:

To sort this data by the Score column (descending), and then by Name (ascending), use the formula:

=SORT(A1:B5, 2, -1, FALSE)

• 2 refers to the second column (Score).
• -1 specifies descending order for the Score.
• FALSE ensures sorting by rows (default).

This will return:

Example 4: Sort by Row (Transposed Data)

Suppose you have the following data in the range A1:E1:

| 10 | 20 | 30 | 40 | 50 |

To sort this data by row in ascending order, use:

=SORT(A1:E1, , 1, TRUE)

This will return:

| 10 | 20 | 30 | 40 | 50 |

And to sort it in descending order:

=SORT(A1:E1, , -1, TRUE)

This will return:

| 50 | 40 | 30 | 20 | 10 |

Example 5: Sorting a Dynamic Range

If you want to sort a dynamic range that will adjust as new data is added, you can reference a range like this:

=SORT(A1:A10)

As new data is added to the range A1:A10, the sorted result will update automatically.

Key Points:

• Dynamic Array: The SORT function is a dynamic array function, meaning it can return multiple values (a range) that “spill” into adjacent cells.
• Sorting Order: You can specify both ascending and descending sorting using the sort_order argument.
• Sort by Rows or Columns: You can sort either by rows (default) or by columns by setting the by_col argument to TRUE.
• Sort by Multiple Columns: Use the sort_index argument to specify which column (or row) to sort by, and then add more complex sorting by combining multiple SORT functions or using helper columns.

Use Cases:

• Data Organization: Use SORT to organize lists, tables, or databases based on specific criteria such as alphabetical order, numerical value, or dates.
• Dynamic Reports: Create reports that automatically sort based on updated data without needing manual intervention.
• Comparison: Sort data to facilitate comparisons, such as sorting a list of sales amounts to find the highest and lowest values.

Limitations:

• Overwrites Data: When used in a formula, SORT will spill the sorted data into adjacent cells. Make sure the surrounding cells are empty to avoid overwriting existing data.
• Limited to 1,024 Rows (in Excel for the Web): In some versions of Excel (like Excel for the Web), the number of rows that can be returned by dynamic array functions, including SORT, might be limited to 1,024 rows.

In summary, the SORT function is a powerful tool for automatically organizing data in Excel, allowing you to sort data dynamically in ascending or descending order, by rows or columns, with the flexibility to sort by multiple columns for complex datasets.