Excel 2021 or laterExamples need Excel 2024 or laterDynamic arrays

SORT

SORTEREN in Dutch Excel

Returns a sorted copy of a range, by any one of its columns, ascending or descending, as a spilled block that re-sorts itself when the data changes.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDE
1RelationNameRelation (sorted)Name
2C2017Van Dijk GroothandelC2003=SORT(A2:B7)=SORT(A2:B7)Hoekstra Transport
3C2003Hoekstra TransportC2009Bakker Elektro
4C2042Jansen KantoorbeheerC2011De Boer Metaal
5C2009Bakker ElektroC2017Van Dijk Groothandel
6C2025Smit VerpakkingenC2025Smit Verpakkingen
7C2011De Boer MetaalC2042Jansen Kantoorbeheer
=SORT(A2:B7)
=SORT(A2:B7)

What SORT does and when to use it

What it does: it returns a sorted copy of a list that spills over the cells below, for example the supplier list in order of relation code. The original stays as it is.

When to use it: for ranked lists, alphabetical overviews and top-n tables that must stay in order as data comes in. It replaces copying a table and running Data, Sort on the copy. For a second key, use SORTBY.

Two main gains:

  • Always current: change a number in the source and the copy sorts itself again; a copy made with Data, Sort stays frozen.
  • Original untouched: the source keeps its order, so the layout other people rely on does not change.

How Excel evaluates it

Excel reads array and takes the column at position sort_index (the first if you leave it out). It compares the values in that column: numbers by size, text alphabetically without regard to case, with numbers ranked before text in ascending order.

It reorders whole rows, so every column of a row stays together, and rows with equal keys keep their original order. With sort_order -1 the comparison is reversed. The sorted block spills from the formula cell with the same size as array.

Watch out: Notes typed beside a spilled SORT list do not move with it: when people annotate the rows, sort the source with Data, Sort instead.

Mental model

A photocopy of the table, cut into strips row by row and laid out again in order; the original page never leaves the folder.

Terms used on this page: spill, array, argument, cell

Syntax

Official SORT documentation
SORT(array, [sort_index], [sort_order], [by_col])
SORT(array; [sort_index]; [sort_order]; [by_col])
ArgumentTypeMeaningDefault
arrayRequiredThe range or array to sort; the source itself stays as it is.
sort_indexOptionalWhich column of array to sort on, counted from 1; default is the first.1
sort_orderOptional1 sorts ascending (default), -1 sorts descending.1
by_colOptionalTRUE reorders columns, for tables laid out across; default sorts rows.FALSE

Return value:The same rows as array, in sorted order

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 18 KB)

Beginner

Put the supplier list in order of relation code

The client's supplier master list comes out of the system in the order the suppliers were created. You want it by relation code, so you can agree it line by line to the payables aging list. The creation order must stay as it is in your workpaper, because it shows what the client delivered.

ABCDEF
1RelationNameRelation (sorted)Name
2C2017Van Dijk GroothandelC2003=SORT(A2:B7)=SORT(A2:B7)Hoekstra Transport
3C2003Hoekstra TransportC2009Bakker Elektro
4C2042Jansen KantoorbeheerC2011De Boer Metaal
5C2009Bakker ElektroC2017Van Dijk Groothandel
6C2025Smit VerpakkingenC2025Smit Verpakkingen
7C2011De Boer MetaalC2042Jansen Kantoorbeheer
8
Formula cell (D2)InputSpilled result

Formula in D2

=SORT(A2:B7)
=SORT(A2:B7)

Result: C2003 | Hoekstra Transport / C2009 | Bakker Elektro / C2011 | De Boer Metaal / C2017 | Van Dijk Groothandel / C2025 | Smit Verpakkingen / C2042 | Jansen KantoorbeheerC2003 | Hoekstra Transport / C2009 | Bakker Elektro / C2011 | De Boer Metaal / C2017 | Van Dijk Groothandel / C2025 | Smit Verpakkingen / C2042 | Jansen Kantoorbeheer

Step by step
  1. Excel reads A2:B7, six rows of two columns. sort_index is left out, so the key is the first column, the relation codes. sort_order is left out, so the order is ascending.
  2. It compares the codes as text, character by character. All start with C20, so the last two digits decide: 03, 09, 11, 17, 25, 42. The codes in the source order were 17, 03, 42, 09, 25, 11.
  3. It moves whole rows: C2003 takes Hoekstra Transport with it, C2042 takes Jansen Kantoorbeheer.
  4. The copy spills from D2 to E7. A2:B7 is untouched: C2017 is still first there, as in the client's export.
  5. Change the code in A3 from C2003 to C2050 and that supplier drops to the bottom of D2:E7 on the next recalculation; the source order does not change.

