Excel SORT and SORTBY Functions
last modified April 4, 2025
The SORT and SORTBY functions are powerful tools for
organizing data in Excel. They allow dynamic sorting of ranges and arrays.
This tutorial provides a comprehensive guide to using these functions with
detailed examples. You'll learn basic syntax, practical applications, and
advanced techniques to master these essential Excel functions.
SORT/SORTBY Function Basics
The SORT function rearranges the contents of a range or array in
ascending or descending order. SORTBY sorts a range based on the
values in another range or array. Both functions return dynamic arrays.
| Function | Description | Syntax |
|---|---|---|
| SORT | Sorts a range or array | =SORT(array,[sort_index],[sort_order],[by_col]) |
| SORTBY | Sorts by another range/array | =SORTBY(array,by_array1,[sort_order1],...) |
This table compares the two sorting functions. SORT sorts the
array directly, while SORTBY sorts based on other arrays. Both
support multiple sort orders and directions.
Basic SORT Example
This example demonstrates the simplest use of the SORT function with a single column of data.
| A | B |
|---|---|
| Apple | |
| Orange | |
| Banana | |
| =SORT(A1:A3) |
The table shows a simple list of fruits in column A and a SORT
formula in cell B4 that sorts the list alphabetically in ascending order.
=SORT(A1:A3)
This formula sorts the values in cells A1 through A3 alphabetically. The result will be {"Apple";"Banana";"Orange"}. This shows the simplest use of SORT with default parameters.
SORT with Multiple Columns
SORT can handle multi-column ranges, sorting by a specified
column. This example shows sorting a table by the second column.
| A | B | C |
|---|---|---|
| John | 35 | |
| Mary | 28 | |
| Bob | 42 | |
| =SORT(A1:B3,2,-1) |
This table demonstrates sorting a two-column range by the second column (ages) in descending order. The sort_index is 2, and sort_order is -1 for descending.
=SORT(A1:B3,2,-1)
This formula sorts the range A1:B3 by the second column in descending order. The result will be {"Bob",42;"John",35;"Mary",28}. The sort_index parameter determines which column to sort by.
SORTBY with Single Criteria
SORTBY sorts a range based on values in another range. This
example shows basic usage with one sort criterion.
| A | B | C |
|---|---|---|
| Red | 3 | |
| Blue | 1 | |
| Green | 2 | |
| =SORTBY(A1:A3,B1:B3) |
The table shows colors in column A and numbers in column B. The SORTBY
formula sorts column A based on the values in column B in ascending order.
=SORTBY(A1:A3,B1:B3)
This formula sorts the range A1:A3 based on values in B1:B3. The result will be {"Blue";"Green";"Red"}. SORTBY is useful when your sort key is separate from the data you want to sort.
SORTBY with Multiple Criteria
SORTBY can sort using multiple criteria with different sort
orders. This example demonstrates two-level sorting.
| A | B | C | D |
|---|---|---|---|
| Apple | Fruit | 10 | |
| Carrot | Vegetable | 15 | |
| Banana | Fruit | 8 | |
| =SORTBY(A1:C3,B1:B3,1,C1:C3,-1) |
This table shows a three-column dataset. The SORTBY formula first
sorts by category (ascending), then by price (descending) within each category.
=SORTBY(A1:C3,B1:B3,1,C1:C3,-1)
This formula sorts the range A1:C3 first by column B (ascending), then by column C (descending). The result will show Fruits first (Apple then Banana), then Vegetables (Carrot), with Banana appearing before Apple due to price.
SORT with Horizontal Data
SORT can sort horizontal ranges by setting the by_col parameter.
This example shows sorting a row of data.
| A | B | C | D |
|---|---|---|---|
| March | January | February | |
| =SORT(B1:D1,,,TRUE) |
The table demonstrates sorting months horizontally. The by_col parameter is set to TRUE to sort columns instead of rows.
=SORT(B1:D1,,,TRUE)
This formula sorts the horizontal range B1:D1 alphabetically. The result will be {"February","January","March"}. The fourth parameter (TRUE) enables column- wise sorting instead of the default row-wise sorting.
SORTBY with Custom Order
SORTBY can implement custom sort orders using helper columns.
This example shows sorting by priority levels.
| A | B | C | D |
|---|---|---|---|
| Task 1 | High | 2 | |
| Task 2 | Medium | 1 | |
| Task 3 | Low | 3 | |
| =SORTBY(A1:B3,C1:C3) |
The table shows tasks with priority labels and numeric values. The
SORTBY formula uses the numeric values to establish a custom
sort order that doesn't follow alphabetical sequence.
=SORTBY(A1:B3,C1:C3)
This formula sorts the range A1:B3 based on values in C1:C3. The result will show tasks in Medium (1), High (2), Low (3) order. This technique is useful for implementing non-alphabetical sort sequences.
SORT with Dynamic Arrays
SORT can be combined with other dynamic array functions. This
example shows sorting filtered results.
=SORT(FILTER(A1:B10,B1:B10>100),2,-1)
This formula first filters rows where column B values exceed 100, then sorts the results by column 2 in descending order. The combination of SORT and FILTER creates powerful data processing workflows.
SORTBY with Multiple Sort Directions
SORTBY allows specifying different sort directions for each
criterion. This example shows mixed ascending and descending sorts.
| A | B | C | D |
|---|---|---|---|
| North | 200 | Q1 | |
| South | 150 | Q2 | |
| East | 200 | Q1 | |
| =SORTBY(A1:C3,B1:B3,-1,C1:C3,1) |
The table demonstrates sorting first by sales (descending), then by quarter (ascending) for records with equal sales values. This creates a multi-level sort with different directions.
=SORTBY(A1:C3,B1:B3,-1,C1:C3,1)
This formula sorts the range A1:C3 first by column B (descending), then by column C (ascending). Records with equal sales (200) will be ordered by quarter. The result shows North before East due to Q1 coming before Q2.
SORT with Error Handling
SORT can be combined with IFERROR to handle potential errors in
the source data. This example shows a robust sorting approach.
=SORT(IFERROR(A1:B10,""),1,1)
This formula sorts range A1:B10 after converting any errors to empty strings. The IFERROR wrapper prevents sort failures due to error values in the source data. The empty strings will sort to the bottom of ascending sorts.
The SORT and SORTBY functions are essential for
dynamic data organization in modern Excel. From simple lists to complex multi-
criteria sorts, they offer powerful solutions. Mastering these functions will
significantly enhance your data analysis capabilities. Remember that both
functions return dynamic arrays that spill results automatically.
Author
List all Excel Formulas.