Study guide
11.2 Multi-Level Sorting: SORT & SORTBY Functions
Microsoft Excel Expert Microsoft 365 Apps
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & MO-211 Fundamentals
1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture
2 Chapter 2: Managing Workbooks & File Options
2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution
3 Chapter 3: Workbook Protection & Collaboration Settings
3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations
4 Chapter 4: Advanced Data Entry & Series
4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)
5 Chapter 5: Custom Formatting & Data Validation
5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing
6 Chapter 6: Data Grouping, Summarization, & Cleansing
6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing
7 Chapter 7: Advanced Conditional Formatting & Filtering
7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges
8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions
8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
9 Chapter 9: Advanced Lookup Functions & Variable Optimization
9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET
10 Chapter 10: Date, Time, Text, & Auditing Functions
10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools
11 Chapter 11: Dynamic Array Functions & Spill Operations
11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions
12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic
13 Chapter 13: Macro Automation & VBA Fundamentals
13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor
14 Chapter 14: Advanced & Specialized Visualizations
14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts
15 Chapter 15: PivotTable Construction & Field Configuration
15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences
16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts
16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts
11.2 Multi-Level Sorting: SORT & SORTBY Functions
Key Takeaways Explain This Topic AI
The SORT function dynamically sorts an array by an internal column or row index, defaulting to ascending order (1) on the first column.
Multi-column sorting in SORT is achieved by supplying array constants for both sort_index (e.g., {2, 4}) and sort_order (e.g., {1, -1}).
SORTBY provides superior architectural flexibility by sorting a target array against external criteria vectors that do not need to be present in the returned output.
Specifying a sort_index greater than the total number of columns in the source array causes SORT to return a #VALUE! error.
Nesting SORT or SORTBY with FILTER creates automated leaderboards, ranking models, and executive dashboards that recalculate dynamically as source records change.
Last updated: September 2026
Multi-Level Sorting: SORT & SORTBY Functions
Sorting data is an essential operation in financial reporting, performance ranking, and dataset organization. In traditional Excel workflows, sorting required selecting a range and invoking the static Data > Sort dialog. While effective for one-time audits, static sorting has significant drawbacks in enterprise models: it permanently reorders source rows, disrupts relative cell references, destroys manual record sequences, and requires manual re-sorting whenever source values change.
The dynamic array functions SORT and SORTBY provide non-destructive, formula-driven sorting. Rather than modifying the underlying table, these functions read the source data and output an ordered, spilled array in a new location. Whenever source records are edited, appended, or recalculated, the sorted spill range updates instantaneously.
Anatomy and Argument Architecture of SORT
The SORT function reorders an array based on values within one or more of its own columns or rows. Its syntax is: =SORT(array, [sort_index], [sort_order], [by_col])
Argument Required / Optional Default Value Description & Rules
array Required None The range or array to sort.
[sort_index] Optional 1 A 1-based number indicating the column (or row) within array to sort by. Can also be an array constant of indices (e.g., {1, 2} ).
[sort_order] Optional 1 1 for ascending order (A to Z, smallest to largest); -1 for descending order (Z to A, largest to smallest). Can also be an array constant (e.g., {1, -1} ).
[by_col] Optional FALSE FALSE (or omitted) sorts vertically by row (standard). TRUE sorts horizontally by column.
Single-Column Sort Example
Given employee records in A2:D50 where column A is Name, column B is Department, column C is Tenure, and column D is Salary: =SORT(A2:D50, 4, -1)
This formula sorts the entire table A2:D50 by column 4 (Salary) in descending order ( -1 ), spilling the ranked roster into the worksheet.
Multi-Level Sorting in SORT Using Array Constants
Many business reports require multi-tiered sorting—for example, grouping employees by Department alphabetically (ascending), and then sorting employees within each department by Salary from highest to lowest (descending).
To execute multi-level sorting within SORT , pass array constants enclosed in curly brackets {} to both sort_index and sort_order : =SORT(A2:D50, {2, 4}, {1, -1}) Evaluation Breakdown:
- Primary Sort Key: Index 2 (Department) --> Order: 1 (Ascending / A-to-Z)
- Secondary Sort Key: Index 4 (Salary) --> Order: -1 (Descending / High-to-Low)
Both array constants must match in length: if you supply two column indices in {2, 4} , you must supply two sort orders in {1, -1} . If sort_order is omitted, Excel defaults to ascending order ( 1 ) for all specified indices.
Anatomy and Mechanics of SORTBY
While SORT relies on numerical column indices within the source array, SORTBY sorts an array against one or more external criteria ranges or arrays. Its syntax is: =SORTBY(array, by_array1, [order1], [by_array2], [order2], ...)
Argument Required / Optional Default Value Description & Rules
array Required None The range or array to sort and return.
by_array1 Required None The first range or array vector to sort by. Must have the same number of rows (or columns) as array .
[order1] Optional 1 1 for ascending, -1 for descending.
[by_array2] Optional None The second criteria vector for secondary sorting.
[order2] Optional 1 1 for ascending, -1 for descending.
The Power of External Criteria Sorting
The defining advantage of SORTBY is that the sort vectors do not need to be included in the returned output .
Suppose a company directory spans A2:D100 (Name in A, Department in B, Tenure in C, Salary in D). An executive dashboard requires a clean list showing only Employee Names (column A), sorted primarily by Department (ascending) and secondarily by Salary (descending).
With SORT , achieving this requires sorting all four columns and then slicing column A using CHOOSECOLS or INDEX . With SORTBY , it is resolved in a single, intuitive expression: =SORTBY(A2:A100, B2:B100, 1, D2:D100, -1)
Excel reads the name vector A2:A100 , pairs each row with its corresponding Department ( B2:B100 ) and Salary ( D2:D100 ), executes the multi-level sort, and outputs only column A.
Comparative Analysis: SORT vs. SORTBY
Dimension SORT Function SORTBY Function
Sort Key Specification Numeric column/row index within array (e.g., 2 or {2, 4} ) Cell range or array reference (e.g., B2:B100 )
Multi-Level Syntax Array constants: {2, 4} , {1, -1} Paired arguments: by_array1, order1, by_array2, order2
External Vectors Unsupported (sort key must exist inside array ) Fully supported (sort vectors can reside anywhere)
Output Slicing Always returns all columns of array unless sliced Naturally isolates target columns without extra functions
Horizontal Sorting Supported via by_col: TRUE Supported when vectors are horizontal rows
Best Used For Reordering complete, self-contained multi-column tables Returning targeted columns or sorting by calculated metrics
Constructing Dynamic Automated Leaderboards: SORT with FILTER
In financial modeling and operational management, a common requirement is building self-updating leaderboards: extracting top-performing records that satisfy a specific business threshold.
Combining SORT (or SORTBY ) with FILTER creates an automated analytical pipeline: =SORT(FILTER(A2:E50, E2:E50>=100000, "No Qualifiers"), 5, -1) Execution Pipeline:
Step 1: FILTER(A2:E50, E2:E50>=100000, "No Qualifiers")
└── Extracts rows where Revenue (col E) >= $100,000.
Step 2: SORT(..., 5, -1)
└── Takes the filtered result and sorts descending by column 5 (Revenue).
Result: An instantaneous, dynamically updated leaderboard of high-revenue accounts.
To display only the Top 5 accounts from this sorted list, modern Excel users can wrap the formula in TAKE : =TAKE(SORT(FILTER(A2:E50, E2:E50>=100000), 5, -1), 5)
Whenever transactions update in A2:E50 , the leaderboard recalculates, re-filters, re-sorts, and re-spills automatically.
High-Frequency MO-211 Exam Traps
Sort Index Exceeds Column Count ( #VALUE! ) : Entering =SORT(A2:C50, 4, 1) produces a #VALUE! error because range A2:C50 contains only 3 columns. The sort_index must be an integer between 1 and the total number of columns in array .
Dimension Mismatch in SORTBY ( #VALUE! ) : In =SORTBY(A2:B50, C2:C60, 1) , the output array contains 49 rows while the sort vector contains 59 rows. Every by_array argument must have the identical length along the sorting axis as array .
Invalid Sort Order Values : Excel requires 1 (ascending) or -1 (descending). Entering 0 , FALSE , or letters like "D" triggers formula syntax or calculation errors.
Using Column Letters Instead of Indices in SORT : Writing =SORT(A2:D50, B, 1) or =SORT(A2:D50, "B", 1) is invalid. SORT requires numeric indices (e.g., 2 ). To refer to ranges by column reference, use SORTBY .
Unbalanced Multi-Level Arrays : If you provide {1, 2, 3} for sort_index and {1, -1} for sort_order , the third column's sort behavior may fail or default unexpectedly. Always balance index and order arrays.