Skip to content

Excel function · Lookup & reference

Excel SORTBY function

SORTBY sorts a range by one or more key ranges you pass next to it; the key does not have to appear in the result. Use it for reports sorted by region first and then by amount.

In Turkish Excel: SIRALAÖLÇÜT

What it does

SORT needs a column number; SORTBY takes the key range directly: =SORTBY(A2:A6,C2:C6,-1) sorts the names by the amounts in column C, largest first, and the amounts do not appear in the result. The key range must have as many rows as the range being sorted.

Key and order pairs are added one after another. Rows that tie on the first key are sorted by the second: region name ascending, then amount descending within each region, in one formula. A key can also be a calculation, such as sales minus target.

It is available in Microsoft 365 and Excel 2021; the result spills and re-sorts whenever the source changes. Put FILTER inside it to sort only matching rows, or TAKE outside it to keep the first few.

Syntax

English Excel=SORTBY(array, by_array1, [sort_order1], ...)
Turkish Excel=SIRALAÖLÇÜT(dizi; ölçüt_dizisi1; [sıralama_düzeni1]; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
arrayRequiredThe range to sort and return, e.g. A2:C7.
by_array1RequiredThe first range or calculation to sort by; it must have as many rows as array.
sort_order1Optional1 or omitted: ascending. -1: descending. Further keys follow as by_array2, sort_order2.

Examples

1Sort by a hidden column or a calculation

E2 sorts only the names, by the sales in column C, largest first. In F2 the key is a calculation, sales minus target; whoever beat their target by the most comes first.

E2fx=SORTBY(A2:A6,C2:C6,-1)
Sort by a hidden column or a calculation: example table
ABCDEF
1RepTargetSalesBy SalesBy Margin over Target
2Emma50,00048,500LiamOlivia
3Liam55,00061,200AvaLiam
4Olivia45,00052,300OliviaEmma
5Noah40,00029,750EmmaAva
6Ava60,00057,800NoahNoah
  • E2 (the result spills into E2:E6)=SORTBY(A2:A6,C2:C6,-1)
  • F2 (the result spills into F2:F6)=SORTBY(A2:A6,C2:C6-B2:B6,-1)

Liam has the highest sales, but Olivia beat the target by the widest margin. Both lists end with Noah.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Sort by region first, then by amount

The first key is the region (B, ascending), the second is the amount (C, descending). Reps in the same region are ordered among themselves from the largest amount down.

E2fx=SORTBY(A2:C7,B2:B7,1,C2:C7,-1)
Sort by region first, then by amount: example table
ABCDEFG
1RepRegionAmountRepRegionAmount
2EmmaSouth48,500MasonNorth33,100
3LiamWest61,200NoahNorth29,750
4OliviaSouth52,300OliviaSouth52,300
5NoahNorth29,750EmmaSouth48,500
6AvaWest57,800LiamWest61,200
7MasonNorth33,100AvaWest57,800
  • E2 (the result spills into E2:G7)=SORTBY(A2:C7,B2:B7,1,C2:C7,-1)

The regions come in alphabetical order: North, South, West. North starts with Mason (33,100) and South starts with Olivia.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#VALUE!

Cause: The key range is not the same size as array, e.g. A2:A6 and C2:C7.

Fix: Start and end the ranges on the same rows.

#SPILL!

Cause: A cell in the spill area is not empty.

Fix: Clear the cells below and to the right of the formula.

#NAME?

Cause: The file was opened in a version without SORTBY (Excel 2019 or earlier).

Fix: In those versions, use a multi-level sort from Data > Sort.

Good to know

  • A key can be a calculation: =SORTBY(A2:A6,C2:C6-B2:B6,-1) puts the people furthest above target at the top.
  • Even if only the name column appears in the result, the sort still uses the amounts; handy for keeping a report short.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Sort the sales list by region first, then by amount from largest to smallest within each region

Convert to table

Bring your list into the tool and type this. Nexell builds the two-level sort and shows it in the preview first; once you approve, you download the file as .xlsx.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I sort by two columns with a formula in Excel?

Give SORTBY key and order pairs one after another: =SORTBY(A2:C7,B2:B7,1,C2:C7,-1) sorts by B ascending, and rows with the same B by C descending.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free