Spreadsheet Practice

How to sort data with a formula in Excel

Excel formula

=SORT(A2:B7,2,-1)

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

Result on the example
a 6-row list that spills from D2 in D2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

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.

=SORT(A2:B7,2,-1)

Result{6 rows × 2 cols} "Eli", 66400, "Ben", 61750, …

  1. A2:B76 rows × 2 cols: "Ana", $48,200, "Ben", $61,750, …

    A range: 6 rows × 2 columns.

  2. -1-1

    Makes the value negative.

  3. SORT(A2:B7,2,-1){6 rows × 2 cols} "Eli", 66400, "Ben", 61750, …

    SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.

    • rangeA2:B76 rows × 2 cols: "Ana", $48,200, "Ben", $61,750, …
    • sort_column2
    • is_ascending-1-1

In Google Sheets

Google Sheets needs a different formula: =SORT(A2:B7,2,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.

FormulaResultWhen to use it
=SORTBY(A2:B7,B2:B7,-1)Excel onlyspills 6 × 2SORTBY 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.

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 data with a formula

In D2, return the reps and sales from A2:B7 sorted by sales, highest first.

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).

Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.