How to Use SORT and SORTBY Functions in Excel + tip

Hello everyone! Today i will show you how to use SORT and SORTBY functions in Excel step by step. Both SORT and SORTBY are dynamic array formulas available in Excel 365 and Excel 2021+.

What are SORT and SORTBY Functions in Excel?

The SORT and SORTBY functions help you organize your table data automatically (A to Z, Z to A, smallest to largest, or largest to smallest). When your source data changes, your sorted result updates automatically without clicking the Sort button manually!

📚 Related Tutorial: Want to extract unique values before sorting? Check out How to Use UNIQUE Function in Excel!

---

1. SORT Function

Use SORT when you want to sort a table based on one of its own column numbers.

🛠️ Formula Syntax

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

• array: The data range you want to sort (example: A2:B5).
• [sort_index] (Optional): The column number to sort by (1 for 1st column, 2 for 2nd column).
• [sort_order] (Optional): 1 for Ascending (A-Z / 1-9), -1 for Descending (Z-A / 9-1).
• [by_col] (Optional): FALSE to sort rows (default), TRUE to sort columns.

---

📊 SORT Examples

Suppose you have this student score table in range A1:B5:

Name (Col 1)	Score (Col 2)
John	75
Dara	90
Lina	80
Sam	65

Example 1: Sort by Score Ascending (Lowest to Highest)

Sort using Column 2 (Score) with sort_order = 1:

=SORT(A2:B5, 2, 1)

Name	Score
Sam	65
John	75
Lina	80
Dara	90

---

Example 2: Sort by Score Descending (Highest to Lowest)

Sort using Column 2 (Score) with sort_order = -1:

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

Name	Score
Dara	90
Lina	80
John	75
Sam	65

---

Example 3: Sort by Name Alphabetically (A to Z)

Sort using Column 1 (Name):

=SORT(A2:B5, 1, 1)

---

2. SORTBY Function

SORTBY is more flexible than SORT. It lets you sort a range by specifying another range or column directly (instead of typing column numbers).

🛠️ Formula Syntax

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

• array: The data range you want to sort (example: A2:C5).
• by_array1: The column range to sort by (example: C2:C5).
• [sort_order1]: 1 for Ascending, -1 for Descending.
• You can add by_array2, sort_order2 for multi-level sorting!

---

📊 SORTBY Examples

Suppose you have this employee table in range A1:C5:

Row	Name	Department	Score
2	John	IT	75
3	Dara	HR	90
4	Lina	IT	80
5	Sam	Sales	65

Example 1: Sort Table by Score (Highest to Lowest)

=SORTBY(A2:C5, C2:C5, -1)

Name	Department	Score
Dara	HR	90
Lina	IT	80
John	IT	75
Sam	Sales	65

---

Example 2: Sort by Multiple Criteria (Department A-Z, then Score Highest to Lowest)

Sort 1st by Department (Column B, 1 = A-Z), then 2nd by Score (Column C, -1 = Highest to Lowest):

=SORTBY(A2:C5, B2:B5, 1, C2:C5, -1)

Result:

1. HR Department first (Dara: 90)
2. IT Department next (Lina: 80, John: 75)
3. Sales Department last (Sam: 65)

---

⚡ SORT vs SORTBY Comparison

Feature	SORT	SORTBY
Primary Method	Sorts by column index number (e.g. 2)	Sorts by cell range (e.g. C2:C5)
Multi-Level Sorting	Complex	Super Easy (by_array1, by_array2)
Sort by Range Outside Table	Not Supported	Supported
Best Choice For	Quick 1-column sorting	Advanced & multi-criteria sorting

---

💡 Powerful Formula Combinations

1. Unique + Sorted List (SORT + UNIQUE)

Extract unique items and sort them A to Z automatically:

=SORT(UNIQUE(A2:A100))

---

2. Filter + Sorted List (SORT + FILTER)

Filter IT department employees and sort results:

=SORT(FILTER(A2:C100, C2:C100="IT"))

---

💡 Quick Tips for SORT & SORTBY:

1. Avoid #SPILL! Error: Make sure cells below and to the right of your formula are empty!
2. Rule of Thumb: Use SORT when you need simple sorting by column index number (1, 2, 3). Use SORTBY when you want multi-criteria sorting or sorting by an external column range!

---

📚 Related Resources & Tutorials

• How to Use UNIQUE Function in Excel
• How to Use FILTER Function in Excel
• VLOOKUP vs XLOOKUP in Excel: When to Use Each
• Top 15 Basic Excel Formulas for Beginners ↗