Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

In this example, we're sorting a list of people's names by their age, in ascending order.

Use SORTBY to sort a range. In this case, we used =SORTBY(D2:E9,E2:E9) to sort a list of people's names by their age, in ascending order.

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) 

Argument

Description

array

Required

The array or range to sort

by_array1

Required

The array or range to sort on

[sort_order1]

Optional

The order to use for sorting. 1 for ascending, -1 for descending. Default is ascending.

[by_array2]

Optional

The array or range to sort on

[sort_order2]

Optional

The order to use for sorting. 1 for ascending, -1 for descending. Default is ascending.

Notes: 

  • 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 array for our SORTBY formula is range D2:E9.

  • The SORTBY 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

Sort a table by Region in ascending order, then by each person's age, in descending order.

Sort a table, by Region in ascending order, then by each person's age, in descending order.

Use SORTBY with RANDARRAY, and COUNTA to randomize a list of values. In this case, E2# references the dynamic array range beginning in cell E2, as that was populated by using =SEQUENCE(10). The # sign is called the spilled range operator.

Use SORTBY with RANDARRAY, and COUNTA. In this case, E2# references the entire range beginning in cell E2, as that was populated using =SEQUENCE(10). The # sign is called the spilled range operator.

Use SORTBY to sort a table of temperature and rainfall values by high temperature.

Use SORTBY to sort a table of temperature and rainfall values by high temperature.

Error conditions

  • The by_array arguments must either be one row high, or one column wide.

  • All of the arguments must be the same size.

  • If the sort order argument is not -1, or 1, the formula will result in a #VALUE! error. If you leave out the sort order argument, Excel will default to ascending order.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SORT function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Implicit intersection operator: @

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×