How do I sort by two columns?

Pass arrays: =SORT(A2:C7,{1,3},{1,-1}) sorts by column 1 ascending, then column 3 descending.

What is the difference between SORT and SORTBY?

SORT sorts by a column of the array; SORTBY sorts by any separate range.

How do I get the top 3?

Use TAKE(SORT(...),3) in Microsoft 365, or INDEX with a sequence.

← All functions

Dynamic arrays

SORT

Sorts an array.

Syntax

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

Example: =SORT(A2:C100,3,-1)

Available in: Excel 2021+, Microsoft 365

SORT returns a sorted copy of a range and spills it into the cells below: =SORT(array, [sort_index], [sort_order], [by_col]). Because the original data is untouched, the sorted list updates whenever the source changes.

When to use it

You want a ranked list, such as top sales, that stays current as data changes.
You need a sorted dropdown source, for example combined with UNIQUE.
You want to sort a table by one column while keeping rows together.
You want to leave the source data in its original order.

Arguments explained

array. The range or array to sort. The whole block is reordered, so related columns stay together.
sort_index. Optional. The number of the column (or row) to sort by within the array, starting at 1. The default is 1.
sort_order. Optional. 1 sorts ascending (default) and -1 sorts descending.
by_col. Optional. FALSE or omitted sorts rows. TRUE sorts columns left to right instead.

Worked examples

Largest to smallest

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =SORT(C2:C7,1,-1)

Result: Spills down: 1500, 1200, 970, 850, 610, 430

sort_order -1 means descending.

Sort a whole table by Amount

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =SORT(A2:C7,3,-1)

Result: Spills down: West, East, North, West, East, East

The table spills with rows kept together; the first column is shown, ordered by Amount (column 3) descending.

Sorted distinct regions

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =SORT(UNIQUE(A2:A7))

Result: Spills down: East, North, West

UNIQUE removes duplicates, then SORT orders the list A to Z.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =SORT(B2:D7,3,-1)

Example spreadsheet for SORT
	A	B	C	D	E	F	G	H
1	Region	Product	Units	Price		Result
2	East	Pen	120	2.5		Lamp	30	25
3	West	Pen	80	2.5		Lamp	15	25
4	East	Book	45	12		Book	45	12
5	North	Book	60	12		Book	60	12
6	West	Lamp	30	25		Pen	120	2.5
7	East	Lamp	15	25		Pen	80	2.5

Result in F2: Lamp

Practice this in a lesson: UNIQUE and SORT

Common errors and how to fix them

#SPILL!. The output range is blocked. Clear the cells where the result should spill.
Wrong column sorted. sort_index is relative to the array you pass, not the sheet. In A2:C7, column 3 is Amount.
#VALUE!. sort_index is bigger than the number of columns in the array.
#NAME?. SORT requires Excel 2021 or Microsoft 365.

Related functions

Frequently asked questions

How do I sort by two columns?: Pass arrays: =SORT(A2:C7,{1,3},{1,-1}) sorts by column 1 ascending, then column 3 descending.
What is the difference between SORT and SORTBY?: SORT sorts by a column of the array; SORTBY sorts by any separate range.
Does SORT change my data?: No. It creates a sorted copy.
How do I get the top 3?: Use TAKE(SORT(...),3) in Microsoft 365, or INDEX with a sequence.