Excel SORT function

How to use the Excel SORT function

Description

The Excel SORT function sorts the contents of a given range or array and returns a dynamic array result. The values can be sorted by one more multiple columns.

Syntax

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

Arguments

array – The range or array to sort

sort_index – [optional] A number representing the row or column to sort by

sort_order – [Optional] A number indicating the sort order (1 = ascending, -1 = descending)

by_col – [Optional] A logical value indicating whether to sort by row or column

Return value

A sorted range or array

How to use the SORT function in Excel

Use the Excel SORT function to sort a given range or array by one or more columns. The result is a dynamic array of values that will be spilled into the surrounding cells if directly entered into a cell (not wrapped by another function). Every time a value changes in the source range, the SORT function automatically updates and re-sorts the range.

In the above example, we sort the range B6:B13 by the second column (2) in a descending order (-1) using the following formula:

= SORT(B6:C13, 2, -1)  

If no optional arguments are provided, SORT will sort by the first column in an ascending order.

SORT by multiple columns

The SORT function also allows you to sort by multiple columns at once. To to that, you have to provide an array constant in curly brackets like this:

= SORT(B6:C13, {2,4,5}, {-1,1,1})  

In the above statement, the range B6:C13 is sorted by the column two in a descending order, then by column 4 in an ascending order, and finally by column 5 in an ascending order.

Sort by another range

If you need to sort values in a range based on another range, the SORTBY function is what you need. SORTBY allows you to separately define the array that will be sorted and the array to be sorted by. 

Formula examples

Related Tutorials

Related Functions

How to use the Excel UNIQUE function

Excel UNIQUE function

The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array. 

How to use the Excel SORTBY function

Excel SORTBY function

The Excel SORTBY function sorts the contents of a given range or array based on the values of another range or array. It returns a dynamic array result.

How to use the Excel FILTER function

Excel FILTER function

The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email