Return to List of Excel Functions

# SORT Function Examples – Excel & Google Sheets

Written by

Reviewed by

*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: