All language subtitles for 3. The SORT and SORTBY Functions

It's time now to talk about two new functions in Excel 2021, and that is sought and sought by.

So now, instead of using ribbons and commands in order to perform a sorts, we actually have some very

specific functions which are going to help us salt and salt and salt by our brand new functions in the

latest version of Excel, and they're part of a list of new dynamic array functions.

Now, both of these functions are classed as look up and reference functions.

So if you're looking for them in the functions library, you'll find them in the look up and reference

group all the way to the bottom here.

Sort and sort by.

So let's have a quick demonstration of both of these so you can see how they work and how they might

be useful to you.

So let's start out with the SALT function.

Now, the data that we're going to sought is, again, some very basic data.

We have some students, we have the block that they reside in, an exam that they've taken and their

pass mark, and we're going to use the salt function to sort these results.

Now, previously, when we've been doing salts, we've been right clicking and selecting different commands

to use here.

And all of these commands really rely on you already having your data in the table in order to sort

it.

Now with this salt function and also the salt by function, we can basically output this data into a

different range of cells.

But the data that we output is going to be sorted as we want it to be organized.

So what I've done here is I've copied across the column headings into another range of cells, and we're

going to use the SALT function to sort this data.

So let's click in block and type in equals salt.

Now we have four arguments here and notice that three of those are optional arguments.

The only one that's mandatory is the array.

Now, the array is basically the cell range that contains the data that you want salt.

So for this sort, my array is going to be all of this information just here.

Now, notice that I'm not including the column headings there.

I don't want those included in my salt now because that is the only mandatory argument.

I could stop for my formula there and close the bracket.

And if I hit enter, it's going to salt my results effectively by that first column.

The default is to salt my results by the first column A to Z.

So this list is now sorted by the block in a to z sort order that that might be absolutely fine.

And if that's what you want to do, then you can just leave it just there with one argument.

However, what if I want to sort by one of the other columns, so maybe I want to sort by them mark

in descending order?

So I have the highest mark at the top going all the way down to the lowest.

So let's undo.

This is where we can use some of these other arguments.

So I'm going to select my array again.

That's this range just here comma sort index.

So this is where I tell Excel what column I want to sort by a much like v look up, which numbers columns

from left to right.

This works in the same way.

So my sort index is going to be a column number.

So if I want to sort by the mark column that is column number four comma, the sort order is where I

can specify if I want to sort in ascending order or descending order.

Well, I want to saw in descending order because I want the highest pass mark at the top going down

to the lowest.

So my argument here is minus one.

And then the final argument on the end, which again, you don't have to put in is if I want to sort

by the column, all sort by the row, and I will say the majority of the time you're going to sort by

the row.

So we want a false argument.

On the end of that, let's close our bracket and hit enter and see what we get.

So now I have my results sorted in descending order by the mark.

So, very simple and straightforward.

And because these are dynamic arrays, it means that if any of this data changes, then my sort is also

going to update.

So if I make a change to, let's say, the block just here, maybe I want to have East Wing in here

and hit enter.

Notice how it's also changed in the sorted results.

Now I'm going to undo that.

Now what if I added another entry on the bottom here?

Is my sort going to update?

Well, let's add East Wing at the bottom and Press tab.

Notice that it doesn't update.

Now I could get around this problem by putting this data in a table.

First of all, because remember, with tables, they auto expand to accommodate any new information.

So let's do this again.

But let's put our data into a table.

First of all, it's a control T to create a table.

Yes, my table has headers.

Click on OK.

You could go in and change that formatting.

I'm not going to bother right now.

Let's do a sort of, let's say, equals sorts.

I want to sort by this range just here and notice now because I have this data in a table.

It's giving me the table name, which is simply Table two, because I haven't renamed it my sort index.

Well, this time, let's sort on the student name A to Z.

So the student name column is column number two.

I'm going to soar A to Z.

So I'm going to say ascending order, and I don't really need the last argument on the end.

So let's just close the bracket and enter.

So now I have this list sorted by student A2Z, and because this is in a table, if I add another entry

on the bottom.

So let's say East Wing as soon as I press tab.

Take a look at that.

It automatically adds it to the bottom of this list.

Let's just add the rest of these details all like so.

So think about incorporating tables if you want everything to dynamically update.

Now, when I'm making my table array selection just here, I don't necessarily always have to select

the entire dataset.

For example, if I just wanted to extract the block and the student name when I do my sort over here,

when it comes to the array argument, I don't have to select the entire table.

I could select just the columns that I want to sort.

So block and student, let's go all the way down to the bottom.

Again, notice what I have in my formula.

It's giving me my formula name and then both of the column names I want to sort by the student name.

So that is column number two.

And this time let's sort in descending order minus one.

Close the bracket, enter.

And there we have our information sorted.

And just another point on that, if I wanted to sort by these three columns, let's just copy and paste

those over there.

When I do the sort this time my array, I only need the student, the exam and the mark so I can select

this array just here.

Now, when it comes to the next argument where we select the column, the column isn't going to be column

number two, it's column number one of the selected array.

So I sort index in this case, if you want to solve by the student is going to be column number one.

And then we can do our sort order, let's say, ascending and hit.

And so a few different examples there of how to use that sort of function.

Now one more thing here is you cannot perform a sort on non-contiguous columns, so you can't sort data

that isn't next to each other.

So, for example, I couldn't have two column headings over here for, let's say, student and also

mark, because the student column and the mark column have an exam column in between.

They're not adjacent, they're not next to each other.

So now we know how salt works.

Let's take a look at sort by because it's a very similar thing.

Now, this function will allow us to specify which column we want to sort by.

So kind of similar to soar.

In many ways, we have the same data.

So let's see what sort by can do for us.

Now, the first argument here is the array.

So again, I'm going to select all of the data that I want to sort out.

Our next argument is by array.

One, so I can go in and choose the column that I want to sort by.

So maybe I want to sort by the mark.

So we're going to select this column and then we need to provide the sort order.

So I want to go from 100 down to the lowest mark.

So we're going to say in descending order minus one comma.

My next argument is by array two.

So this method allows me to sort by multiple columns because my by array two argument.

Well, let me go in and select another column, a second column to sort by.

So this time I'm going to sort by the block.

So let's select that column, and I'm going to do that in ascending order, which is a one argument

on the end.

And I could carry on going, let's do another column.

I can choose another column to sort by.

So let's do the student name.

And I'm going to sort A2Z, which is a one on the end there.

Now I'm going to leave it there.

Let's close the bracket and hit enter.

And there is my sorted list.

I'm sorting by Mark, then by block and then by student.