What's the difference between SORT and SORTBY functions in Excel?

SORT rearranges the entire data range based on specified columns, while SORTBY sorts a range based on the values in another range. SORTBY is more flexible for complex sorting scenarios where you want to sort by columns not included in the output.

Do SORT and SORTBY functions work in all Excel versions?

No, these are dynamic array functions available only in Excel 365 and Excel 2021 . They don't work in Excel 2019 or earlier versions. For older Excel versions, you'll need to use traditional sorting methods or array formulas.

Can I sort by multiple columns using SORT function?

Yes! The SORT function allows multi-column sorting by specifying multiple columns in the sort_index parameter. For example: =SORT(A2:C100, {1,3}, {1,-1}) sorts first by column 1 ascending, then by column 3 descending.

How do I create custom sort orders with SORTBY?

SORTBY excels at custom sorting. You can create a custom order using MATCH function: =SORTBY(data_range, MATCH(sort_column, custom_order_range, 0)) . This lets you sort by days of week, priority levels, or any custom sequence not following alphabetical order.

Excel SORT SORTBY Functions: Complete Guide 2024

Mastering Excel SORT and SORTBY functions revolutionizes how you organize and analyze data. These dynamic array functions automatically sort your data and update in real-time, eliminating the need for manual sorting or complex formulas. This comprehensive guide covers everything from basic syntax to advanced techniques.

Excel SORT and SORTBY Functions: Complete Guide 2024

SORT Function Basics
SORTBY Function Explained
Multi-Column Sorting
Custom Sort Orders
Dynamic Data Sorting
Advanced Techniques
Common Errors & Solutions
SORT & SORTBY FAQ

1. SORT Function Basics

The SORT function rearranges the contents of a range or array in ascending or descending order. It's a dynamic array function that automatically spills results to adjacent cells.

Unsorted Data

Original Range

→

=SORT(A2:C10)

SORT Function

→

Sorted Data

Dynamic Result

SORT Syntax

The complete syntax for the SORT function with all optional parameters.

                                    // Complete SORT function syntax

                                    =SORT(array, [sort_index], [sort_order], [by_col])

                                    // Simple ascending sort

                                    =SORT(A2:C100)

                                    // Sort by column 2 descending

                                    =SORT(A2:C100, 2, -1)

💡 If sort_index is omitted, SORT uses the first column. If sort_order is omitted, it defaults to ascending (1).

Basic Examples

Common use cases for the SORT function in everyday Excel tasks.

                                    // Sort by first column ascending

                                    =SORT(A2:D50)

                                    // Sort by third column descending

                                    =SORT(A2:D50, 3, -1)

                                    // Sort rows instead of columns

                                    =SORT(A2:Z2, , , TRUE)

💡 Use -1 for descending order, 1 for ascending order. Empty parameters use defaults.

Dynamic Updates

SORT results update automatically when source data changes - no manual re-sorting needed.

                                    // Dynamic sales leaderboard

                                    =SORT(SalesData, 4, -1)

                                    // Always current task list by due date

                                    =SORT(Tasks, 3, 1)

                                    // Real-time inventory by stock level

                                    =SORT(Inventory, 5, 1)

💡 Combine with FILTER for powerful dynamic reports that update automatically.

💡 Pro Tip: Always reference entire columns (A:A) instead of specific ranges (A2:A100) when you want your SORT function to automatically include new data as it's added.

2. SORTBY Function Explained

The SORTBY function sorts the contents of a range based on the values in a different range or array. This provides greater flexibility than SORT for complex sorting scenarios.

SORTBY Syntax

SORTBY allows sorting by multiple columns with different sort orders in a single function.

                                    // Complete SORTBY function syntax

                                    =SORTBY(array, by_array1, [sort_order1], ...)

                                    // Sort data by department column

                                    =SORTBY(A2:E100, C2:C100)

                                    // Sort by salary descending

                                    =SORTBY(A2:E100, E2:E100, -1)

💡 You can add multiple by_array and sort_order pairs for complex multi-level sorting.

Key Advantages

SORTBY offers unique capabilities that make it superior for certain sorting tasks.

                                    // Sort by calculated column

                                    =SORTBY(A2:D100, B2:B100*C2:C100, -1)

                                    // Sort by external range

                                    =SORTBY(Products, DeptSortOrder, 1)

                                    // Sort by function result

                                    =SORTBY(A2:D100, LEN(B2:B100), -1)

💡 SORTBY can sort by columns that aren't included in the output array - perfect for sensitive data.

Real-World Applications

Practical examples where SORTBY outperforms traditional sorting methods.

                                    // Sort products by custom category order

                                    =SORTBY(Products, MATCH(Categories, CustomOrder, 0))

                                    // Sort by multiple criteria with different orders

                                    =SORTBY(Data, Region, 1, Sales, -1, Date, 1)

                                    // Sort by weighted score

                                    =SORTBY(Candidates, Score1*0.6+Score2*0.4, -1)

💡 Combine with UNIQUE and FILTER for powerful data analysis dashboards.

3. Multi-Column Sorting

