# How to sort data with a formula in Excel

Use =SORT(range, sort_column, -1) for largest first, or 1 for smallest first. The sorted copy spills and updates automatically.

**Excel:** `=SORT(A2:B7,2,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=SORT(A2:B7,2,FALSE)`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Rep | Q3 sales |  | Sorted, highest first |
| Ana | 48200 |  |  |
| Ben | 61750 |  |  |
| Cara | 57300 |  |  |
| Dev | 39800 |  |  |
| Eli | 66400 |  |  |
| Fay | 44150 |  |  |

Formula in D2: `=SORT(A2:B7,2,-1)`

Result (spills 6 × 2): Eli, $66,400; Ben, $61,750; Cara, $57,300; Ana, $48,200; Fay, $44,150; Dev, $39,800

## How it works

SORT takes the range, the column to sort by (2 is the sales column within `A2:B7`) and the order: `-1` for descending in Excel. Google Sheets' SORT uses TRUE or FALSE for the order, so the Sheets version ends with FALSE. The original data stays as it is.

## Other ways

- `=SORTBY(A2:B7,B2:B7,-1)`: SORTBY sorts by a range rather than a column number, so it still works if you insert columns. Excel only.

## Common mistakes

- The sort column is counted inside the range, not on the sheet: column 2 of A2:B7 is B.
- Don't include the header row in the range, or it gets sorted with the data.
- In Google Sheets, -1 isn't a valid order; use FALSE.

## Questions

**How do I sort by two columns?**

Give SORT arrays of columns and orders: `=SORT(A2:C10,{2,3},{1,-1})` sorts by column 2, then column 3 descending.

**How do I sort alphabetically?**

Sort by the text column ascending: `=SORT(A2:B7,1,1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/sort-data-with-a-formula