The SORT function is a Dynamic Array function that automatically sorts data in ascending or descending order.
It updates instantly when the source data changes.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
Arguments:
- array → The range to sort
- sort_index (optional) → Column number to sort by
- sort_order (optional) → 1 = Ascending, -1 = Descending
- by_col (optional) → TRUE = sort by column, FALSE = sort by row
Basic Example (Single Column)
If A2:A10 contains numbers:
=SORT(A2:A10)
Sorts numbers in ascending order.
Descending Order
=SORT(A2:A10, 1, -1)
Sorts values in descending order.
Sorting Multiple Columns
If you have:
| Name | Department | Salary |
To sort by Salary:
=SORT(A2:C10, 3, -1)
Here:
- 3 = Salary column
- -1 = Descending order
Sorting by Text
=SORT(A2:A10)
Sorts text alphabetically (A–Z).
SORT with Tables (Recommended)
If your table name is Employees:
=SORT(Employees, 3, -1)
Sorts entire table by third column.
Combine SORT with FILTER
=SORT(FILTER(A2:C10, B2:B10="IT"), 3, -1)
Filters IT department and sorts by Salary (descending).
Combine SORT with UNIQUE
=SORT(UNIQUE(A2:A10))
Returns sorted unique values.
Common Errors
#SPILL!
Occurs when the output range is blocked.
Important Notes
- Available in Excel 365 and Excel 2021+
- Automatically spills results
- Updates dynamically when data changes
- Does not modify original data
SORT vs Manual Sorting
| SORT Function | Manual Sort |
|---|---|
| Dynamic | Static |
| Formula-based | One-time action |
| Updates automatically | Requires re-sorting |
Benefits of SORT
- Automatic updates
- Cleaner formulas
- Ideal for dashboards
- No need to manually sort data
- Works perfectly with FILTER and UNIQUE
Conclusion
The SORT function is a powerful modern Excel feature that automatically arranges data in the desired order. It simplifies data organization and enhances dynamic reporting and analysis.