How to How to Use SORTBY with Multiple Sort Columns in Excel in Excel - Practical Guide

Learn how to use the SORTBY function to sort data by multiple columns simultaneously in Excel. This tutorial covers syntax, practical examples, and advanced sorting scenarios. SORTBY eliminates the need for manual sorting or complex helper columns, saving time and reducing errors when organizing complex datasets with multiple sort priorities.

Why This Matters

Multi-column sorting is essential for data analysis, financial reporting, and business intelligence. SORTBY streamlines this process dynamically without disrupting original data or requiring manual intervention.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with single-column sorting concepts
•Excel 365 or Excel 2021 with SORTBY function support

Step-by-Step Instructions

Identify Your Data Range

Select your data table including headers (e.g., A1:D100). Note the column positions you want to sort by; for example, sort by Region first, then by Sales descending.

Open a New Cell for the Formula

Click on an empty cell where you want the sorted results to appear, typically below or to the right of your original data to avoid overlap.

Enter the SORTBY Formula with Multiple Sort Keys

Type the formula: =SORTBY(A1:D100, C1:C100, 1, B1:B100, -1). Replace ranges with your data; first sort key (C) is ascending (1), second sort key (B) is descending (-1).

Add Sort Order Parameters

After each sort column reference, add 1 for ascending or -1 for descending order. For three sort criteria: =SORTBY(data, sort_col1, order1, sort_col2, order2, sort_col3, order3).

Press Enter to Apply

Press Enter to execute the formula. Excel instantly displays the sorted dataset in the selected location; results update automatically if source data changes.

Alternative Methods

Data > Sort Menu (Traditional Method)

Use the Data tab > Sort button to manually configure multi-column sorts with custom sort dialogs. Less dynamic but works in older Excel versions without SORTBY support.

SORT + Multiple Helper Columns

Create helper columns with ranking formulas, then sort by helpers. More cumbersome but useful for complex conditional sorting logic.

SORTBY with Single Column

Use SORTBY with one sort key only if complex multi-level sorting isn't required. Simpler formula but less powerful.

Tips & Tricks

✓Always include headers in your data range if they exist; SORTBY treats the first row as data unless you exclude it separately.
✓Use -1 for descending (Z-A, largest to smallest) and 1 for ascending (A-Z, smallest to largest) order parameters.
✓Place your SORTBY formula in a separate area to avoid overwriting original data and maintain a reference copy.
✓Test with 2-3 sort columns first before attempting complex multi-column sorts with many criteria.
✓Remember that SORTBY returns results in a new location; it doesn't modify the original dataset in place.

Pro Tips

★Combine SORTBY with FILTER to both sort and filter data dynamically: =SORTBY(FILTER(data, criteria), sort_col, order).
★Use SORTBY with dynamic ranges like A1:D1000 to automatically include new rows added below existing data.
★Chain multiple SORTBY functions for conditional sorting logic based on data values.
★Reference sort columns from different tables using SORTBY to consolidate and organize cross-source datasets.

Troubleshooting

#VALUE! error appears

Check that sort order parameters are 1 or -1 only. Verify all ranges have identical row counts and that column references are correct.

Data not sorting correctly

Confirm sort columns contain the correct data types (dates, numbers, text). Mixed data types may sort unexpectedly; clean data first.

Formula returns #REF! error

Ensure all referenced ranges exist and are properly formatted. Check that you haven't deleted source columns or ranges after creating the formula.

SORTBY function not recognized

Verify you have Excel 365 or Excel 2021+. SORTBY is unavailable in Excel 2019 or earlier; use Data > Sort instead.

Related Excel Formulas

SORT FILTER UNIQUE RANK RANK.AVG

Frequently Asked Questions

Can I sort by more than 3 columns with SORTBY?

Yes, SORTBY supports unlimited sort columns. Simply continue the pattern: =SORTBY(data, col1, order1, col2, order2, col3, order3, col4, order4, ...). Each additional sort column acts as a tiebreaker for the previous sorts.

Does SORTBY preserve the original data?

Absolutely. SORTBY returns sorted results in a new location without modifying your original dataset. Your source data remains untouched.

What's the difference between SORTBY and the Data > Sort menu?

SORTBY is a formula-based, dynamic approach that updates automatically when source data changes. Data > Sort is manual and static; changes to source data don't automatically re-sort. SORTBY is more efficient for frequently updated datasets.

Can I use SORTBY with headers?

Yes, but include headers in your main data range (first row). Exclude headers from sort column ranges if they contain text labels to avoid sorting them as data values.

Is SORTBY case-sensitive?

No, SORTBY sorts text alphabetically regardless of case. Both 'Apple' and 'apple' are treated identically in sort order.