How to sort by two columns with a formula in Excel
Excel formula
=SORT(A2:C9,{2,3},{1,-1})Give SORT arrays of columns and orders: =SORT(A2:C9,{2,3},{1,-1}) sorts by department A to Z, then by salary from highest within each department.
- Result on the example
- a 8-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
The second argument lists the columns to sort by, in order of priority: column 2 (department) first, then column 3 (salary) to break ties. The third argument gives an order for each: 1 for ascending, -1 for descending.
Google Sheets' SORT takes pairs of arguments instead, column then TRUE or FALSE, so the Sheets version is SORT(A2:C9,2,TRUE,3,FALSE).
=SORT(A2:C9,{2,3},{1,-1})Result{8 rows × 3 cols} "Mo Diaz", "Finance", 74000, "Ana Ruiz", …
A2:C98 rows × 3 cols: "Ana Ruiz", "Finance", $68,500, "Raj Patel", …A range: 8 rows × 3 columns.
{2,3}{1 row × 2 cols} 2, 3An array of values.
{1,-1}{1 row × 2 cols} 1, -1An array of values.
SORT(A2:C9,{2,3},{1,-1}){8 rows × 3 cols} "Mo Diaz", "Finance", 74000, "Ana Ruiz", …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
A2:C98 rows × 3 cols: "Ana Ruiz", "Finance", $68,500, "Raj Patel", … - sort_column
{2,3}{1 row × 2 cols} 2, 3 - is_ascending
{1,-1}{1 row × 2 cols} 1, -1
- range
In Google Sheets
Google Sheets needs a different formula: =SORT(A2:C9,2,TRUE,3,FALSE). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORTBY(A2:C9,B2:B9,1,C2:C9,-1)Excel only | spills 8 × 3 | SORTBY takes ranges instead of column numbers, so it keeps working if columns are inserted. Excel only. |
Common mistakes
- Each sort column needs its own order:
{2,3},-1sorts both columns descending. Give an array of orders,{1,-1}. - In Google Sheets, array constants like
{2,3}don't work in SORT. Use the column, order pairs.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sort by two columns with a formula
E2, sort the employees by department A to Z, and by salary from highest to lowest within each department.Questions
How do I sort by three columns?
Add a third column and order: =SORT(A2:D9,{2,3,1},{1,-1,1}).
How do I sort by a column I don't want to show?
Use SORTBY with that column as the key: =SORTBY(A2:A9,C2:C9,-1) returns just the names, highest salary first.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.