SORT
Sorts the contents of a range or array.
=SORT(array, [sort_index], [sort_order], [by_col])This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Sorts the contents of a range or array.
The SORT function is a powerful dynamic array formula that reorders a range or array without altering the original source data. It is highly effective for creating real-time dashboards where data must remain structured and up-to-date. A key technical aspect is its ability to handle both row-based (default) and column-based sorting via the [ol] argument. Best practices involve using SORT in conjunction with the FILTER function to organize specific subsets of data or nesting it within UNIQUE to provide sorted distinct lists. Users should be aware that the function returns a spilled array, which requires sufficient empty space in the worksheet to avoid spill conflicts. Edge cases include sorting ranges that contain blanks, which are typically placed at the end of an ascending sort, and handling data types like dates which are sorted by their underlying serial numbers.
Quick reference
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])Inputs
Arguments
Example: A2:C100
Example: 2
Example: -1
Example: FALSE
Formula patterns
Examples
Basic ascending sort by first column
=SORT(A2:B10, 1, 1)Descending sort by third column
=SORT(A2:D50, 3, -1)Horizontal sort across columns
=SORT(A1:F2, 1, 1, TRUE)Avoid these issues
Common Errors
#SPILL!
Cause: The output range contains existing data or merged cells blocking the results from populating.
Fix: Clear the cells in the area where the formula needs to 'spill' or unmerge cells in the destination range.
#VALUE!
Cause: The [sort_index] provided is either less than 1 or greater than the number of columns (or rows) in the source array.
Fix: Check the dimensions of your source array and ensure the [sort_index] corresponds to a valid column or row number within that range.
Platform support
Compatibility
Dynamic array function. Excel 2021+ and Microsoft 365
Source: Microsoft Support
Common questions
Frequently Asked Questions
Sorts the contents of a range or array.
array: The range to sort ndex: Column number to sort by (1 = first column) rder: 1 = Ascending, -1 = Descending ol: TRUE to sort by column, FALSE by row
#SPILL!: Clear the cells in the area where the formula needs to 'spill' or unmerge cells in the destination range. #VALUE!: Check the dimensions of your source array and ensure the [ndex] corresponds to a valid column or row number within that range.