How to Sort in Excel: SORT Function and Sort Dialog
Excel gives you two ways to sort. The Sort dialog rearranges your data in place. The SORT function (Excel 365/2021) leaves the source untouched and returns a sorted copy that updates automatically. This guide shows both, with exact results, plus the Google Sheets differences.
1. Sort in place with the Sort dialog
- Click any cell inside your data (do not select a single column).
- Go to Data > Sort.
- Tick My data has headers if row 1 contains column names.
- Under Sort by, pick the column and, under Order, pick A to Z, Z to A, smallest to largest, largest to smallest, or a custom list.
- Click Add Level to add a tie-breaker column (for example, sort by Region, then by Sales).
- Click OK.
| Option | What it does |
|---|---|
| Sort On | Values (default), Cell Color, Font Color, or Cell Icon |
| Add Level | Adds a second or third sort key |
| Options... | Case sensitive, and sort top to bottom or left to right |
Common pitfall: If you select only one column before sorting, Excel can offer to expand the selection. Always choose Expand the selection so each row stays together. Sorting in place is permanent apart from Undo, so keep a copy or a numbered ID column before big sorts.
2. The SORT function (Excel 365 and 2021)
=SORT(array, [sort_index], [sort_order], [by_col])
| Argument | Meaning |
|---|---|
| array | The range to sort |
| sort_index | Column number inside the array to sort by (default 1) |
| sort_order | 1 ascending (default), -1 descending |
| by_col | FALSE sorts rows (default), TRUE sorts columns |
2.1 Sort by price, lowest first
| A (Product) | B (Price) |
|---|---|
| Apple | 1.20 |
| Banana | 0.80 |
| Cherry | 2.50 |
| Date | 1.75 |
| Elderberry | 3.00 |
=SORT(A2:B6, 2, 1)
Result (spills into 5 rows and 2 columns):
| Banana | 0.80 |
|---|---|
| Apple | 1.20 |
| Date | 1.75 |
| Cherry | 2.50 |
| Elderberry | 3.00 |
2.2 Sort by price, highest first, keeping the header
Excel 365 has VSTACK to put the header above the sorted rows:
=VSTACK(A1:B1, SORT(A2:B6, 2, -1))
Result:
| Product | Price |
|---|---|
| Elderberry | 3.00 |
| Cherry | 2.50 |
| Date | 1.75 |
| Apple | 1.20 |
| Banana | 0.80 |
2.3 Sort by two columns
Data in A1:C6, headers in row 1:
| Region | Sales | Rep |
|---|---|---|
| West | 1200 | Lee |
| East | 950 | Kim |
| West | 850 | Patel |
| East | 1500 | Gomez |
| South | 700 | Chen |
=SORT(A2:C6, {1,2}, {1,-1})
This sorts by Region ascending, then by Sales descending.
Result:
| East | 1500 | Gomez |
|---|---|---|
| East | 950 | Kim |
| South | 700 | Chen |
| West | 1200 | Lee |
| West | 850 | Patel |
2.4 Sort only the rows that match a condition
Combine FILTER and SORT. For example, sort only the East rows by Sales, highest first:
=SORT(FILTER(A2:C6, A2:A6="East"), 2, -1)
Result:
| East | 1500 | Gomez |
|---|---|---|
| East | 950 | Kim |
3. Google Sheets: the syntax is different
Google Sheets also has SORT, but it does not use 1 and -1. It takes TRUE/FALSE for ascending, and each extra sort key is its own pair of arguments:
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
| Goal | Google Sheets formula |
|---|---|
| Price low to high | =SORT(A2:B6, 2, TRUE) |
| Price high to low, keep header | ={A1:B1; SORT(A2:B6, 2, FALSE)} |
| Region A to Z, then Sales high to low | =SORT(A2:C6, 1, TRUE, 2, FALSE) |
4. Common sorting mistakes
| Mistake | Why it happens | Fix |
|---|---|---|
| Header row ends up in the middle | My data has headers is not ticked | Tick it in the Sort dialog |
| Numbers sort like text (1, 10, 2) | Numbers are stored as text | Convert with Data > Text to Columns > Finish, or multiply by 1 |
| Only one column moved | Only one column was selected | Select a single cell inside the table, or choose Expand the selection |
#SPILL! from SORT |
Something is blocking the cells below the formula | Clear the cells in the spill range |
SORT shows #NAME? |
Your Excel version has no dynamic arrays (before 2021) | Use the Sort dialog, or upgrade to 365/2021 |
| Need to undo a sort later | Sorting in place cannot be reversed after saving | Add an ID column with typed-in values 1, 2, 3... before sorting, then sort by it to restore the order (a =ROW() formula will not work because it recalculates after the sort) |
5. FAQ
What is the difference between the Sort dialog and the SORT function?
The Sort dialog rearranges the original cells permanently. The SORT function writes a sorted copy somewhere else and recalculates whenever the source data changes, so the original order stays intact.
Does the SORT function work in older versions of Excel?
No. SORT needs Excel 365 or Excel 2021 (dynamic arrays). In Excel 2019 and earlier, use the Sort dialog.
How do I sort in descending order with SORT?
Set the third argument to -1, for example =SORT(A2:B6, 2, -1). In Google Sheets use FALSE instead: =SORT(A2:B6, 2, FALSE).
Can I sort by cell color?
Yes, in the Sort dialog: set Sort On to Cell Color or Font Color. The SORT function cannot sort by color, and neither can Google Sheets' SORT function (use a filter by color instead).
How do I sort a table without breaking the rows?
Click one cell inside the table, then use Data > Sort. Excel detects the full range and moves each row as a whole. Converting the range to a Table with Ctrl + T makes this more reliable.