Watch out: Codes sort as text, so lengths matter: C2100 would come before C299, because 1 is smaller than 9 at the third character. Keep codes the same length, or sort numeric keys.

More on this example: why it works, 1 variation

Why it works

With only the first argument, SORT uses its defaults: first column, ascending, sort rows. It returns a copy, which is why the client's order survives in A2:B7 while your working version is in code order.

Variations

=SORT(A2:B7, 2)
=SORT(A2:B7; 2)

Sort on the second column instead: by supplier name, Bakker Elektro first and Van Dijk Groothandel last.

Intermediate

List the largest manual entries first

In journal entry testing you have filtered the manual postings in the MEM journal to six entries. You want them largest first, to pick the ones for testing. Two entries have exactly the same amount, and you want to know which one the list shows first.

ABCDEFGH
1Entry no.AccountAmountLargest first
225-041245004500€18,500.00€ 18.500,0025-0472=SORT(A2:C7, 3, -1)=SORT(A2:C7; 3; -1)11001100€72,300.00€ 72.300,00
325-043140004000€4,250.00€ 4.250,0025-041245004500€18,500.00€ 18.500,00
425-045580008000€18,500.00€ 18.500,0025-045580008000€18,500.00€ 18.500,00
525-046018001800€960.00€ 960,0025-048945004500€9,800.00€ 9.800,00
625-047211001100€72,300.00€ 72.300,0025-043140004000€4,250.00€ 4.250,00
725-048945004500€9,800.00€ 9.800,0025-046018001800€960.00€ 960,00
8
Formula cell (E2)InputSpilled result

Formula in E2

=SORT(A2:C7, 3, -1)
=SORT(A2:C7; 3; -1)

Result: 25-0472 | 1100 | €72,300.00 / 25-0412 | 4500 | €18,500.00 / 25-0455 | 8000 | €18,500.00 / 25-0489 | 4500 | €9,800.00 / 25-0431 | 4000 | €4,250.00 / 25-0460 | 1800 | €960.0025-0472 | 1100 | € 72.300,00 / 25-0412 | 4500 | € 18.500,00 / 25-0455 | 8000 | € 18.500,00 / 25-0489 | 4500 | € 9.800,00 / 25-0431 | 4000 | € 4.250,00 / 25-0460 | 1800 | € 960,00

Step by step
  1. sort_index is 3, so the key is the third column of A2:C7, Amount: 18500, 4250, 18500, 960, 72300, 9800.
  2. sort_order is -1, descending, so the largest goes first: 72300, 18500, 18500, 9800, 4250, 960.
  3. Entries 25-0412 and 25-0455 both have 18500. Equal keys keep their source order, and 25-0412 (row 2) comes before 25-0455 (row 4), so 25-0412 is listed first.
  4. Whole rows move with their key, and the result spills from E2 to G7: 25-0472 on account 1100 with 72300 on top, 25-0460 with 960 at the bottom.
  5. Leave out the last two arguments and SORT(A2:C7) sorts on the first column, ascending: the entries in entry number order, which does not rank anything by size. The optional arguments carry the whole meaning here.

Watch out: Writing sort_order 0 instead of -1 gives #VALUE!; only 1 and -1 are accepted.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

sort_index picks the key by its position inside the array, and sort_order flips the direction, so one formula turns a list of entries into a ranking by amount. Because ties are kept in source order, the outcome is predictable: if you want a different tie-breaker, you have to supply it as a second key.

More to watch out for

  • If a seventh manual entry is added in row 8 and the formula still says A2:C7, it is silently left out of the ranking, and you could miss the largest entry. Use a Table so the range grows by itself.

Variations

=SORTBY(A2:C7, C2:C7, -1, A2:A7, -1)
=SORTBY(A2:C7; C2:C7; -1; A2:A7; -1)

A deliberate tie-breaker: largest amount first, and for equal amounts the higher entry number first, which puts 25-0455 above 25-0412.

AdvancedExcel 2024 or later

Top 3 overdue receivables in one aging band

From the receivables aging list you want, for the band you type in F1 (days overdue from), the three largest customer balances, largest first, as candidates for confirmation follow-up. The number of customers per band varies.

