Complete Guide to Excel SORTBY: Sort Arrays by Multiple Criteria
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)The SORTBY function represents a significant advancement in Excel's data manipulation capabilities, offering users a dynamic and flexible approach to sorting arrays without manually rearranging data. Unlike traditional sorting methods that modify your original data structure, SORTBY creates a new sorted result that updates automatically when source data changes, making it invaluable for creating dynamic reports and dashboards. Introduced in Excel 365 and Excel 2021, SORTBY eliminates the need for complex helper columns or manual sorting procedures. This function handles multiple sort criteria simultaneously, allowing you to sort by primary, secondary, and tertiary columns in a single formula. Whether you're analyzing sales data, managing inventory, or organizing employee records, SORTBY provides the precision and efficiency modern data analysis demands. Understanding SORTBY transforms your Excel workflow by enabling real-time sorted views of your data without disrupting the original source. This formula works seamlessly with other dynamic array functions, creating powerful combinations that automate previously time-consuming tasks and reduce errors in data presentation.
Syntax & Parameters
The SORTBY formula syntax is structured as =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...), where each parameter serves a specific purpose in the sorting operation. The 'array' parameter is mandatory and represents the range or array containing the data you want to sort—this can be a single column, multiple columns, or a named range. The 'by_array1' parameter specifies the first sorting criterion, which must be a single column or range with the same number of rows as your main array; this column determines the primary sort order. The optional 'sort_order1' parameter accepts either 1 (ascending, default) or -1 (descending) to control whether values sort from smallest to largest or vice versa. For multi-level sorting, you can add 'by_array2' and 'sort_order2' parameters to establish secondary and tertiary sort criteria, with Excel evaluating each level sequentially. A crucial practical tip: ensure all by_array parameters contain the same number of rows as your primary array to avoid errors. The sort order parameter is position-sensitive—it must immediately follow its corresponding by_array. Additionally, SORTBY returns a dynamic array, meaning the result automatically updates when source data changes, and you can reference the entire result without array formula syntax in Excel 365.
arrayby_array1sort_order1Practical Examples
Sales Performance Ranking by Revenue
=SORTBY(A2:C50, C2:C50, -1)This formula sorts the entire sales table (columns A through C, rows 2-50) by the revenue column (C) in descending order (-1). The result displays highest-performing employees first, automatically updating when new sales data is entered.
Multi-Level Sort: Department and Salary
=SORTBY(A2:D100, B2:B100, 1, D2:D100, -1)This formula applies a two-level sort: first by department (B column) in ascending order (1), then by salary (D column) in descending order (-1). Employees appear grouped by department with highest earners listed first within each group.
Inventory Management: Stock Levels and Reorder Status
=SORTBY(A2:D200, C2:C200, 1, A2:A200, 1)This formula sorts inventory by stock level (C column) ascending, placing lowest stock items first. Secondary sort by product name (A column) alphabetically helps organize items within each stock level group.
Key Takeaways
- SORTBY provides dynamic, formula-based sorting that automatically updates when source data changes, eliminating manual sorting procedures.
- Multi-level sorting is supported through multiple by_array and sort_order pairs, enabling complex data organization in a single formula.
- SORTBY preserves original data integrity by creating new sorted arrays rather than modifying source ranges in place.
- Combining SORTBY with FILTER and UNIQUE creates powerful dynamic dashboards and reports that reduce manual data manipulation.
- SORTBY requires Excel 365 or Excel 2021; earlier versions need alternative methods like INDEX/MATCH combinations or manual sorting.
Pro Tips
Use SORTBY with FILTER to create dynamic dashboards that automatically display sorted filtered data. This combination eliminates the need for manual data refreshing and reduces errors in reporting.
Impact : Saves significant time on report generation and ensures data accuracy by removing manual sorting steps from your workflow.
When sorting by multiple criteria, remember that sort order matters—Excel processes by_array parameters left to right. Place your most important sort criteria first, then secondary and tertiary criteria in order of importance.
Impact : Ensures your data displays in the exact hierarchy you need without requiring formula restructuring or complex workarounds.
Combine SORTBY with named ranges for cleaner, more maintainable formulas. Instead of =SORTBY(A2:D100, C2:C100, -1), use =SORTBY(SalesData, Revenue, -1) if you've named your ranges appropriately.
Impact : Improves formula readability, makes maintenance easier, and reduces errors when data ranges expand or contract.
Test SORTBY formulas with small datasets first before applying to large ranges. This helps identify issues with sort order values or array mismatches before they affect your entire dataset.
Impact : Prevents errors in production formulas and helps you understand SORTBY behavior before implementing in critical business reports.
Useful Combinations
SORTBY with FILTER for Conditional Sorted Views
=SORTBY(FILTER(A2:D100, B2:B100="Sales"), D2:D100, -1)This combination first filters the dataset to show only 'Sales' department records, then sorts the filtered results by salary (column D) in descending order. This creates a dynamic view showing top-earning sales employees, updating automatically when data changes.
SORTBY with UNIQUE for Sorted Distinct Values
=SORTBY(UNIQUE(A2:A100), UNIQUE(A2:A100), 1)Combines UNIQUE to eliminate duplicate values with SORTBY to arrange them alphabetically. Useful for creating sorted lists of unique regions, departments, or product categories without manual deduplication.
SORTBY with SEQUENCE for Ranking Results
=HSTACK(SORTBY(A2:C50, C2:C50, -1), SEQUENCE(ROWS(SORTBY(A2:C50, C2:C50, -1))))Combines SORTBY results with SEQUENCE to automatically generate ranking numbers. Creates a ranked list where the first row receives rank 1, second row rank 2, etc., maintaining accuracy even when source data updates.
Common Errors
Cause: The by_array parameter contains a different number of rows than the main array, or sort_order parameter contains a value other than 1 or -1.
Solution: Verify that all array ranges have identical row counts. Check sort_order values are either 1 (ascending) or -1 (descending). Use =ROWS(array) to confirm matching dimensions across all parameters.
Cause: One or more range references in the formula are invalid, deleted, or point to closed workbooks.
Solution: Review all cell references in the formula. Ensure source data ranges still exist and contain valid data. Reconstruct the formula using the Name Box to select ranges directly, preventing reference errors.
Cause: SORTBY function is not recognized, typically because you're using Excel 2019 or earlier versions that don't support this dynamic array function.
Solution: Upgrade to Excel 365 or Excel 2021 to access SORTBY functionality. For earlier versions, use alternative methods like Sort dialog, RANK combined with INDEX/MATCH, or helper columns with manual sorting.
Troubleshooting Checklist
- 1.Verify all array ranges contain the same number of rows—use =ROWS() to compare dimensions across parameters.
- 2.Confirm sort_order parameters contain only 1 (ascending) or -1 (descending); other values trigger #VALUE! errors.
- 3.Check that by_array references are single columns, not multi-column ranges, as SORTBY requires single-column sort criteria.
- 4.Ensure you're using Excel 365 or Excel 2021; SORTBY is not available in Excel 2019 or earlier versions.
- 5.Verify source data contains no circular references that might cause SORTBY to fail or produce unexpected results.
- 6.Test formula with a small data subset first to identify issues before applying to large production datasets.
Edge Cases
Sorting array with empty cells in the by_array column
Behavior: SORTBY treats empty cells as zero values for numeric columns or places them at the beginning for text columns when sorting ascending. This may not produce expected results if empty cells should be excluded.
Solution: Use FILTER to remove empty cells before sorting: =SORTBY(FILTER(array, by_array<>""), by_array, sort_order)
Alternatively, fill empty cells with placeholder values that sort to desired positions.
Using SORTBY with mixed data types in sort column (numbers stored as text)
Behavior: Excel sorts text-formatted numbers differently than true numeric values. Text numbers sort lexicographically ("10" before "2"), not numerically.
Solution: Convert text to numbers using VALUE function or ensure consistent data types. Verify data formatting before building sort formulas.
This is a common issue when importing data from external sources with inconsistent formatting.
SORTBY with very large datasets (100,000+ rows)
Behavior: Performance may degrade as SORTBY processes massive arrays. Formula calculation time increases, potentially causing noticeable delays in workbook responsiveness.
Solution: Consider breaking data into smaller subsets or using database solutions for very large datasets. Test performance with sample data first.
Excel's dynamic array engine handles most datasets efficiently, but extremely large arrays may warrant alternative approaches.
Limitations
- •SORTBY is only available in Excel 365 and Excel 2021, making it unavailable for users with Excel 2019 or earlier versions, limiting its use in organizations with legacy Excel deployments.
- •SORTBY cannot sort by custom lists or non-standard sort orders; it only supports ascending (1) or descending (-1) for each sort criterion, requiring workarounds for specialized sorting needs.
- •The function returns results as dynamic arrays that cannot be directly edited or manipulated within the formula result range, limiting flexibility in post-sort data modification.
- •Performance may be impacted with extremely large datasets (100,000+ rows), as SORTBY must process entire arrays in memory, potentially causing calculation delays in complex workbooks.
Alternatives
Compatibility
✓ Excel
Since Excel 365 and Excel 2021
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)✓Google Sheets
=SORT(array, sort_column, is_ascending, [sort_column2, is_ascending2], ...)Google Sheets uses SORT function with different parameter structure. The is_ascending parameter uses TRUE/FALSE instead of 1/-1. Functionality is similar but syntax differs significantly.
✗LibreOffice
Not available