SORT Function in Excel and Google Sheets
Purpose
Return a sorted copy of a range or array that spills dynamically and updates when the source data changes
Return value
A dynamic array of all rows from the source range sorted by the specified column, spilling into adjacent empty cells
SORT is the dynamic array function that replaced the tedious copy-paste-sort workflow I used to follow every time I needed a sorted view of data. Rather than sorting the original table and disrupting its structure, SORT produces a separate live sorted output that updates automatically whenever the source data changes. Add a new row to your data table and the sorted output refreshes instantly. Change a value and the sort order adjusts on its own. I use SORT constantly in dashboards where I want to show a top-N leaderboard or an alphabetical list drawn from a larger unsorted dataset -- the source stays as-is and the sorted view is always current. SORT also composes cleanly with FILTER and UNIQUE: sort a filtered subset, sort a deduplicated list, or sort and filter in one formula. It is available in Excel 365, Excel 2021, and all versions of Google Sheets.
Syntax
✓ Excel 365 / 2021
✓ Google Sheets
= Same syntax
=SORT(array, [sort_index], [sort_order], [by_col])
Arguments
| Argument | Required | Description |
|---|---|---|
| array | Required | The range or array to sort. All columns in this range are returned in the output. |
| sort_index | Optional | The column number within array to sort by (1 for the first column, 2 for the second, etc). Defaults to 1. Pass an array like {1,2} to sort by multiple columns. |
| sort_order | Optional | 1 for ascending (A to Z, smallest to largest -- default), -1 for descending (Z to A, largest to smallest). Pass an array like {1,-1} for multi-column sorts. |
| by_col | Optional | FALSE (default) to sort rows, TRUE to sort columns. Sorting rows is the standard use case. |
How it works
SORT evaluates the array argument and returns a sorted copy. The output spills into adjacent empty cells automatically -- do not enter anything in those cells or a #SPILL! error appears. The source data is never modified. Sort by a single column by passing its position as sort_index. Sort descending by setting sort_order to -1. Sort by multiple columns by passing arrays to both sort_index and sort_order: =SORT(A2:C100,{2,3},{1,-1}) sorts first by column 2 ascending then by column 3 descending. Wrap FILTER around SORT or inside it to combine filtering and sorting in one formula -- the FILTER function returns an array that SORT handles directly. Wrap UNIQUE inside SORT to get a sorted deduplicated list in one step. For older Excel versions where SORT does not exist, use the Data > Sort ribbon command or helper columns with RANK and INDEX MATCH.
Examples
1
Sort a sales table by the Sales column in descending order to show the top performers first.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Alice | North | 4200 |
| 3 | Bob | South | 3100 |
| 4 | Carol | East | 5500 |
| 5 | Dan | North | 3800 |
| 6 | Ella | South | 4900 |
| 7 | Finn | North | 5100 |
| 8 | Gia | East | 2800 |
| 9 | Hugo | North | 4700 |
| 10 | Iris | South | 3600 |
| 11 | Jo | East | 4100 |
Row 2: Carol, East, 5500
— The highest sales figure is Carol at 5500. SORT returns all 10 rows ordered from highest to lowest: Carol 5500, Finn 5100, Ella 4900, Hugo 4700, Jo 4100, Alice 4200, Dan 3800, Iris 3600, Bob 3100, Gia 2800.
The formula sits in one cell and spills all 10 sorted rows into the cells below it automatically. The original table in A2:C11 is unchanged. Argument 3 means sort by the third column (Sales), -1 means descending.
2
Sort the same table alphabetically by Rep name (column 1) in ascending order.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Alice | North | 4200 |
| 3 | Bob | South | 3100 |
| 4 | Carol | East | 5500 |
| 5 | Dan | North | 3800 |
| 6 | Ella | South | 4900 |
| 7 | Finn | North | 5100 |
| 8 | Gia | East | 2800 |
| 9 | Hugo | North | 4700 |
| 10 | Iris | South | 3600 |
| 11 | Jo | East | 4100 |
Row 2: Alice, North, 4200
— Alphabetically Alice comes first, then Bob, Carol, Dan, Ella, Finn, Gia, Hugo, Iris, Jo. All three columns are returned in the sorted order.
Sort_index 1 targets the Rep column. Sort_order 1 means ascending (A to Z). Both arguments are optional defaults so =SORT(A2:C11) produces the same result -- but writing them explicitly is clearer.
3
Combine SORT with FILTER to show only North region rows sorted by Sales descending in a single formula.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Alice | North | 4200 |
| 3 | Bob | South | 3100 |
| 4 | Carol | East | 5500 |
| 5 | Dan | North | 3800 |
| 6 | Ella | South | 4900 |
| 7 | Finn | North | 5100 |
| 8 | Gia | East | 2800 |
| 9 | Hugo | North | 4700 |
| 10 | Iris | South | 3600 |
| 11 | Jo | East | 4100 |
Row 2: Finn, North, 5100
— FILTER first extracts the 4 North rows: Alice 4200, Dan 3800, Finn 5100, Hugo 4700. SORT then orders them by Sales descending: Finn 5100, Hugo 4700, Alice 4200, Dan 3800.
FILTER returns an array that SORT receives as its array argument -- no intermediate cells needed. This SORT + FILTER combination is the dynamic array equivalent of a filtered sorted table and updates live as data changes.
Common use cases
1. Sort a leaderboard by score descending so the top performers always appear first
=SORT(A2:C100,3,-1)
2. Return an alphabetically sorted list of unique values using SORT wrapped around UNIQUE
=SORT(UNIQUE(A2:A100))
3. Show a filtered sorted view of a table using SORT wrapped around FILTER
=SORT(FILTER(A2:C100,B2:B100="North"),3,-1)
4. Sort by two columns simultaneously -- first by region alphabetically then by sales descending
=SORT(A2:C100,{2,3},{1,-1})
5. Create a live top-5 display by wrapping SORT in TAKE to limit output rows
=TAKE(SORT(A2:C100,3,-1),5)
Common errors
#SPILL! error
The cells below or to the right of the SORT formula contain data. SORT cannot spill its results into occupied cells.
Fix: Clear the spill range. SORT needs a blank area equal to the number of rows in the sorted output across all columns returned.
#NAME? error on the SORT function
SORT is not available in Excel 2019 or earlier. The function name is unrecognised in those versions.
Fix: Use Excel 365 or Excel 2021. For older Excel, use Data > Sort from the ribbon or build a sorted output with helper columns using RANK and INDEX MATCH.
Sort order is wrong or mixed up
sort_index points to the wrong column number, or sort_order is 1 when you wanted -1. A common mistake is using column letters instead of numbers.
Fix: Count the column position within the array argument -- column A is 1 only if A is the leftmost column of the array. Sort_order 1 is ascending, -1 is descending.
Tips and variations
Combine with UNIQUE for a sorted deduplicated list in one formula
=SORT(UNIQUE(A2:A100)) returns all unique values from the range in alphabetical order. This replaces the two-step Remove Duplicates then Sort workflow with a single live formula.
=SORT(UNIQUE(A2:A100))
Sort by multiple columns using array arguments
Pass {col1,col2} to sort_index and {order1,order2} to sort_order to sort by a primary and secondary column simultaneously. The outer curly braces create an array constant directly inside the formula.
=SORT(A2:C100,{2,3},{1,-1})
Use TAKE to limit the sorted output to a top-N result
TAKE(array,n) returns the first n rows of an array. Wrapping SORT in TAKE gives you a live top-5 or top-10 leaderboard: =TAKE(SORT(A2:C100,3,-1),5) returns the 5 highest-sales rows.
=TAKE(SORT(A2:C100,3,-1),5)
Excel vs Google Sheets
Excel vs Google Sheets
SORT is available in Excel 365, Excel 2021, and all versions of Google Sheets. It is not available in Excel 2019 or earlier. The syntax and spill behaviour are identical in Excel 365 and Google Sheets. Formulas copy between the two without changes.
Frequently asked questions
SORT returns a sorted copy of a range or array as a dynamic spill result. It does not modify the original data -- it creates a separate sorted output that updates automatically whenever the source data changes. You can sort by any column, in ascending or descending order, and the result spills into adjacent empty cells automatically.
Set the sort_order argument to -1: =SORT(A2:C10,2,-1) sorts the range by column 2 in descending order. The default sort_order is 1 (ascending). You can also sort multiple columns by passing arrays to sort_index and sort_order: =SORT(A2:C10,{1,2},{1,-1}) sorts first by column 1 ascending then by column 2 descending.
SORT sorts a range by one of its own columns. SORTBY sorts a range by a separate array that is not part of the returned range -- useful when you want to sort a display range by a helper column that should not appear in the output. For most straightforward sorting tasks, SORT is simpler. Use SORTBY when the sort key is outside the data range.
No. SORT was introduced in Excel 365 and Excel 2021 as part of the dynamic array function family. It is not available in Excel 2019, 2016, or earlier. For older Excel versions, use the built-in Data > Sort feature or helper columns with RANK and INDEX MATCH to produce a sorted output. SORT is available in all modern versions of Google Sheets.
Yes -- this is one of the most useful dynamic array combinations. Wrap FILTER inside SORT: =SORT(FILTER(A2:C100,B2:B100="North"),3,-1) returns only the North region rows sorted by column 3 in descending order. The two functions compose cleanly because FILTER returns an array that SORT can sort directly.