SORT: Dynamic Array Sorting (2025)
Quick Answer: SORT automatically sorts data: =SORT(A2:C100, 2, -1) sorts by column 2 descending. 1=ascending, -1=descending. Multi-level: =SORT(data, {2,1}, {-1,1}) sorts by col 2 desc, then col 1 asc. Combine with FILTER: =SORT(FILTER(data, criteria)) for filtered sorted results.
What is SORT?
SORT is a dynamic array function in Excel 365 that automatically sorts data by one or more columns. Results update instantly when source data changes - perfect for dashboards and reports.
⚠️ Excel 365 Only
SORT requires Excel 365 subscription with dynamic arrays. Not available in Excel 2019, 2016, or earlier.
Alternative for older Excel: Use Data → Sort (manual) or LARGE/SMALL with INDEX for formula-based sorting.
Before & After:
Unsorted Data:
| Name | Sales |
|---|---|
| John | 450 |
| Sarah | 820 |
| Mike | 380 |
| Lisa | 950 |
Sorted by Sales (Descending):
=SORT(A2:B5, 2, -1)
| Name | Sales |
|---|---|
| Lisa | 950 |
| Sarah | 820 |
| John | 450 |
| Mike | 380 |
Why Use SORT?
✓ SORT Advantages:
- • Automatic updates with data changes
- • Formula-based (auditable)
- • Combine with FILTER, UNIQUE
- • Multi-level sorting in one formula
- • No manual sorting needed
- • Perfect for dashboards
✗ Manual Sort:
- • Requires manual refresh
- • Not auditable/traceable
- • Can't combine with formulas
- • Multiple clicks for multi-level
- • Source data gets reordered
- • Breaks easily
Common Use Cases:
✓ Top Performers List
Sort sales data by revenue descending - updates automatically
✓ Alphabetical Lists
Sort names, products, categories A-Z automatically
✓ Date-Based Reports
Sort by date newest/oldest first - always current
✓ Filtered & Sorted
Combine FILTER and SORT for dynamic reports
Sort Order:
Ascending (1 or omitted)
Numbers: 1, 2, 3, 4, 5...
Text: A, B, C, D...
Dates: Oldest first
Descending (-1)
Numbers: ...5, 4, 3, 2, 1
Text: ...D, C, B, A
Dates: Newest first
💡 Dynamic Array Spilling
SORT spills results: Formula in one cell, sorted results fill cells automatically. Ensure space below is empty or #SPILL! error occurs.
Source unchanged: SORT creates new sorted array, doesn't modify original data. Perfect for keeping source data intact.
SORT Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
Arguments
array (required)
Range or array to sort
sort_index (optional, default 1)
Column or row number to sort by
Single: 2 (sort by 2nd column)
Multiple: {2,1} (sort by col 2, then col 1)
Default: 1 (first column)
sort_order (optional, default 1)
Sort direction
1 or omitted: Ascending (A-Z, 1-9, oldest first)
-1: Descending (Z-A, 9-1, newest first)
Multiple: {-1,1} (1st desc, 2nd asc)
by_col (optional, default FALSE)
Sort direction
FALSE or omitted: Sort by rows (most common)
TRUE: Sort by columns (rare)
Common Patterns
Sort by First Column (Ascending)
=SORT(A2:C100)
Default: sorts by column 1, A-Z
Sort by Specific Column (Descending)
=SORT(A2:C100, 2, -1)
Sort by column 2, highest to lowest
Multi-Level Sort
=SORT(A2:C100, {2,1}, {-1,1})
Sort by col 2 desc, then col 1 asc
Sort Filtered Data
=SORT(FILTER(A2:C100, B2:B100>500), 2, -1)
Filter then sort results
Multi-Level Sorting
Array Method (Recommended)
Sort by col 3 asc, then col 2 desc, then col 1 asc. Arrays must be same length.
Nested Method (More Readable)
Sorts from innermost to outermost. Easier to debug but longer formula.
SORT vs SORTBY
SORT
=SORT(A:C, 2, -1)
Sorts data by column within same range
Use when sort key is in result
SORTBY
=SORTBY(A:C, D:D, -1)
Sorts data by separate range/column
Use when sort key not in result
⚠️ Important Notes
- • Excel 365 only: Not available in Excel 2019 or earlier
- • Sort index numbering: 1 = first column in array (not Excel column letter)
- • Spills automatically: Results fill multiple cells - ensure space is clear
- • Source unchanged: Creates sorted copy, doesn't modify original
- • Updates automatically: When source data changes, sorted result updates
- • #SPILL! error: Blocked by data in output range - clear cells or move formula
- • Blank handling: Blanks sort to bottom by default
- • Case insensitive: Text sorting ignores uppercase/lowercase
10+ SORT Examples
Basic Sorting
1. Sort Alphabetically
=SORT(A2:C100)
Default: sorts by first column A-Z
2. Sort by Sales (Descending)
=SORT(A2:C100, 2, -1)
Highest sales first
3. Sort by Date (Newest First)
=SORT(A2:C100, 3, -1)
Most recent dates at top
Multi-Level Sorting
4. Sort by Two Columns
=SORT(A2:C100, {2,1}, {-1,1})Col 2 descending, then col 1 ascending
5. Sort by Three Columns
=SORT(A2:D100, {3,2,1}, {1,-1,1})Priority: col 3 asc, col 2 desc, col 1 asc
6. Nested Sorting (Readable)
=SORT(SORT(A2:C100, 1, 1), 2, -1)
First by col 1 asc, then col 2 desc
Combined with Other Functions
7. Filter Then Sort
=SORT(FILTER(A2:C100, B2:B100>500), 2, -1)
Show records > 500, sorted high to low
8. Sort Unique Values
=SORT(UNIQUE(A2:A100))
Unique list sorted alphabetically
9. Top 10 with TAKE
=TAKE(SORT(A2:C100, 2, -1), 10)
Top 10 by sales (Excel 365)
Advanced Applications
10. Sort with Table
=SORT(Table1[#All], 2, -1)
Sort entire Excel table by column 2
11. Dynamic Month Sorting
=SORT(FILTER(A2:C100, MONTH(C2:C100)=MONTH(TODAY())), 2, -1)
Current month's data sorted by sales
🎯 Pro Tips
- • Use named ranges: =SORT(SalesData, ...) more readable than cell references
- • Remember column position: sort_index = position in array, not Excel column
- • Multi-level: use arrays: {2,1} cleaner than nested SORT functions
- • Combine wisely: FILTER → SORT → UNIQUE for complex operations
- • Performance: SORT is fast - handles 100k+ rows easily
- • Check spill space: Ensure cells below are empty before creating SORT
- • Use SORTBY: When sorting by column not in result range
Frequently Asked Questions
What's the difference between SORT and SORTBY?
SORT sorts by columns within the data range. SORTBY sorts by separate columns/ranges. Example: SORT(A:C, 2) sorts A:C by column B. SORTBY(A:C, D:D, -1) sorts A:C by values in column D. Use SORTBY when the sort key isn't in the result range or when sorting by calculated values.
How do I sort by multiple columns with different orders?
Use array syntax: =SORT(A2:D100, {2,3,1}, {-1,1,1}). This sorts by col 2 descending, then col 3 ascending, then col 1 ascending. Arrays {2,3,1} and {-1,1,1} must be same length. Alternative: nest SORT functions: =SORT(SORT(SORT(data, 1, 1), 3, 1), 2, -1) but array method is cleaner.
Can I sort by a calculated column?
Not directly in SORT - use SORTBY instead. Example: Sort names by length: =SORTBY(A2:A100, LEN(A2:A100), -1). Sort by profit margin: =SORTBY(A2:C100, C2:C100/B2:B100, -1). SORTBY accepts any formula that returns same-sized array as sort key.
Why does my SORT return #SPILL! error?
#SPILL! means results blocked by data in output cells. Fix: (1) Clear cells below/right of formula, (2) Move formula to empty area, (3) Delete blocking content. SORT needs space to expand - can't overwrite existing data. Check if merged cells or objects blocking spill range.
How do I sort dates newest to oldest?
Use -1 for descending order: =SORT(A2:C100, 3, -1) where column 3 contains dates. This puts newest dates first. For oldest first, use 1 or omit: =SORT(A2:C100, 3, 1). Excel treats dates as numbers, so numeric sorting works perfectly. Ensure dates are actual date values, not text.
Can SORT handle blank cells?
Yes, blanks sort to bottom by default regardless of sort order. In ascending sort: numbers → text → blanks. In descending: text → numbers → blanks. Can't change blank position in SORT. Workaround: Use FILTER to exclude blanks first: =SORT(FILTER(A2:C100, A2:A100<>'')).