SORT returns a sorted copy of a range or array without changing the original data. You choose which column or row to sort by, ascending or descending, and whether to sort rows or columns.
=SORT(array, [sort_index], [sort_order], [by_col])What does SORT do?
SORT is a dynamic array function in the Lookup & reference category. You give it a range, tell it which column (or row) to use as the sort key, and it returns a new block of cells in the order you asked for. The source data stays exactly where it is, so you can keep a tidy master list and build sorted views beside it.
Before dynamic arrays, sorting meant either rearranging the original table or building helper columns. SORT removes that trade-off: the output updates automatically when the source values change, and one formula can feed a report, a dashboard, or a lookup chain. It also nests neatly inside functions such as TAKE, CHOOSECOLS, and FILTER.
A typical situation: you maintain a sales table by region and need a leaderboard that always shows the highest totals first. Instead of re-sorting the table every morning, you point SORT at the table, set the sort column and descending order, and the leaderboard refreshes itself. The same pattern works for alphabetical name lists, date-ordered schedules, and ranked score tables.
When to use SORT
- Building a live leaderboard from a sales or score table.
- Producing an alphabetical staff or student list without reordering the source.
- Showing the most recent records first by sorting on a date column.
- Sorting a small summary block by a total column for a report.
- Creating a sorted view that feeds VLOOKUP, XLOOKUP, or INDEX/MATCH.
- Ordering a filtered subset of rows before presenting it.
SORT syntax
=SORT(array, [sort_index], [sort_order], [by_col])| Argument | Required? | What to enter |
|---|---|---|
array | Required | The range or array to sort. Example: A2:C10 sorts the data rows of a three-column table. |
sort_index | Optional | Which column (or row) to sort by, counted from the first column of the array. Default is 1. Example: 2 sorts by the second column of the range. |
sort_order | Optional | 1 for ascending (default), -1 for descending. Example: -1 puts the largest numbers first. |
by_col | Optional | FALSE (default) sorts rows using a column as the key; TRUE sorts columns using a row as the key. Example: TRUE reorders columns left to right. |
What it returns: A dynamic array of the same size and shape as the input, with rows (or columns) reordered according to the sort key. The result spills into neighbouring cells and does not alter the source range.
SORT examples
1 Example 1: Sort a single column of numbers
The goal is to list the sales figures from smallest to largest. The formula =SORT(B2:B6) uses only the array argument, so Excel sorts by the first (and only) column in ascending order. It returns five values stacked vertically, starting with the smallest. The original cells in B2:B6 are untouched.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 850 | ||
| 3 | South | 850 | |||
| 4 | East | 1500 | |||
| 5 | West | 950 | |||
| 6 | Central | 1100 | |||
| 7 |
2 Example 2: Sort a two-column table by its second column
Here you want the region names paired with their sales, ordered by sales from highest to lowest. The formula =SORT(A2:B6,2,-1) sorts the whole two-column block, uses column 2 (Sales) as the key, and sets descending order. It returns five rows, each with the region and its figure. The top row is East with 1500.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | East | ||
| 3 | South | 850 | |||
| 4 | East | 1500 | |||
| 5 | West | 950 | |||
| 6 | Central | 1100 | |||
| 7 |
3 Example 4: Sort a staff table by hire date, newest first
You need the most recently hired employees at the top of a list. The formula =SORT(A2:C6,3,-1) sorts the three-column range using column 3 (Hire Date) as the key and descending order. Dates stored as real dates sort chronologically, so the latest date comes first. The top row is the employee hired on 2022-03-15.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Department | Hire Date | |||
| 2 | Ava Patel | Finance | 2019-05-10 | Daniel Kim | ||
| 3 | Ben Carter | IT | 2021-11-02 | |||
| 4 | Chloe Nguyen | HR | 2020-01-15 | |||
| 5 | Daniel Kim | Sales | 2022-03-15 | |||
| 6 | Ella Rossi | Marketing | 2018-09-30 | |||
| 7 |
4 Example 5: Sort a sales table by total, highest first, and keep all columns
A regional manager wants a ranked view of the full sales table, with the highest total at the top. The formula =SORT(A2:D6,4,-1) sorts all four columns using column 4 (Total) as the key in descending order. Every row keeps its region, rep, and units together. The first returned row is the North region with a total of 18000.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Rep | Units | Total | |||
| 2 | North | Ava | 120 | 18000 | East | ||
| 3 | South | Ben | 90 | 13500 | |||
| 4 | East | Chloe | 150 | 22500 | |||
| 5 | West | Daniel | 75 | 11250 | |||
| 6 | Central | Ella | 110 | 16500 | |||
| 7 |
Common SORT errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The sort_index is larger than the number of columns in the array, or sort_order is not 1 or -1. | Count the columns in the array and use a sort_index within that count; use 1 or -1 for sort_order. |
| #SPILL! | The cells where the result needs to spill are not empty. | Clear the cells below and to the right of the target cell, or move the formula to an empty area. |
| #NAME? | The function name is misspelled or the workbook uses an Excel version that does not support dynamic arrays. | Check the spelling as SORT; if your version lacks dynamic arrays, use the legacy Sort tool or upgrade Excel. |
| #CALC! | The array argument is empty or refers to a range with no values. | Point the array argument at a range that contains data. |
Tips for using SORT
- Leave the target cell and the spill area empty; SORT writes into as many cells as the result needs.
- Use
SORTinsideTAKEorCHOOSECOLSto return only the top rows or selected columns of a sorted list. - Combine
SORTwithFILTERto sort a subset without changing the source table. - Remember that sort_index counts columns from the left edge of the array you pass, not from column A of the sheet.
- Dates and numbers sort correctly only when they are stored as real values, not as text.
- Use
by_colset to TRUE when your data is laid out horizontally and you want to reorder columns.
Frequently asked questions
Does SORT change the original data?
No. SORT returns a new array in the cells where the formula spills. The source range is not modified.
How do I sort by more than one column?
SORT handles one key. For multi-level sorting, use SORTBY, which accepts multiple sort arrays and orders.
Why does my SORT formula show #SPILL!?
The result needs empty cells to expand into. Clear any values or formulas in the spill area, or move the formula.
Can SORT sort text alphabetically?
Yes. With sort_order 1, text sorts A to Z; with -1, it sorts Z to A.
Is SORT available in Excel 2019?
No. SORT is part of dynamic arrays and is available in Microsoft 365 and Excel 2021 and later.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SORT reference