Sorting data in Excel is one of the most common tasks for professionals dealing with spreadsheets. Whether you’re organizing lists, structuring databases, or arranging datasets for analysis, Excel’s SORT function provides a powerful way to reorder your data dynamically.
What is the SORT Function in Excel?
The SORT function is a dynamic array function introduced in Excel 365 and Excel 2019. Unlike traditional sorting methods that require manual input or filtering, SORT automatically updates when the source data changes.
Here’s the basic syntax:
=SORT(array, [sort_index], [sort_order], [by_col])- array – The range of data you want to sort.
- sort_index (optional) – The column (or row) number used for sorting.
- sort_order (optional) – 1 for ascending, -1 for descending. Default is ascending.
- by_col (optional) – FALSE (default) sorts by rows, TRUE sorts by columns.
Simple SORT Example
Let’s say I have a list of products in column A along with their prices in column B. I want to sort the list by price in ascending order.
| Product | Price |
|---|---|
| Tablet | $300 |
| Laptop | $800 |
| Phone | $500 |
The SORT formula to arrange these by price (ascending) would be:
=SORT(A2:B4, 2, 1)The output would be:
| Product | Price |
|---|---|
| Tablet | $300 |
| Phone | $500 |
| Laptop | $800 |
Sorting in Descending Order
If I want to sort the prices from highest to lowest, I change the sort_order parameter to -1:
=SORT(A2:B4, 2, -1)Sorting by Multiple Columns
What if I want to sort first by category (column A) and then by price (column B)? I use SORT with an array for sort_index:
=SORT(A2:C10, {1,2}, {1,1})Here’s what happens:
- First, sorts data by the first column (Category) in ascending order.
- Then, sorts by the second column (Price) also in ascending order.
Using SORT with Other Functions
The real power of SORT emerges when combined with functions like FILTER or UNIQUE. For example, sorting unique product names:
=SORT(UNIQUE(A2:A10))This extracts unique product names and sorts them alphabetically.
Dynamic Sorting vs. Manual Sorting
Why use SORT instead of Excel’s built-in sorting tools? Here are some advantages:
- Sorting updates automatically when the source data changes.
- No need to manually go through sorting steps.
- Works exceptionally well in dashboards and reports.
Final Thoughts
The SORT function is an essential tool for managing and organizing data dynamically in Excel. Whether you’re ranking sales figures, restructuring datasets, or creating automated reports, SORT provides clarity and efficiency without manual intervention. Mastering its usage will save time and enhance spreadsheet automation.
Other interesting article:
How UNIQUE works in Excel? Best UNIQUE examples