What is the difference between SORT and the Sort button?

The button reorders the original data once. SORT returns a sorted copy that updates when the data changes.

Can I sort by two columns?

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

Your version of Excel does not have it. It needs Microsoft 365 or Excel 2021.

exceltestpractice

On the clock 0:00Free practice test

Function guides · Lookup

SORT function

Returns a sorted copy of a range, spilling the results into neighboring cells.

Syntax

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

Argument	What it is
`array`	The range to sort.
`sort_index`	Optional. The column to sort by, counting from 1 inside the range. Defaults to 1.
`sort_order`	Optional. 1 for ascending (default), -1 for descending.
`by_col`	Optional. TRUE sorts by columns instead of rows. Almost always omitted.

What SORT does

SORT returns a sorted copy of a range. It does not touch the original data; it writes the sorted version into the cells below and beside the formula.

You choose which column to sort by, counting inside the range, and whether to go up or down. Ascending is the default.

Unlike Data > Sort, the formula stays live. Add a row to the source and the sorted list reorders itself.

It pairs naturally with FILTER and UNIQUE, and it needs Microsoft 365 or Excel 2021.

Sorting formulas shine in dashboards. A leaderboard that sits beside the raw data and re-sorts itself whenever a number changes is one SORT formula. Add TAKE around it for a top five, and the list stays five rows long however big the source grows. The cost is that the layout depends on the spill area staying clear, so keep other content out of the way.

SORT examples

1. Sort a list from high to low

Sort a list from high to low. The formula =SORT(A2:B5,2,-1) in cell D2 returns a block starting with Mei.
	A	B	C	D	E
1	Rep	Sales
2	Dana	410		Mei	520
3	Luis	295		Dana	410
4	Mei	520		Luis	295
5	Omar	180		Omar	180

The range has two columns. sort_index 2 sorts by sales, and -1 puts the largest first. The original data is untouched. The sorted copy spills from D2.

2. Alphabetical order

Alphabetical order. The formula =SORT(A2:A5) in cell C2 returns a block starting with Dana.
	A	B	C
1	Rep
2	Mei		Dana
3	Dana		Luis
4	Omar		Mei
5	Luis		Omar

With only the range given, SORT orders by the first column ascending. Names come out A to Z.

3. The top three

The top three. The formula =TAKE(SORT(A2:B5,2,-1),3) in cell D2 returns a block starting with Mei.
	A	B	C	D	E
1	Rep	Sales
2	Dana	410		Mei	520
3	Luis	295		Dana	410
4	Mei	520		Luis	295
5	Omar	180

TAKE, a newer Microsoft 365 function, keeps the first rows of any array. Wrapped around a descending SORT it returns the three highest sellers and leaves the rest of the sorted list out.

Common SORT mistakes

#SPILL! Cells in the way of the output block must be empty.
Sorting only one column of a table. SORT(B2:B5) sorts that column alone, so the names no longer match. Sort the whole table range and use sort_index.
Counting the sort column from the sheet. sort_index counts inside the range you passed, so the second column of A2:B5 is 2, whatever its sheet letter.
Using it in older Excel. SORT is in Microsoft 365 and Excel 2021. Excel 2019 shows #NAME?. Use Data > Sort there.

SORT on Excel job tests

If the task says to sort the data, use Data > Sort on the original range. If it says to produce a sorted list that updates, in a new area, use SORT. Those are different jobs.

For "sorted by sales, largest first", the sort index is the column of sales counted within the range, and the order is -1. SORT combines with FILTER and UNIQUE, so expect one formula wrapped around another.

Leave the spill area empty, and check that the sort column and direction match the wording.

SORT questions

What is the difference between SORT and the Sort button?: The button reorders the original data once. SORT returns a sorted copy that updates when the data changes.
How do I sort descending?: Use -1 as the third argument.
Can I sort by two columns?: Yes, with arrays: =SORT(A2:C9,{1,3},{1,-1}) sorts by column 1 ascending, then column 3 descending.
Why does SORT show: Your version of Excel does not have it. It needs Microsoft 365 or Excel 2021.

Practice SORT

The lesson SORT: ordering a list without touching the data (module 19 of Analyst) has checked SORT exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.

Try a 5-item Excel test sample