SORT function
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.
- Available in
- Excel 2021, 2024 and Microsoft 365
- Category
- Dynamic array functions
- 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.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =SORT(A2:A6) | Desk lampspills 5 × 1 | Product names, A to Z |
| G2 | =SORT(A2:C6,3,-1) | Monitor armspills 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Product | Units | Revenue | A to Z | By revenue, highest first | Most units sold | |||||
| Desk lamp | 48 | $1,872 | Desk lamp | Monitor arm | 22 | $1,958 | USB hub | |||
| Monitor arm | 22 | $1,958 | Laptop stand | Laptop stand | 35 | $1,890 | ||||
| Laptop stand | 35 | $1,890 | Monitor arm | Desk lamp | 48 | $1,872 | ||||
| USB hub | 61 | $1,769 | USB hub | USB hub | 61 | $1,769 | ||||
| Webcam | 17 | $1,173 | Webcam | Webcam | 17 | $1,173 |
Practice SORT
From lesson 19, UNIQUE & SORT. Graded instantly, then re-checked on changed data.
Sorted rep list
F2, list each rep once, sorted A to Z.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).
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.
Lessons that use SORT
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.