How to Use SORT in Excel
Returns a sorted copy of a range as a spilled array, leaving the original data untouched.
What is SORT in Microsoft Excel?
SORT produces a new, sorted version of your range without disturbing the source. Because it is a formula, it re-sorts automatically when the underlying data changes. That makes it ideal for live leaderboards, top-N lists and dashboards that must never go stale.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| array | Required | The range or array to sort. |
| sort_index | Optional | Which column or row to sort by, counted from 1. Defaults to 1. |
| sort_order | Optional | 1 for ascending (default), -1 for descending. |
| by_col | Optional | FALSE sorts rows (default), TRUE sorts columns. |
When to Use SORT (Real-World Business Scenarios)
- 1
Building a live top-10 list that re-ranks itself as values change.
- 2
Sorting a filtered result: =SORT(FILTER(...),2,-1).
- 3
Creating a sorted copy for a chart without touching the source table.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Salesperson | Region | Sales |
|---|---|---|
| Rahul | North | 125000 |
| Pooja | South | 98000 |
| Aman | West | 142000 |
How it works: Sorts the whole three-column range by column 3 in descending order, so the highest seller appears first.
Common Errors & How to Fix Them
Why it happens: There is not enough empty space below and to the right for the sorted output.
Fix: Clear the area under the formula, or wrap it in INDEX to return a single row.
Why it happens: sort_index is larger than the number of columns in the array.
Fix: Count the columns and pass a valid index, remembering that counting starts at 1.
Try SORT Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (SORT)
What does the SORT formula do in Excel?
SORT produces a new, sorted version of your range without disturbing the source. Because it is a formula, it re-sorts automatically when the underlying data changes. That makes it ideal for live leaderboards, top-N lists and dashboards that must never go stale.
What is the syntax for SORT?
What are common mistakes when using SORT?
#SPILL!: There is not enough empty space below and to the right for the sorted output. (Clear the area under the formula, or wrap it in INDEX to return a single row.) #VALUE!: sort_index is larger than the number of columns in the array. (Count the columns and pass a valid index, remembering that counting starts at 1.)
Related Excel Formulas
Keep building your formula toolkit with these dynamic arrays functions.
- LETAssigns names to calculation results within a formula, boosting calculation performance and readability.
- UNIQUEExtracts an automatically spilling list of unique, deduplicated values from any column or range.
- FILTERFilters a range of data dynamically based on criteria and spills the matching records automatically.
- SEQUENCEGenerates a series of numbers in one formula — build calendars, month numbers and test data instantly.
- VSTACKStacks several ranges on top of each other into one continuous list — the clean fix for monthly files.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
Master SORT in Advanced Excel
Learn SORT along with our formula library, practice files, and verifiable certificates in our complete curriculum.