SORTBY: Sort by Separate Columns (2025)
Quick Answer: SORTBY sorts by external range: =SORTBY(A:C, D:D, -1) sorts A:C by column D descending. Multiple keys: =SORTBY(data, col1, 1, col2, -1) sorts by col1 asc then col2 desc. Use when sort key outside data or calculated: =SORTBY(A:B, LEN(A:A)) sort by text length.
What is SORTBY?
SORTBY is a dynamic array function in Excel 365 that sorts data based on values in separate columns or ranges. Unlike SORT which uses columns within the data, SORTBY lets you sort by external criteria, calculated values, or columns you don't want in the result.
⚠️ Excel 365 Only
SORTBY requires Excel 365 subscription with dynamic arrays. Not available in Excel 2019, 2016, or earlier.
Alternative for older Excel: Helper column with sort key, then use Data → Sort or SORT function.
SORTBY vs SORT:
SORT (Internal)
=SORT(A:C, 2, -1)
Sorts A:C by column B (position 2 within A:C)
Sort key must be IN the data range
SORTBY (External)
=SORTBY(A:C, D:D, -1)
Sorts A:C by column D (separate from result)
Sort key can be OUTSIDE data range
Why Use SORTBY?
✓ Use SORTBY when:
- • Sort key not in result range
- • Sort by calculated values
- • Sort by helper column data
- • Multiple separate sort keys
- • Sort by non-adjacent columns
- • Custom sort logic needed
✓ Use SORT when:
- • Sort key within data range
- • Simple column-based sorting
- • Cleaner syntax for basic sorts
- • Sort by column position
- • Standard sorting needs
Common Use Cases:
✓ Sort by Text Length
=SORTBY(A2:A100, LEN(A2:A100)) - shortest to longest
✓ Sort by Calculated Priority
=SORTBY(Tasks, Urgency*2 + Importance) - weighted score
✓ Sort by Hidden Column
Display A:C sorted by hidden column D
✓ Custom Sort Order
Sort by lookup value from separate table
Key Advantages:
No helper columns needed: Calculate sort keys directly in formula
Multiple sort keys: Sort by multiple separate criteria easily
Dynamic updates: Results refresh automatically when data changes
Flexible logic: Use formulas, calculations, lookups as sort keys
💡 When to Choose SORTBY
Choose SORTBY if: Your sort criterion isn't in the data you want to return, or you need to sort by a calculated value (like LEN, MONTH, custom formula).
Choose SORT if: You're sorting by a column that's already in your result range. SORT is simpler with cleaner syntax for basic sorting.
SORTBY Syntax
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
Arguments
array (required)
Range or array to sort
by_array1 (required)
First column/range to sort by
Can be: column reference (D:D), formula (LEN(A:A)), calculated range
Must be same height as array
sort_order1 (optional, default 1)
Sort direction for by_array1
1 or omitted: Ascending (A-Z, 1-9, oldest first)
-1: Descending (Z-A, 9-1, newest first)
by_array2, sort_order2, ... (optional)
Additional sort keys and orders
Common Patterns
Sort by External Column
=SORTBY(A2:C100, D2:D100, -1)
Sort A:C by column D (descending)
Sort by Calculation
=SORTBY(A2:B100, LEN(A2:A100))
Sort by text length
Multiple Sort Keys
=SORTBY(A2:C100, D2:D100, 1, E2:E100, -1)
Sort by D ascending, then E descending
Sort by Formula
=SORTBY(A2:B100, B2:B100*C2:C100, -1)
Sort by product of two columns
Array Size Requirements
⚠️ Critical: by_array must match array height
• If array = A2:C100 (99 rows), by_array must also have 99 rows
• by_array = D2:D100 ✓ (99 rows - matches)
• by_array = D:D ✓ (full column - Excel matches automatically)
• by_array = D2:D50 ✗ (49 rows - ERROR!)
Multi-Level Sorting
Two-Level Sort
Priority: Column D ascending, then column E descending
Three-Level Sort
Sort by D asc → E desc → F asc
Mixed: Column + Formula
Sort by column D, then by name length
⚠️ Important Notes
- • Excel 365 only: Not available in Excel 2019 or earlier
- • Array height must match: by_array same rows as array or #VALUE! error
- • Spills automatically: Results fill multiple cells - ensure space is clear
- • Source unchanged: Creates sorted copy, doesn't modify original
- • Formula evaluation: Calculations in by_array recalculate automatically
- • #SPILL! error: Blocked by data - clear cells or move formula
- • Performance: Complex formulas in by_array can be slow for large datasets
- • Priority order: Sorts by first pair, then second, then third (left to right)
10+ SORTBY Examples
Sort by External Column
1. Sort Names by Priority
=SORTBY(A2:B100, C2:C100, -1)
Display names + emails, sorted by priority column (hidden)
2. Sort Products by Stock Level
=SORTBY(A2:C100, D2:D100)
Show products sorted by stock (D not in result)
3. Sort Tasks by Due Date
=SORTBY(A2:B100, C2:C100)
Tasks sorted by date, date column excluded from result
Sort by Calculated Values
4. Sort by Text Length
=SORTBY(A2:A100, LEN(A2:A100))
Shortest names first
5. Sort by Product Total
=SORTBY(A2:B100, B2:B100*C2:C100, -1)
Sort by Quantity × Price (highest total first)
6. Sort by Month Number
=SORTBY(A2:C100, MONTH(D2:D100))
Sort by month extracted from date
Multi-Level Sorting
7. Two-Level: Category then Price
=SORTBY(A2:C100, B2:B100, 1, C2:C100, -1)
Sort by category A-Z, then price high to low
8. Three-Level: Region, Status, Date
=SORTBY(A2:B100, C2:C100, 1, D2:D100, 1, E2:E100, -1)
Sort by region, then status, then date newest first
Advanced Applications
9. Sort by Custom Priority
=SORTBY(A2:C100, XLOOKUP(B2:B100, Priority[Category], Priority[Score]), -1)
Sort by looked-up priority score from separate table
10. Sort Filtered Data
=SORTBY(FILTER(A2:C100, D2:D100="Active"), E2:E100, -1)
Filter active records, sort by external column
11. Sort by Absolute Value
=SORTBY(A2:B100, ABS(B2:B100), -1)
Sort by magnitude regardless of positive/negative
🎯 Pro Tips
- • Use when sort key excluded: SORTBY perfect when you don't want sort column in result
- • Formula-based sorting: Calculate sort keys on the fly without helper columns
- • Match array sizes: Ensure by_array has same rows as data array
- • Multiple criteria: Add more pairs for complex multi-level sorting
- • Combine with FILTER: Filter first, then sort for dynamic reports
- • Named ranges: Make formulas more readable: =SORTBY(Names, Priority, -1)
- • Performance: Simple references faster than complex formulas in by_array
Frequently Asked Questions
When should I use SORTBY instead of SORT?
Use SORTBY when: (1) Sort key not in result range, (2) Sorting by calculated values (LEN, MONTH, formulas), (3) Sorting by lookup values from other tables, (4) Want to exclude sort column from display. Use SORT when sort key is within the data you're returning - it's simpler. Example: SORTBY(A:B, C:C) excludes C from result. SORT(A:C, 3) includes C.
Can I sort by multiple separate columns?
Yes, add more by_array/sort_order pairs: =SORTBY(A:B, C:C, 1, D:D, -1, E:E, 1). Sorts by C ascending, then D descending, then E ascending. Up to 64 pairs supported. Priority is left to right - first pair has highest priority. All by_arrays must have same height as main array.
How do I sort by a formula without helper columns?
Put formula directly in by_array: =SORTBY(A2:B100, LEN(A2:A100)) sorts by text length. =SORTBY(A:B, B:B*C:C, -1) sorts by product. =SORTBY(Names, XLOOKUP(Names, Table[Name], Table[Priority])) sorts by lookup value. Formula recalculates automatically when data changes. No helper columns needed!
What happens if by_array size doesn't match?
#VALUE! error. by_array must have exact same number of rows as array. If array = A2:C100 (99 rows), by_array must also be 99 rows. Use full column references (D:D) and Excel matches automatically. Or use same row numbers (D2:D100). Common mistake: array starts row 2, by_array starts row 1 - sizes don't match.
Can SORTBY handle errors in the sort column?
Yes, but errors sort to bottom. #N/A, #DIV/0!, #VALUE! all appear at end regardless of sort order. Workaround: wrap by_array in IFERROR: =SORTBY(A:B, IFERROR(C:C,0), -1) replaces errors with 0. Or use FILTER to exclude errors first: =SORTBY(FILTER(A:B, NOT(ISERROR(C:C))), C:C).
How does SORTBY performance compare to SORT?
SORTBY slightly slower if using complex formulas in by_array (recalculates for every cell). Simple column references (D:D) perform similarly to SORT. For large datasets (100k+ rows), consider: (1) Helper column for complex calculations, (2) SORT if possible, (3) Limit range instead of full columns. For typical use (<10k rows), performance difference negligible.