The by_array arguments must either be one row high, or one column wide.Īll of the arguments must be the same size. Use SORTBY to sort a table of temperature and rainfall values by high temperature. The # sign is called the spilled range operator. In this case, E2# references the dynamic array range beginning in cell E2, as that was populated by using = SEQUENCE(10). Use SORTBY with RANDARRAY, and COUNTA to randomize a list of values. Sort a table by Region in ascending order, then by each person's age, in descending order. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed. For more details, see this article on Spilled Array Behavior.Įxcel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. 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. This means that Excel will dynamically create the appropriate sized array range when you press ENTER.
The SORTBY function will return an array, which will spill if it's the final result of a formula.
In the example above, the array for our SORTBY formula is range D2:E9. An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values.
