The normal Sort command is useful when you want to rearrange a worksheet. The SORT function is different: it returns a sorted array as a formula result, which is useful when you want a separate, automatically updated view of your data.
Basic SORT syntax
=SORT(array,[sort_index],[sort_order],[by_col])The sort_index identifies the row or column used for sorting, while sort_order is 1 for ascending order and -1 for descending order.
Example: sort a list of sales
=SORT(A2:C20,3,-1)This returns the rows from A2:C20 ordered by the third column from largest to smallest.
SORT with FILTER
=SORT(FILTER(A2:C20,B2:B20="North",""),3,-1)This creates a filtered North-region result and then sorts it by the third column in descending order.
SORT with UNIQUE
=SORT(UNIQUE(B2:B100))This is useful when you need an automatically refreshed alphabetical list of distinct values.
SORT versus SORTBY
SORT works well when the sort position is known. SORTBY is often more flexible when you want to specify the actual range or column that determines the order, especially as worksheet columns change.
Common problems
- Wrong column sorted: check the
sort_index. - Unexpected text order: check whether values are actually numbers or dates rather than text.
- #SPILL!: clear cells blocking the returned array.
- Result does not include new rows: use an Excel Table or another expanding source reference.
Compatibility note
SORT is a dynamic-array function available in newer Excel versions. Confirm compatibility when distributing workbooks to users on older releases.
Practical checklist
- Choose ascending or descending order deliberately.
- Verify the sort column contains consistently typed values.
- Use SORT for formula-driven views and the normal Sort command when you intend to rearrange the source data.
- Use SORT with FILTER or UNIQUE when building dynamic reports.
- Leave room for the spilled result.
Migration 074: SORT refinement.
Sorting by a different column
SORT uses the position of the sort column inside the array. For example, if A:D contains Order, Region, Customer and Sales, this sorts the whole result by Sales from largest to smallest:
=SORT(A2:D100,4,-1)The 4 refers to the fourth column of the supplied array, not necessarily column D on the worksheet.
When SORTBY is a better choice
If the sort rule should refer to a separate range or should remain stable when worksheet columns are inserted or moved, consider SORTBY. It sorts one array using another range or array as the sort key. That can be easier to maintain in workbooks that change over time.
=SORTBY(A2:D100,D2:D100,-1)SORT, SORTBY, and the normal Sort command
| Need | Better starting point |
|---|---|
| Rearrange the source data itself | Data > Sort |
| Create a formula-driven sorted view | SORT |
| Sort using one or more separate ranges | SORTBY |
Choosing the right method matters. A formula-driven view leaves the source data in place, while the normal Sort command changes the order of the selected dataset.
Why SORT can return #SPILL!
SORT returns a dynamic array. If cells in the intended output area contain data, or if the result would extend beyond the worksheet, Excel may return #SPILL!. Clear the obstruction or use a smaller source range.
Version note
Microsoft currently lists SORT for Microsoft 365 and Excel 2021 and later releases. If a workbook must support older Excel versions, verify compatibility before relying on dynamic-array formulas.