Mastering Excel's Dynamic Array SORTBY Function: A Comprehensive Guide

Excel offers various methods for arranging data, but the new dynamic array SORTBY function provides a powerful and streamlined approach to custom sorting using formulas. This guide explores its syntax, typical uses, and troubleshooting tips.

Understanding the SORTBY Function

SORTBY sorts one array based on the values in another. It supports single or multiple column sorting and is part of Excel 365 and Excel 2021's dynamic array functionality. Results spill into neighboring cells and update automatically with data changes.

Syntax:

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

• array (required): The range or array to be sorted.
• by_array1 (required): The range or array to sort by.
• sort_order1 (optional): 1 (ascending, default) or -1 (descending).
• by_array2, sort_order2, etc. (optional): Additional sorting criteria.

Key Considerations:

• by_array arguments must be single-row or single-column ranges.
• array and all by_array arguments must have compatible dimensions (same number of rows/columns). Mismatched dimensions result in a #VALUE! error.
• Ensure sufficient empty cells for spill range to avoid #SPILL! errors. Using Excel Tables or dynamic named ranges helps manage this automatically.
• Results update automatically, but added data outside the initial array reference requires updating the formula or using a dynamic range.

Basic SORTBY Example:

Let's say you have project names (Column A) and their values (Column B). To sort projects by value (descending) without showing the values in the result:

=SORTBY(A2:A10, B2:B10, -1)

This formula, entered in a cell, will spill the sorted project names into the cells below.

Advanced SORTBY Applications:

• Multi-Column Sorting: Sort by "Status" (Column B) alphabetically, then by "Value" (Column C) descending:

  =SORTBY(A2:C10, B2:B10, 1, C2:C10, -1)

  

• Custom Sorting: Sort by a custom order of statuses (e.g., "In Progress", "Completed", "On Hold") defined in cells E2:E4:

  =SORTBY(A2:C10, MATCH(B2:B10, E2:E4, 0))

  

  This uses MATCH to assign a numerical rank based on the custom list. Adding a , -1 after the MATCH function reverses the custom sort order.

• Random Sorting: Use RANDARRAY to randomly shuffle data:

  =SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10)))

  Note: RANDARRAY is volatile; recalculating the sheet reshuffles the data. Use "Paste Special" > "Values" to fix the results.

  

• Sorting by String Length: Sort cells by text length (ascending):

  =SORTBY(A2:A7, LEN(A2:A7), 1)

  

SORTBY vs. SORT:

While both sort data, SORTBY offers flexibility by allowing sorting based on independent arrays or custom lists, unlike SORT. Both support multi-level sorting and work with other functions.

Troubleshooting:

• #VALUE! error: Check by_array dimensions and data types.
• #SPILL! error: Clear space for the spill range.
• #REF! error: Ensure referenced workbooks are open.
• #NAME? error: Ensure you're using Excel 365 or Excel 2021.

This comprehensive guide empowers you to leverage the SORTBY function for efficient and flexible data manipulation in Excel. Download the practice workbook for hands-on experience.

The above is the detailed content of Excel SORTBY function - custom sort with formula. For more information, please follow other related articles on the PHP Chinese website!