ABCDEFGH
1CustomerDays overdue fromBalanceBand (days)9090
2D10019090€18,450.00€ 18.450,00
3D10143030€22,100.00€ 22.100,00D1005=TAKE(SORT(FILTER(A2:C9, B2:B9=F1), 3, -1), 3)=TAKE(SORT(FILTER(A2:C9; B2:B9=F1); 3; -1); 3)9090€31,200.00€ 31.200,00
4D10089090€9,750.00€ 9.750,00D10129090€27,900.00€ 27.900,00
5D10226060€14,300.00€ 14.300,00D10019090€18,450.00€ 18.450,00
6D10059090€31,200.00€ 31.200,00
7D101700€6,800.00€ 6.800,00
8D10309090€12,600.00€ 12.600,00
9D10129090€27,900.00€ 27.900,00
10
Formula cell (E3)InputSpilled result

Formula in E3

=TAKE(SORT(FILTER(A2:C9, B2:B9=F1), 3, -1), 3)
=TAKE(SORT(FILTER(A2:C9; B2:B9=F1); 3; -1); 3)

Result: D1005 | 90 | €31,200.00 / D1012 | 90 | €27,900.00 / D1001 | 90 | €18,450.00D1005 | 90 | € 31.200,00 / D1012 | 90 | € 27.900,00 / D1001 | 90 | € 18.450,00

Step by step
  1. The innermost function first: FILTER tests B2:B9=F1, band 90, and keeps five rows: D1001 (18450), D1008 (9750), D1005 (31200), D1030 (12600), D1012 (27900).
  2. SORT receives that 5-row block and sorts on its third column, balance, descending: 31200, 27900, 18450, 12600, 9750.
  3. TAKE keeps the first 3 rows of the sorted block: D1005, D1012, D1001.
  4. The result spills from E3 to G5: D1005 90 31200, D1012 90 27900, D1001 90 18450.
  5. Type 60 in F1. FILTER finds only D1022 (14300), SORT leaves that one row as it is, and TAKE asks for 3 but returns the 1 row that exists, without an error.

Watch out: Forgetting -1 silently returns the smallest three, D1008, D1030 and D1001, and a list of three customers from the right band still looks like a sensible answer.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

The order of nesting is the order of the question: first narrow to the band, then rank, then cut. SORT has to sit between the other two, because taking 3 before sorting would give the first three customers in the list, not the largest three.

More to watch out for

  • TAKE(FILTER(...), 3) wrapped in SORT, the wrong way round, ranks only the first three customers found and returns D1005, D1001, D1008: D1012 with 27900 is missing.
  • A band that does not occur, such as 120, makes FILTER return #CALC!, and SORT and TAKE pass it on. Add if_empty only after thinking it through: SORT on column 3 of a single text gives #VALUE!.

Variations

=TAKE(SORTBY(FILTER(A2:A9, B2:B9=F1), FILTER(C2:C9, B2:B9=F1), -1), 3)
=TAKE(SORTBY(FILTER(A2:A9; B2:B9=F1); FILTER(C2:C9; B2:B9=F1); -1); 3)

Only the customer codes, ranked by a balance that is not shown: SORTBY takes the balance as a separate key.

Master

Reorder journal columns by their total

Your analysis of postings per journal has the journals across the top and the quarters down the side, with a Total row at the bottom. For the workpaper you want a second copy with the journal with the largest total on the left.

The layout must stay across, the key is a row, and a Q4 row will be inserted above Total after year end.

ABCDEF
1QuarterVRKINKBNKMEM
2Q1€412,500.00€ 412.500,00€298,300.00€ 298.300,00€505,200.00€ 505.200,00€38,400.00€ 38.400,00
3Q2€436,800.00€ 436.800,00€311,900.00€ 311.900,00€478,600.00€ 478.600,00€52,100.00€ 52.100,00
4Q3€398,200.00€ 398.200,00€305,400.00€ 305.400,00€521,900.00€ 521.900,00€27,800.00€ 27.800,00
5Total€1,247,500.00€ 1.247.500,00€915,600.00€ 915.600,00€1,505,700.00€ 1.505.700,00€118,300.00€ 118.300,00
6
7
8By totalBNK=LET( block, B1:E5, keyRow, ROWS(block), SORT(block, keyRow, -1, TRUE) )=LET( block; B1:E5; keyRow; ROWS(block); SORT(block; keyRow; -1; TRUE) )VRKINKMEM
9€505,200.00€ 505.200,00€412,500.00€ 412.500,00€298,300.00€ 298.300,00€38,400.00€ 38.400,00
10€478,600.00€ 478.600,00€436,800.00€ 436.800,00€311,900.00€ 311.900,00€52,100.00€ 52.100,00
11€521,900.00€ 521.900,00€398,200.00€ 398.200,00€305,400.00€ 305.400,00€27,800.00€ 27.800,00
12€1,505,700.00€ 1.505.700,00€1,247,500.00€ 1.247.500,00€915,600.00€ 915.600,00€118,300.00€ 118.300,00
13
Formula cell (B8)InputSpilled resultNote: Row 5 holds =SUM(B2:B4) and its neighbours in a real sheet; the totals are shown as values.