Both SORT and SORTBY support multi-column sorting, allowing you to sort data by multiple criteria with different sort orders.

Function	Multi-Column Syntax	Use Case	Example
SORT	=SORT(array, {col1,col2}, {order1,order2})	Sort by multiple columns in the array	=SORT(A2:D100, {1,3}, {1,-1})
SORTBY	=SORTBY(array, by1, order1, by2, order2)	Sort by external ranges or calculations	=SORTBY(A2:D100, C2:C100, 1, B2:B100, -1)

SORT Multi-Column

Use array constants to specify multiple columns and sort orders in SORT function.

                                    // Sort by department (col 2) then salary (col 5)

                                    =SORT(A2:E100, {2,5}, {1,-1})

                                    // Sort by region, then salesperson, then date

                                    =SORT(SalesData, {1,2,4}, {1,1,-1})

                                    // Three-level sort with mixed orders

                                    =SORT(Inventory, {3,1,4}, {-1,1,-1})

💡 Use curly braces { } to create array constants for multiple sort criteria.

SORTBY Multi-Column

SORTBY's syntax is more intuitive for multi-column sorting with separate parameters.

                                    // Sort by region then sales descending

                                    =SORTBY(Data, RegionRange, 1, SalesRange, -1)

                                    // Three-level sort with SORTBY

                                    =SORTBY(A2:F100, C2:C100, 1, E2:E100, -1, B2:B100, 1)

                                    // Sort by calculated columns

                                    =SORTBY(Results, Test1, -1, Test2, -1, AVG_Range, -1)

💡 SORTBY can reference ranges outside the sorted array, providing more flexibility.

4. Custom Sort Orders

Create custom sort orders that don't follow alphabetical or numerical sequence using SORTBY with helper functions.

Using MATCH for Custom Orders

The MATCH function is perfect for implementing custom sort sequences.

                                    // Custom day-of-week sorting

                                    =SORTBY(Schedule, MATCH(DayColumn, {"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}, 0))

                                    // Priority-based sorting

                                    =SORTBY(Tasks, MATCH(PriorityColumn, {"High","Medium","Low"}, 0))

                                    // Custom department order

                                    =SORTBY(Employees, MATCH(DeptColumn, CustomOrderRange, 0))

💡 Store custom sort orders in a separate range for easy maintenance and updates.

Complex Custom Sorting

Combine multiple techniques for sophisticated sorting scenarios.

                                    // Sort by quarter then custom status

                                    =SORTBY(Projects, QuarterColumn, 1, 
    MATCH(StatusColumn, {"Planning","Active","Review","Completed"}, 0))


                                    // Sort by multiple custom sequences

                                    =SORTBY(Data, 
    MATCH(CategoryColumn, CatOrder, 0), 1, 
    MATCH(StatusColumn, StatusOrder, 0), 1, 
    ValueColumn, -1)
                                

💡 Use SWITCH or IFS within MATCH for even more complex conditional sorting logic.

5. Dynamic Data Sorting

Combine SORT and SORTBY with other dynamic array functions for powerful, self-updating reports and dashboards.

SORT + FILTER Combinations

Filter data first, then sort the results for targeted dynamic reports.

                                    // Top 10 salespeople in West region

                                    =SORT(FILTER(SalesData, Region="West"), 4, -1)

                                    // High-priority tasks due this week

                                    =SORT(FILTER(Tasks, (Priority="High")*(DueDate<=TODAY()+7)), 3, 1)

                                    // Products low in stock, sorted by urgency

                                    =SORTBY(FILTER(Inventory, Stock)

💡 These combinations create live reports that update instantly when source data changes.

SORT + UNIQUE Combinations

Extract unique values and sort them for clean, organized lists.

                                    // Sorted list of unique departments

                                    =SORT(UNIQUE(Departments))

                                    // Unique products sorted by category

                                    =SORTBY(UNIQUE(Products), ProductCategories, 1)

                                    // Top 5 unique values by frequency

                                    =SORT(UNIQUE(A2:A100), COUNTIF(A2:A100, UNIQUE(A2:A100)), -1)

💡 Perfect for creating dynamic dropdown lists or summary reports.

Conclusion: Mastering Excel Sorting

Mastering Excel SORT and SORTBY functions transforms how you work with data by providing dynamic, automatic sorting that updates in real-time. These powerful functions enable you to:

Create self-updating reports and dashboards
Implement complex multi-column sorting with ease
Build custom sort orders for specialized business needs
Combine with other dynamic array functions for powerful data analysis
Eliminate manual sorting and reduce errors
Improve productivity with automated data organization

Next Steps: Start with basic SORT function examples and practice until comfortable. Then explore SORTBY for more complex scenarios. Finally, combine these functions with FILTER, UNIQUE, and other dynamic array functions to build sophisticated, self-updating reports.

💬 Practice Challenge: Convert one of your existing manually-sorted reports to use SORT or SORTBY functions. Time how long it takes to update both versions when source data changes. Most users see 5-10x speed improvements with dynamic sorting!

Kelasexcel.net