SORT Function Examples – Excel & Google Sheets

This tutorial demonstrates how to use the SORT Function in Excel to sort a list of values.

The SORT Function is used to sort values in a range or array. It can be used to arrange values in ascending or descending order.

Sort in Ascending or Descending Order

We can order an array of values in either ascending or descending order. To do so we manipulate the [sort_order] argument in the SORT Function.

To sort our list of employees in A2:A7 in ascending order we enter the following formula in D2.

=SORT(A2:A7)

You’ll notice that in the example above  we’ve omitted the [sort_order] argument, this is because it isn’t necessary to set its value when you want to order your range in ascending order.

In the example below we’ve set the value of the [sort_order] argument to -1 so as to order our array in descending order. We enter the following formula in D2:

=SORT(A2:A7, ,-1)

You’ll notice that the third argument [sort_order] in our formula is set to -1. When set to 1 it returns the list in ascending order.

Sort by Another Column

You can also use the SORT Function to sort a column in your array by the values in another column in the same array. For example, we’d like to sort our list of employees in A2:A7 by the departments they work in in B2:B7.  To do so, we enter the following formula in E2:

=SORT(A2:B7,2)

You’ll notice that the second argument [sort_index] is set to 2. This means we are using the second column to sort the order of the function’s output.

This example can also be solved using the SORTBY Function which is closely related to the SORT Function in terms of use case and functionality.

Sort by Multiple Columns

In the previous section we saw how to sort values in one column by values in another column.  You can go a step further and sort the result by another column.

Imagine a scenario whereby we want to sort our list of employees in A2:A7  by the department they belong to in B2:B7 after which we want to order the names alphabetically within each department. To do so we enter the following formula in E2:

=SORT(A2:B7,{2,1},{1,1})

We use constant arrays {2,1} and {1,1} to achieve this.

[sort_index] = {2,1}

The snippet above means we first sort the data by the second column i.e. the department column and then by the first column i.e. the employee column

[sort_order] = {1,1}

The snippet above means we sort both the second and first column in ascending order.

Use with other Dynamic Array Formulas

The SORT Function can be used along with other Dynamic Array Formulas such as UNIQUE or FILTER for added functionality.

SORT & UNIQUE

For example, we’d like to sort the list of unique employees in our company, to do so we enter the following formula in D2:

=SORT(UNIQUE(A2:A7))

SORT & FILTER

For example, we’d like to return a list of fruits whose sale-quantity has hit or exceeded our target in D2,  to do so we enter the following formula in F2:

=SORT(FILTER(A2:B17,B2:B17>=D2),2,-1)

Issues

#SPILL!

This error occurs when there is a value in the Spill Range i.e. the range where the SORT Function places its results.

To correct this error, clear the range that Excel highlights.

 

SORT Function Tips and Tricks

The [sort_order] can only either be 1(ascending) or -1(descending), when no value is given for this argument the SORT Function defaults to sorting in ascending order.

Ensure that the cells below the input cell are blank to avoid the Spill Error, learn more about the Spill Error ‘here’ — add link to “Introduction To Dynamic Array Formulas”

The SORT Function can be used with other Dynamic Array Functions such as FILTER  and UNIQUE to create more versatile formulas.

SORT in Google Sheets

The SORT Function works exactly the same in Google Sheets as in Excel: