# SORT function in Excel

> SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.

Source: https://spreadsheetpractice.com/functions/sort · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.
- **Syntax:** `=SORT(array, [sort_index], [sort_order], [by_col])`
- **Most common use:** `=SORT(A2:A6)` returns Desk lamp (Product names, A to Z)
- **Available in:** Excel 2021, 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** A sorted copy of array, the same size as the original. It spills.
- **Google Sheets:** Google Sheets has SORT, but its arguments differ: you give a column number and TRUE or FALSE for ascending.

## What it does

SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse. The original data stays where it is, and the sorted copy reorders itself whenever the numbers change, which makes it the backbone of leaderboards and ranked reports.

## Syntax

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

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range to sort. Leave out the header row. |
| `sort_index` | Optional | Which column to sort by, counting from the left edge of array. The default is 1. |
| `sort_order` | Optional | 1 sorts ascending (the default). -1 sorts descending. |
| `by_col` | Optional | TRUE sorts columns left to right instead of rows. The default is FALSE. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=SORT(A2:A6)` | Desk lamp (spills 5 × 1) | Product names, A to Z |
| G2 | `=SORT(A2:C6,3,-1)` | Monitor arm (spills 5 × 3) | The whole table by revenue, column 3, highest first |
| K2 | `=INDEX(SORT(A2:C6,2,-1),1,1)` | USB hub | Sort by units, then take the first name |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Units | Revenue |  | A to Z |  | By revenue, highest first |  |  |  | Most units sold |
| 2 | Desk lamp | 48 | $1,872 |  | Desk lamp |  | Monitor arm | 22 | $1,958 |  | USB hub |
| 3 | Monitor arm | 22 | $1,958 |  | Laptop stand |  | Laptop stand | 35 | $1,890 |  |  |
| 4 | Laptop stand | 35 | $1,890 |  | Monitor arm |  | Desk lamp | 48 | $1,872 |  |  |
| 5 | USB hub | 61 | $1,769 |  | USB hub |  | USB hub | 61 | $1,769 |  |  |
| 6 | Webcam | 17 | $1,173 |  | Webcam |  | Webcam | 17 | $1,173 |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #SPILL! | Something is in the cells the result needs. Clear everything below and to the right of the formula, or move it to an empty area. |
| The header row ends up in the middle | array included row 1. Start it on the first data row, such as A2:C6. |
| #VALUE! | sort_index is bigger than the number of columns in array, such as 4 for a three-column range. Count from the left edge of array, not from column A. |
| #NAME? | SORT needs Excel 2021, Excel 2024 or Microsoft 365. In older versions, sort a copy with Data > Sort. |

## Tips

- To sort by two columns, use SORTBY, which takes several sort keys.
- Combine with TAKE for a live top-3: =TAKE(SORT(A2:C6,3,-1),3).

## Related functions

- [SORTBY](https://spreadsheetpractice.com/functions/sortby): SORTBY sorts one range by the values in another.
- [UNIQUE](https://spreadsheetpractice.com/functions/unique): UNIQUE returns each distinct value in a range once, in the order it first appears.
- [FILTER](https://spreadsheetpractice.com/functions/filter): FILTER returns only the rows of a range that pass a test, such as every order from the West region.
- [TAKE](https://spreadsheetpractice.com/functions/take): TAKE keeps the first or last rows (and columns) of a range and drops the rest.

## Questions

### How do I sort largest to smallest with a formula?

Set sort_order to -1: =SORT(A2:C6,3,-1) sorts by the third column from highest to lowest.

### Does SORT change my original data?

No. It returns a sorted copy and the source stays as it is. That also means you can't type over the results; edit the source instead.

### How do I sort by more than one column?

Use SORTBY: =SORTBY(A2:C6,B2:B6,1,C2:C6,-1) sorts by column B, then breaks ties by column C from highest to lowest.

## Practice

A graded SORT exercise ("Sorted rep list") is on [the SORT page](https://spreadsheetpractice.com/functions/sort). It is checked instantly, then re-checked on changed data.

Lessons that use SORT:

- [Lesson 26: UNIQUE & SORT](https://spreadsheetpractice.com/learn/unique-sort) (Pro)
- [Lesson 30: VSTACK & HSTACK](https://spreadsheetpractice.com/learn/vstack-hstack) (Pro)
- [Lesson 44: Capstone: sales report](https://spreadsheetpractice.com/learn/capstone-sales-report) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
