Cellwise Try it free

Excel guide · 3 min read

How to sort data with a formula in Excel

Quick answerUse =SORT(A2:D9,4,-1) to return a copy of the data sorted by its 4th column, largest first. The result spills into the cells below and re-sorts by itself whenever the source changes, and your original data stays untouched. SORT is in Excel 365 and Excel 2021 and later.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1RepRegionDealsRevenue
2Maya ChenWest1448200
3Luis OrtegaEast961750
4Priya NairWest2139900
5Tom BeckerCentral1152300
6Aisha BelloEast1770400
7Sam WalshCentral628650
8Grace KimWest1255100
9Owen HartEast833800

SORT by one column

SORT takes the range, the column number to sort by (counted inside the range, not the sheet), and 1 for ascending or -1 for descending. Leave out the header row: you want the labels to stay put above the result.

Type the formula once in an empty cell with enough room below and to the right. Excel fills the rest. That block is a spill range: you cannot type inside it, and it grows or shrinks as rows are added to the source.

=SORT(A2:D9,4,-1)

Result: spills 8 rows × 4 columns

Aisha BelloEast1770400
Luis OrtegaEast961750
Grace KimWest1255100
Tom BeckerCentral1152300
Maya ChenWest1448200
Priya NairWest2139900
Owen HartEast833800
Sam WalshCentral628650

All 8 reps, 4 columns, from Aisha Bello (70,400) down to Sam Walsh (28,650).

Default sort: first column, A to Z

With only the range, SORT orders by the first column, ascending. Text sorts alphabetically and ignores case. Here that is by first name, because the names are stored first name first.

=SORT(A2:D9)

Result: spills 8 rows × 4 columns

Aisha BelloEast1770400
Grace KimWest1255100
Luis OrtegaEast961750
Maya ChenWest1448200
Owen HartEast833800
Priya NairWest2139900
Sam WalshCentral628650
Tom BeckerCentral1152300

The table in A to Z order of the Rep column, from Aisha Bello to Tom Becker.

Sort a filtered list

Wrap FILTER inside SORT to pick rows and order them in one step. This is the formula version of filtering a table and then sorting it, except it never goes stale.

The column number still refers to the columns of what FILTER returns, which here is the same four columns.

=SORT(FILTER(A2:D9,B2:B9="West"),4,-1)

Result: spills 3 rows × 4 columns

Grace KimWest1255100
Maya ChenWest1448200
Priya NairWest2139900

3 West reps by revenue: Grace Kim 55,100, Maya Chen 48,200, Priya Nair 39,900.

Sort by two columns, or by a column you do not show

For a second sort key, give SORT arrays of columns and orders: =SORT(A2:D9,{2,4},{1,-1}) sorts by Region A to Z, then by Revenue largest first within each region. That form is valid in Excel 365, but the Cellwise practice engine cannot run it, so try it in Excel.

SORTBY sorts one range by another range, so the sort column does not have to be in the output: =SORTBY(A2:A9,D2:D9,-1) lists just the names, ordered by revenue. SORTBY is also Excel 365 and 2021 and later.

A sorted list of unique values

SORT and UNIQUE together give a clean, alphabetical list of categories that updates as new ones appear. It is the usual source for a dropdown list.

=SORT(UNIQUE(B2:B9))

Result: spills 3 rows × 1 column

Central
East
West

3 regions in A to Z order: Central, East, West.

Older Excel, or a one-off sort

In Excel 2019 and earlier there is no SORT. For a ranked list of numbers, =LARGE(D2:D9,1), =LARGE(D2:D9,2) and so on return the values in order, and INDEX with MATCH fetches the matching name. If you only need to sort once, the ribbon is faster and changes the data in place.

  1. Click any cell in the data.
  2. Go to Data > Sort.
  3. Tick My data has headers, choose Revenue under Sort by, and Largest to Smallest under Order.
  4. Click Add Level for a second key, then OK.

Common mistakes

#SPILL! instead of a sorted list

Something is in the way: a value, even a space, in the cells the result needs. Clear the area below and to the right. SORT also cannot spill inside an Excel table, so put the formula outside it.

Including the header row

SORT(A1:D9) sorts the header in with the data, so "Revenue" lands among the names. Start the range on row 2 and type the headers above the formula.

Counting the sort column from column A

The index is the position inside the range. In SORT(B2:D9,3,-1) the 3 means column D, because B is column 1 of that range.

Numbers stored as text sort in the wrong place

Text sorts after numbers in ascending order, so "9" can land below 61,750. Convert the column to real numbers first.

FAQ

How do I sort largest to smallest with a formula?

Use -1 as the third argument: =SORT(A2:D9,4,-1). Use 1, or leave it out, for smallest to largest.

Does SORT change my original data?

No. SORT returns a sorted copy somewhere else on the sheet. The source keeps its order, and the copy updates whenever the source changes.

Why do I get #NAME? with SORT?

Your Excel version does not have the function. SORT needs Excel 365 or Excel 2021 or later. Use Data > Sort, or LARGE and SMALL with INDEX and MATCH, in older versions.

Can SORT sort horizontally?

Yes. The fourth argument, by_col, set to TRUE sorts columns instead of rows: =SORT(B1:E2,2,-1,TRUE) orders the columns by the values in row 2.

Functions used

Related guides

Updated