Dynamic arrays functions

SORT and SORTBY functions in Excel

SORT returns a range sorted by one of its columns; SORTBY sorts a range by the values of another range.

The Sort command rearranges your data where it sits, once. The SORT function leaves the data alone and shows a sorted copy somewhere else, which re-sorts itself whenever the data changes. It is available in Excel 2021 and Microsoft 365.

Syntax

=SORT(array, [sort_index], [sort_order], [by_col])

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

• =SORT(B2:D8)Copy
  Result:

  Ball Pen	Stationery	10
  Basmati Rice 5 kg	Grocery	650
  LED Bulb	Electronics	110
  Notebook	Stationery	60
  Power Bank	Electronics	1299
  Steel Bottle	Home	450
  Toor Dal 1 kg	Grocery	160

  Sorted by the first column of the range, the item name, A to Z.

• =SORT(B2:D8,3,-1)Copy
  Result:

  Power Bank	Electronics	1299
  Basmati Rice 5 kg	Grocery	650
  Steel Bottle	Home	450
  Toor Dal 1 kg	Grocery	160
  LED Bulb	Electronics	110
  Notebook	Stationery	60
  Ball Pen	Stationery	10

  Sorted by the 3rd column of the range, the price, largest first.

• =SORT(B2:D8,{2,3},{1,-1})Copy
  Result:

  Power Bank	Electronics	1299
  LED Bulb	Electronics	110
  Basmati Rice 5 kg	Grocery	650
  Toor Dal 1 kg	Grocery	160
  Steel Bottle	Home	450
  Notebook	Stationery	60
  Ball Pen	Stationery	10

  Two sort levels: by category A to Z, and inside each category by price, largest first.

• =SORTBY(B2:B8,E2:E8,-1)Copy
  Result:

  Notebook

  LED Bulb

  Toor Dal 1 kg

  Steel Bottle

  Basmati Rice 5 kg

  Power Bank

  Ball Pen

  SORTBY returns only the names, ordered by the Stock column, which is not shown.

• =SORTBY(B2:C8,C2:C8,1,D2:D8,-1)Copy
  Result:

  Power Bank	Electronics
  LED Bulb	Electronics
  Basmati Rice 5 kg	Grocery
  Toor Dal 1 kg	Grocery
  Steel Bottle	Home
  Notebook	Stationery
  Ball Pen	Stationery

  SORTBY with two levels: category, then price descending.

• =SORT(UNIQUE(C2:C8))Copy
  Result:

  Electronics

  Grocery

  Home

  Stationery

  The different categories in alphabetical order.

• =INDEX(SORT(B2:D8,3,-1),1,1)Copy
  Result: Power Bank

  The first row of the sorted result: the name of the dearest item.

• =SORT(FILTER(B2:E8,E2:E8>0),4)Copy
  Result:

  Power Bank	Electronics	1299	18
  Basmati Rice 5 kg	Grocery	650	40
  Steel Bottle	Home	450	55
  Toor Dal 1 kg	Grocery	160	75
  LED Bulb	Electronics	110	200
  Notebook	Stationery	60	300

  Items in stock only, sorted by stock, smallest first.

How SORT works

Give SORT a range. It returns the same rows in order of the first column, smallest to largest or A to Z. The result spills from the formula's cell into the cells below and to the right.

The second argument chooses the column to sort by, counted from the left of the range you selected. The third sets the direction: 1 for ascending, -1 for descending.

Several levels

To sort by one column and break ties with another, give lists in curly brackets: =SORT(B2:D8,{2,3},{1,-1}) sorts by column 2 ascending, then by column 3 descending. The third example does this.

SORTBY

SORTBY separates two things that SORT ties together: what to show and what to sort by.

• The sort column does not have to be in the result. The fourth example lists item names in order of stock without showing the stock.
• You refer to the sort column as a range, not as a number, so inserting a column does not break the formula.
• Several sort levels are written naturally, as pairs of range and direction.

Whenever the sort key is outside the displayed columns, or the formula needs to survive changes to the layout, SORTBY is the better choice.

A custom order

Alphabetical order is not always wanted. To sort by size as Small, Medium, Large, give SORTBY the position of each value in your own list: =SORTBY(A2:C20, MATCH(B2:B20, {"Small","Medium","Large"}, 0)).

Sorting in random order

=SORTBY(A2:A20, RANDARRAY(19)) shuffles a list of nineteen names, which is useful for drawing lots or mixing quiz questions. It reshuffles on every recalculation, so copy the result and paste it as values when you want to keep one order.

Combined with other functions

• A sorted list of distinct values: =SORT(UNIQUE(range)), the usual source for a drop-down list.
• Filter, then sort: =SORT(FILTER(...), column, order).
• Top five rows: in Microsoft 365, =TAKE(SORT(range, 3, -1), 5). In Excel 2021, =INDEX(SORT(range,3,-1), SEQUENCE(5), {1,2,3}).

Things to know

• Headings must be left out of the range, or they are sorted in with the data. Type them above the result.
• Text sorts without regard to capital letters. Numbers come before text, and empty cells come last.
• Numbers stored as text sort as text, so "100" comes before "20".
• The result keeps no formatting. Format the spill area yourself.
• #SPILL! means cells in the way. #NAME? means the Excel version is too old.

SORT or the Sort command?

Use the command on the Data tab to put the actual data in order. Use the function to keep the original entry order and show an ordered view for a report, a chart or a drop-down list.

In Google Sheets

Google Sheets has SORT with a slightly different shape: =SORT(range, sort_column, is_ascending, ...), where the direction is TRUE or FALSE and further pairs can follow. Sheets has no SORTBY, because its SORT can already sort by a range outside the data.

Related functions

• FILTERDynamic arrays
• UNIQUEDynamic arrays
• LARGE and SMALLStatistical
• RANK.EQ, RANK and RANK.AVGStatistical
• SEQUENCEDynamic arrays

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.