How to Use SORT and SORTBY in Excel
Introduction
SORT and SORTBY reorder your data with a formula instead of the ribbon's sort buttons. The result updates automatically when the source data changes, and the original table stays untouched.
The Dataset
Here's a sales table we'll use throughout:
| A | B | C | D | |
|---|---|---|---|---|
| 3 | Salesperson | Region | Product | Revenue |
| 4 | Maria | West | Monitor | 4200 |
| 5 | James | East | Laptop | 8500 |
| 6 | Sarah | West | Keyboard | 1300 |
| 7 | James | East | Monitor | 3100 |
| 8 | Maria | West | Laptop | 9200 |
| 9 | Sarah | East | Headset | 2700 |
We want to sort this data in different ways without changing the original table.
SORT
SORT reorders a range by one of its columns. The syntax is:
=SORT(array, [sort_index], [sort_order], [by_col])
- array: The range to sort.
- sort_index: Which column to sort by (1 = first column, 2 = second, etc.). Defaults to 1.
- sort_order: 1 for ascending (default), -1 for descending.
- by_col: TRUE to sort left-to-right instead of top-to-bottom. Defaults to FALSE.
To sort the entire table by Salesperson (column 1) in alphabetical order:
=SORT(A4:D9)
| Salesperson | Region | Product | Revenue |
|---|---|---|---|
| James | East | Laptop | 8500 |
| James | East | Monitor | 3100 |
| Maria | West | Monitor | 4200 |
| Maria | West | Laptop | 9200 |
| Sarah | West | Keyboard | 1300 |
| Sarah | East | Headset | 2700 |
Since sort_index defaults to 1 and sort_order defaults to ascending, this sorts A-Z by the first column.
To sort by Revenue (column 4) from highest to lowest:
=SORT(A4:D9, 4, -1)
| Salesperson | Region | Product | Revenue |
|---|---|---|---|
| Maria | West | Laptop | 9200 |
| James | East | Laptop | 8500 |
| Maria | West | Monitor | 4200 |
| James | East | Monitor | 3100 |
| Sarah | East | Headset | 2700 |
| Sarah | West | Keyboard | 1300 |
The -1 flips the order to descending.
SORTBY
SORTBY sorts one range based on values in a different range. The syntax is:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
- array: The range to sort.
- by_array1: The range to sort by (must have the same number of rows as array).
- sort_order1: 1 for ascending, -1 for descending.
To sort the table by Region:
=SORTBY(A4:D9, B4:B9)
| Salesperson | Region | Product | Revenue |
|---|---|---|---|
| James | East | Laptop | 8500 |
| James | East | Monitor | 3100 |
| Sarah | East | Headset | 2700 |
| Maria | West | Monitor | 4200 |
| Sarah | West | Keyboard | 1300 |
| Maria | West | Laptop | 9200 |
This produces the same result as =SORT(A4:D9, 2), but SORTBY becomes more useful when you add multiple sort levels.
Sorting by Multiple Columns
SORTBY accepts additional by_array and sort_order pairs. Each pair adds a tiebreaker.
To sort by Region ascending, then by Revenue descending within each region:
=SORTBY(A4:D9, B4:B9, 1, D4:D9, -1)
| Salesperson | Region | Product | Revenue |
|---|---|---|---|
| James | East | Laptop | 8500 |
| James | East | Monitor | 3100 |
| Sarah | East | Headset | 2700 |
| Maria | West | Laptop | 9200 |
| Maria | West | Monitor | 4200 |
| Sarah | West | Keyboard | 1300 |
Within East, the rows go 8500, 3100, 2700. Within West, they go 9200, 4200, 1300. The second sort level only matters when the first level has ties.
SORT can also handle multiple sort levels using arrays:
=SORT(A4:D9, {2,4}, {1,-1})
This does the same thing: sort by column 2 ascending, then column 4 descending. SORTBY is easier to read when sorting by more than one column.
Combining with FILTER and UNIQUE
SORT, SORTBY, FILTER, and UNIQUE are all dynamic array functions. They spill results into neighboring cells automatically, and you can nest them together.
To filter for West region only, then sort by Revenue descending:
=SORT(FILTER(A4:D9, B4:B9="West"), 4, -1)
| Salesperson | Region | Product | Revenue |
|---|---|---|---|
| Maria | West | Laptop | 9200 |
| Maria | West | Monitor | 4200 |
| Sarah | West | Keyboard | 1300 |
FILTER narrows the rows, then SORT reorders them.
To get a sorted list of unique salesperson names:
=SORT(UNIQUE(A4:A9))
The result is James, Maria, Sarah. UNIQUE removes duplicates, then SORT puts them in alphabetical order.
Key takeaway: SORT, FILTER, and UNIQUE are designed to work together. Nesting them lets you filter, deduplicate, and reorder data in a single formula.
Conclusion
SORT reorders a range by a column index. SORTBY reorders it by a separate range, which makes multi-column sorting cleaner. Both update automatically and leave the original data in place.
For more on extracting specific rows, check out our guide on the FILTER function or the UNIQUE function.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.