Skip to content

HAVE YOU EVER SORTED WITH A FORMULA?

HAVE YOU EVER SORTED WITH A FORMULA?

THE SORT FUNCTION

 

Hello everyone,

No doubt that one of the most frequently used functions in Excel is the Sort function.
This feature has 2 constants: A-Z and Z-A i.e. Ascending and Descending Sort.
We sort our data by certain field in pretty much all tables.
Have you ever tried to get this sorting action done with a formula?

 

WHAT DOES IT DO

You can easily sort columns or rows by the sort order you want in a different field from your mail table with the SORT function that is one of the dynamic array functions that are recently released after Office 365 . 

 

SYNTAX

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

There are 4 arguments in the function.
The first one is required, the next 3 arguments are optional.

Now, let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.

Argument Description
array

Required

The range, or array to sort
[sort_index]

Optional

A number indicating the row or column to sort by
[sort_order]

Optional

A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order
[by_col]

Optional

A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column

 

USE OF THE FUNCTION

  • Choose the fields you want from your table and state by which column and order you will sort.
  • When you choose a field for the array argument only, the data is sort based on the Ascending order.
  • If multiple columns are selected in the array argument, the column by which the sorting will be done is chosen.
  • You can choose the Descending order in the sort_order argument if you want. If you don’ choose it, the sort is always Ascending.
  • In the by_col argument, you can sort by column i.e. from left to right. If not chosen, the sorting will always be from top to bottom.

 

Let’s have an example and see what happens when we choose the array argument only.

 

Now, let’s choose multiple columns and state the sort_index argument as well.

 

Since we didn’t use the sort_order argument in the formula below, prices are sorted from the lower to the higher. Now, let’s use the sort_order argument in the same function and see our data sorted from the higher to the lower.

 

Lastly, let’s sort the data from left to right and now from top to bottom.

We will be talking about the other New Dynamic Array functions in our next articles.
And then we will be able to get things done way easier by using these new functions together. LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!

See you in other articles, bye. 🙋🏻‍♂️
You can share this article and help a lot of people get informed as well. 👍🏻