Excel spreadsheets often need to be sorted in a variety of ways, and in the past, we had to sort them manually

However, if the original data is updated, it cannot be updated and sorted in real time, and it has to be manually adjusted, which is very troublesome

For this need, the latest version of Excel has developed a new function formula, the SORT formula

Can be used for automatic sorting

1. Basic usage

When we are in the table, enter =SORT

It will consist of 4 parameters

Parameter 1: is the sorted data region

The second parameter is the index mark of sorting

The third parameter is the sorting method, 1 is ascending, -1 is descending, and it is 1 by default when not filled

In the fourth parameter, FALSE is vertical sorting, True is horizontal sorting, and the default is false when it is not filled

In fact, the 3rd and 4th parameters can be omitted

For example, when we enter the formula:

=SORT(A2:D7,4)

等同于=SORT(A2:D7,4,1,FALSE)

If the 3rd parameter is not filled, the default is 1 ascending order, and the 4th parameter is not filled, and the default is TRUE vertical sorting

So the above formula is to sort the data in column 4 in ascending order for the data region A2:D7

Therefore, the results obtained can be seen to be obtained in ascending order of wages

2. Multi-condition sorting

If we have a need, we need to rank in ascending order according to gender, and then in descending order according to salary

When two conditions need to be met for sorting, both the second and third parameters need to use array usage

Here, the formula that needs to be entered is:

=SORT(A2:D7,{3,4},{1,-1},FALSE)

The 4th parameter can also be omitted and the formula is used:

=SORT(A2:D7,{3,4},{1,-1})

It will be ranked first in ascending order by gender and then in descending order of wages

For example, here's how many brands sell in stores

We now need to sort the aggregated data in descending order

So here's the horizontal sorting

We can't use FALSE for the 4th parameter, we need to use TURE

We just need to enter the formula:

=SORT(B1:E5,5,-1,TRUE)

Sort the data in descending order by row 5 and we get the result we want

For example, here's the store's sales data

We need to withdraw the stores with a total amount greater than 85, and we need to sort the business in descending order

First of all, we can use the FILTER formula to quickly extract the store data with an amount greater than 85:

=FILTER(A:F,F:F>85)

Then we want to sort the 6th column in descending order, so let's nest the SORT formula directly:

=SORT(FILTER(A:F,F:F>85),6,-1)

You can quickly get a list

Have you learned this little trick? Try it out!