Spreadsheet Practice

How to sort columns left to right with a formula in Excel

Excel formula

=SORT(B1:F2,2,-1,TRUE)

Set SORT's fourth argument, by_col, to TRUE: =SORT(B1:F2,2,-1,TRUE) sorts the columns by the values in row 2, largest first.

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

See it work

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

How it works

SORT normally reorders rows. With by_col set to TRUE it reorders columns instead, and the sort index 2 now means the second row of the range. Each region stays with its sales figure.

Google Sheets' SORT only sorts rows, so the Sheets version turns the range on its side with TRANSPOSE, sorts it, and turns it back.

=SORT(B1:F2,2,-1,TRUE)

Result{2 rows × 5 cols} "South", "East", "Central", "North", …

  1. B1:F22 rows × 5 cols: "North", "South", "East", "West", …

    A range: 2 rows × 5 columns.

  2. -1-1

    Makes the value negative.

  3. SORT(B1:F2,2,-1,TRUE){2 rows × 5 cols} "South", "East", "Central", "North", …

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

    • rangeB1:F22 rows × 5 cols: "North", "South", "East", "West", …
    • sort_column2
    • is_ascending-1-1
    • sort_column2, is_ascending2, ...TRUE

In Google Sheets

Google Sheets needs a different formula: =TRANSPOSE(SORT(TRANSPOSE(B1:F2),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
=TRANSPOSE(SORT(TRANSPOSE(B1:F2),2,-1))spills 2 × 5The transpose trick works in Excel too, and is how you'd write it in Google Sheets.

Common mistakes

  • Without TRUE as the fourth argument, SORT treats the two rows as two records and sorts them as rows.
  • Leave the label column (A) out of the range, or "Region" and "Q3 sales" get sorted along with the data.

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 columns left to right with a formula

In B4, sort the regions in B1:F2 left to right by their Q3 sales in row 2, largest first.

Questions

How do I sort a row alphabetically?

Sort by the first row instead: =SORT(B1:F2,1,1,TRUE).

Can I sort columns without a formula?

Yes: Data > Sort > Options > Sort left to right. That rearranges the data itself, once.

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.