SORT function

The SORT function sorts the contents of a range or array. 

In this example, we're sorting by Region, Sales Rep, and Product individually with =SORT(A2:A17), copied across cells F2:H2.

SORT by Region, Sales Rep, and Product individually with =SORT(A2:A17) copied across

Note: September 24, 2018: The SORT function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

SORT returns a sorted array of the elements in an array. The returned array is the same shape as the provided array argument. 

=SORT(array,[sort_index],[sort_order],[by_col])

Argument

Description

array

Required

The range, or array to sort

[sort_index]

Optional 

A number indicating the row or column to sort by

[sort_order]

Optional

A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order

[by_col]

Optional

A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column

Notes: 

  • Where sort_index is not provided, row1/col1 will be presumed. Where order is not provided, ascending order will be presumed. By default Excel will sort by row, and will only sort by column where by_col is TRUE. When by_col is FALSE or missing Excel will sort by row.

  • The SORT function is provided to sort data in an array. If you want to sort data in the grid, it's better to use the SORTBY function, as it is more flexible. SORTBY will respect column additions/deletions, because it references a range, where SORT references a column index number.

  • An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the source array for our SORT formula is range A5:D20.

  • The SORT function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel Table, then the array will automatically resize as you add or remove data from your array range if you're using Structured References. For more details see this article on Spilled Array Behavior.

  • Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

Examples

Note: The examples shown here are available in this downloadable workbook: SORT function examples.

Sort a range of values in descending order.

Sort a range of values in descending order.

Use SORT and FILTER together to sort a range in ascending order, and limit it to values over 5,000.

Use SORT and FILTER together to sort a range in ascending order, and limit it to values over 5,000.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SINGLE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×