Formula in B8

=LET(
  block, B1:E5,
  keyRow, ROWS(block),
  SORT(block, keyRow, -1, TRUE)
)
=LET(
  block; B1:E5;
  keyRow; ROWS(block);
  SORT(block; keyRow; -1; TRUE)
)

Result: BNK | VRK | INK | MEM / €505,200.00 | €412,500.00 | €298,300.00 | €38,400.00 / €478,600.00 | €436,800.00 | €311,900.00 | €52,100.00 / €521,900.00 | €398,200.00 | €305,400.00 | €27,800.00 / €1,505,700.00 | €1,247,500.00 | €915,600.00 | €118,300.00BNK | VRK | INK | MEM / € 505.200,00 | € 412.500,00 | € 298.300,00 | € 38.400,00 / € 478.600,00 | € 436.800,00 | € 311.900,00 | € 52.100,00 / € 521.900,00 | € 398.200,00 | € 305.400,00 | € 27.800,00 / € 1.505.700,00 | € 1.247.500,00 | € 915.600,00 | € 118.300,00

Step by step
  1. LET names block as B1:E5: the journal codes in row 1, three quarters, and the totals in row 5. Column A stays out, because the quarter labels must not move.
  2. keyRow is ROWS(block): 5, the last row of the block, which is the Total row.
  3. by_col is TRUE, so SORT treats each column as one item and sort_index 5 means the fifth row of each column: VRK 1247500, INK 915600, BNK 1505700, MEM 118300.
  4. sort_order -1 orders them descending: 1505700, 1247500, 915600, 118300, so the columns come out as BNK, VRK, INK, MEM, each with its own code and quarter figures.
  5. The block spills from B8 to E12. The BNK column reads 505200, 478600, 521900 and 1505700, the same figures as D2:D5 in the source. Check: 505200 + 478600 + 521900 = 1505700.
  6. Insert a row above Total for Q4. The range grows to B1:E6, ROWS returns 6, and the key is still the Total row. A typed 5 would now sort on Q4 instead.

Watch out: The mirror-image mistake is leaving out TRUE. Here that gives #VALUE!, because the block is only 4 columns wide and there is no column 5.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

by_col TRUE turns SORT sideways: items are columns, and sort_index counts rows. Including the header row in block makes each journal code travel with its figures, and deriving keyRow from ROWS ties the key to the last row instead of to a number that goes stale when rows are inserted.

Do not use this construction when people annotate the output, for example a tickmark or workpaper reference typed under BNK: next year VRK may overtake BNK, the columns swap, and the note stays in place under the wrong journal.

In that case sort the source columns once with Data, Sort, Options, Sort left to right; the result is static and has to be redone with each update, but notes stay attached.

Watch out for

  • The mirror-image mistake is leaving out TRUE. Here that gives #VALUE!, because the block is only 4 columns wide and there is no column 5.

    With five or more journals there would be no error: SORT would quietly sort the quarter rows on the fifth journal's figures, the journal order would stay as it was, and the copy would look like a tidied table.

  • Starting block at A1 drags the quarter labels into the sort: column A has text in row 5 and ranks after the numbers, so in a descending sort it ends up on the left of the copy. Keep labels outside the block.

Variations

=SORTBY(B1:E5, B5:E5, -1)
=SORTBY(B1:E5; B5:E5; -1)

The same column order with SORTBY: a horizontal by_array sorts columns without needing by_col. Shorter, but the key row is a fixed range that you must widen by hand.

Common mistakes

SymptomCauseFix
Sorted on the wrong columnSORT(C2:F9, 4, -1) is meant to sort on column D, and it sorts on column F instead.sort_index counts inside array, starting at C. Column D is position 2: SORT(C2:F9, 2, -1).
Prices no longer match their productsYou sort the price column on its own, SORT(C2:C9), and the prices no longer line up with their products.Give SORT the whole table and pick the key with sort_index, so each row moves as a unit: SORT(A2:C9, 3).
Numbers stored as text land at the endA number stored as text, such as '45 from an import, ends up after all the real numbers in an ascending sort.SORT ranks numbers before text. Convert the column with VALUE or Text to Columns before sorting.
#SPILL! instead of the sorted listThe sorted list shows #SPILL!.Something occupies the cells the copy needs. It is as large as array, so leave that many rows and columns free.

Used in combinations