Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,260 --> 00:00:14,050
It's time now to talk about two new functions in Excel 2021, and that is sought and sought by.
2
00:00:14,920 --> 00:00:21,550
So now, instead of using ribbons and commands in order to perform a sorts, we actually have some very
3
00:00:21,550 --> 00:00:28,090
specific functions which are going to help us salt and salt and salt by our brand new functions in the
4
00:00:28,090 --> 00:00:33,910
latest version of Excel, and they're part of a list of new dynamic array functions.
5
00:00:34,150 --> 00:00:38,110
Now, both of these functions are classed as look up and reference functions.
6
00:00:38,680 --> 00:00:42,880
So if you're looking for them in the functions library, you'll find them in the look up and reference
7
00:00:42,880 --> 00:00:44,590
group all the way to the bottom here.
8
00:00:44,620 --> 00:00:46,450
Sort and sort by.
9
00:00:46,570 --> 00:00:51,970
So let's have a quick demonstration of both of these so you can see how they work and how they might
10
00:00:51,970 --> 00:00:52,930
be useful to you.
11
00:00:53,110 --> 00:00:55,630
So let's start out with the SALT function.
12
00:00:55,870 --> 00:00:59,950
Now, the data that we're going to sought is, again, some very basic data.
13
00:01:00,370 --> 00:01:07,450
We have some students, we have the block that they reside in, an exam that they've taken and their
14
00:01:07,450 --> 00:01:13,030
pass mark, and we're going to use the salt function to sort these results.
15
00:01:13,240 --> 00:01:18,370
Now, previously, when we've been doing salts, we've been right clicking and selecting different commands
16
00:01:18,370 --> 00:01:19,210
to use here.
17
00:01:19,780 --> 00:01:25,210
And all of these commands really rely on you already having your data in the table in order to sort
18
00:01:25,210 --> 00:01:25,420
it.
19
00:01:26,170 --> 00:01:32,920
Now with this salt function and also the salt by function, we can basically output this data into a
20
00:01:32,920 --> 00:01:34,270
different range of cells.
21
00:01:34,450 --> 00:01:39,700
But the data that we output is going to be sorted as we want it to be organized.
22
00:01:39,910 --> 00:01:45,970
So what I've done here is I've copied across the column headings into another range of cells, and we're
23
00:01:45,970 --> 00:01:48,460
going to use the SALT function to sort this data.
24
00:01:49,240 --> 00:01:53,350
So let's click in block and type in equals salt.
25
00:01:54,670 --> 00:02:00,370
Now we have four arguments here and notice that three of those are optional arguments.
26
00:02:00,670 --> 00:02:03,250
The only one that's mandatory is the array.
27
00:02:04,000 --> 00:02:09,010
Now, the array is basically the cell range that contains the data that you want salt.
28
00:02:09,670 --> 00:02:14,770
So for this sort, my array is going to be all of this information just here.
29
00:02:14,800 --> 00:02:17,770
Now, notice that I'm not including the column headings there.
30
00:02:18,010 --> 00:02:23,080
I don't want those included in my salt now because that is the only mandatory argument.
31
00:02:23,320 --> 00:02:25,900
I could stop for my formula there and close the bracket.
32
00:02:26,470 --> 00:02:32,380
And if I hit enter, it's going to salt my results effectively by that first column.
33
00:02:32,500 --> 00:02:37,000
The default is to salt my results by the first column A to Z.
34
00:02:37,150 --> 00:02:43,420
So this list is now sorted by the block in a to z sort order that that might be absolutely fine.
35
00:02:43,420 --> 00:02:47,800
And if that's what you want to do, then you can just leave it just there with one argument.
36
00:02:48,550 --> 00:02:54,190
However, what if I want to sort by one of the other columns, so maybe I want to sort by them mark
37
00:02:54,430 --> 00:02:55,540
in descending order?
38
00:02:55,540 --> 00:02:58,990
So I have the highest mark at the top going all the way down to the lowest.
39
00:02:59,170 --> 00:03:00,940
So let's undo.
40
00:03:01,210 --> 00:03:04,840
This is where we can use some of these other arguments.
41
00:03:05,170 --> 00:03:06,820
So I'm going to select my array again.
42
00:03:07,510 --> 00:03:11,530
That's this range just here comma sort index.
43
00:03:12,160 --> 00:03:19,750
So this is where I tell Excel what column I want to sort by a much like v look up, which numbers columns
44
00:03:19,750 --> 00:03:20,720
from left to right.
45
00:03:20,740 --> 00:03:22,210
This works in the same way.
46
00:03:22,900 --> 00:03:25,930
So my sort index is going to be a column number.
47
00:03:26,620 --> 00:03:35,500
So if I want to sort by the mark column that is column number four comma, the sort order is where I
48
00:03:35,500 --> 00:03:40,270
can specify if I want to sort in ascending order or descending order.
49
00:03:40,870 --> 00:03:45,670
Well, I want to saw in descending order because I want the highest pass mark at the top going down
50
00:03:45,670 --> 00:03:46,510
to the lowest.
51
00:03:47,080 --> 00:03:49,690
So my argument here is minus one.
52
00:03:51,160 --> 00:03:55,330
And then the final argument on the end, which again, you don't have to put in is if I want to sort
53
00:03:55,330 --> 00:04:00,850
by the column, all sort by the row, and I will say the majority of the time you're going to sort by
54
00:04:00,850 --> 00:04:01,390
the row.
55
00:04:01,510 --> 00:04:03,160
So we want a false argument.
56
00:04:03,160 --> 00:04:08,530
On the end of that, let's close our bracket and hit enter and see what we get.
57
00:04:09,430 --> 00:04:14,710
So now I have my results sorted in descending order by the mark.
58
00:04:15,070 --> 00:04:17,260
So, very simple and straightforward.
59
00:04:17,590 --> 00:04:23,920
And because these are dynamic arrays, it means that if any of this data changes, then my sort is also
60
00:04:23,920 --> 00:04:24,820
going to update.
61
00:04:25,540 --> 00:04:33,160
So if I make a change to, let's say, the block just here, maybe I want to have East Wing in here
62
00:04:33,310 --> 00:04:34,120
and hit enter.
63
00:04:34,360 --> 00:04:38,080
Notice how it's also changed in the sorted results.
64
00:04:38,890 --> 00:04:40,630
Now I'm going to undo that.
65
00:04:41,620 --> 00:04:45,010
Now what if I added another entry on the bottom here?
66
00:04:45,010 --> 00:04:46,750
Is my sort going to update?
67
00:04:47,440 --> 00:04:51,910
Well, let's add East Wing at the bottom and Press tab.
68
00:04:52,330 --> 00:04:54,340
Notice that it doesn't update.
69
00:04:54,940 --> 00:04:59,250
Now I could get around this problem by putting this data in a table.
70
00:04:59,260 --> 00:05:05,800
First of all, because remember, with tables, they auto expand to accommodate any new information.
71
00:05:06,400 --> 00:05:07,810
So let's do this again.
72
00:05:08,110 --> 00:05:10,260
But let's put our data into a table.
73
00:05:10,270 --> 00:05:13,450
First of all, it's a control T to create a table.
74
00:05:13,660 --> 00:05:15,330
Yes, my table has headers.
75
00:05:15,340 --> 00:05:16,150
Click on OK.
76
00:05:16,690 --> 00:05:18,650
You could go in and change that formatting.
77
00:05:18,670 --> 00:05:20,320
I'm not going to bother right now.
78
00:05:20,830 --> 00:05:23,470
Let's do a sort of, let's say, equals sorts.
79
00:05:23,860 --> 00:05:30,520
I want to sort by this range just here and notice now because I have this data in a table.
80
00:05:31,000 --> 00:05:37,840
It's giving me the table name, which is simply Table two, because I haven't renamed it my sort index.
81
00:05:37,840 --> 00:05:41,440
Well, this time, let's sort on the student name A to Z.
82
00:05:42,190 --> 00:05:45,070
So the student name column is column number two.
83
00:05:45,970 --> 00:05:47,590
I'm going to soar A to Z.
84
00:05:47,650 --> 00:05:53,540
So I'm going to say ascending order, and I don't really need the last argument on the end.
85
00:05:53,570 --> 00:05:55,750
So let's just close the bracket and enter.
86
00:05:56,740 --> 00:06:03,610
So now I have this list sorted by student A2Z, and because this is in a table, if I add another entry
87
00:06:03,610 --> 00:06:04,390
on the bottom.
88
00:06:04,390 --> 00:06:08,170
So let's say East Wing as soon as I press tab.
89
00:06:08,200 --> 00:06:09,130
Take a look at that.
90
00:06:09,130 --> 00:06:12,460
It automatically adds it to the bottom of this list.
91
00:06:14,110 --> 00:06:17,980
Let's just add the rest of these details all like so.
92
00:06:18,220 --> 00:06:23,080
So think about incorporating tables if you want everything to dynamically update.
93
00:06:23,440 --> 00:06:29,320
Now, when I'm making my table array selection just here, I don't necessarily always have to select
94
00:06:29,560 --> 00:06:31,120
the entire dataset.
95
00:06:31,810 --> 00:06:40,810
For example, if I just wanted to extract the block and the student name when I do my sort over here,
96
00:06:41,080 --> 00:06:45,160
when it comes to the array argument, I don't have to select the entire table.
97
00:06:45,460 --> 00:06:48,520
I could select just the columns that I want to sort.
98
00:06:48,520 --> 00:06:51,880
So block and student, let's go all the way down to the bottom.
99
00:06:52,660 --> 00:06:55,060
Again, notice what I have in my formula.
100
00:06:55,060 --> 00:07:03,500
It's giving me my formula name and then both of the column names I want to sort by the student name.
101
00:07:03,520 --> 00:07:05,710
So that is column number two.
102
00:07:06,940 --> 00:07:10,210
And this time let's sort in descending order minus one.
103
00:07:10,690 --> 00:07:12,250
Close the bracket, enter.
104
00:07:12,640 --> 00:07:15,190
And there we have our information sorted.
105
00:07:15,520 --> 00:07:20,890
And just another point on that, if I wanted to sort by these three columns, let's just copy and paste
106
00:07:20,890 --> 00:07:21,610
those over there.
107
00:07:21,790 --> 00:07:29,320
When I do the sort this time my array, I only need the student, the exam and the mark so I can select
108
00:07:29,320 --> 00:07:30,970
this array just here.
109
00:07:31,240 --> 00:07:36,070
Now, when it comes to the next argument where we select the column, the column isn't going to be column
110
00:07:36,070 --> 00:07:40,090
number two, it's column number one of the selected array.
111
00:07:40,810 --> 00:07:45,610
So I sort index in this case, if you want to solve by the student is going to be column number one.
112
00:07:45,940 --> 00:07:49,990
And then we can do our sort order, let's say, ascending and hit.
113
00:07:50,240 --> 00:07:54,760
And so a few different examples there of how to use that sort of function.
114
00:07:54,880 --> 00:08:02,500
Now one more thing here is you cannot perform a sort on non-contiguous columns, so you can't sort data
115
00:08:02,500 --> 00:08:04,240
that isn't next to each other.
116
00:08:04,900 --> 00:08:11,560
So, for example, I couldn't have two column headings over here for, let's say, student and also
117
00:08:12,850 --> 00:08:19,570
mark, because the student column and the mark column have an exam column in between.
118
00:08:19,750 --> 00:08:22,420
They're not adjacent, they're not next to each other.
119
00:08:22,630 --> 00:08:24,260
So now we know how salt works.
120
00:08:24,280 --> 00:08:27,820
Let's take a look at sort by because it's a very similar thing.
121
00:08:28,060 --> 00:08:32,620
Now, this function will allow us to specify which column we want to sort by.
122
00:08:32,650 --> 00:08:34,060
So kind of similar to soar.
123
00:08:34,060 --> 00:08:36,760
In many ways, we have the same data.
124
00:08:36,850 --> 00:08:39,400
So let's see what sort by can do for us.
125
00:08:39,790 --> 00:08:42,100
Now, the first argument here is the array.
126
00:08:42,610 --> 00:08:46,960
So again, I'm going to select all of the data that I want to sort out.
127
00:08:47,740 --> 00:08:50,050
Our next argument is by array.
128
00:08:50,160 --> 00:08:54,690
One, so I can go in and choose the column that I want to sort by.
129
00:08:55,530 --> 00:08:58,260
So maybe I want to sort by the mark.
130
00:08:58,380 --> 00:09:03,250
So we're going to select this column and then we need to provide the sort order.
131
00:09:03,270 --> 00:09:06,060
So I want to go from 100 down to the lowest mark.
132
00:09:06,060 --> 00:09:09,780
So we're going to say in descending order minus one comma.
133
00:09:10,830 --> 00:09:13,140
My next argument is by array two.
134
00:09:13,170 --> 00:09:20,130
So this method allows me to sort by multiple columns because my by array two argument.
135
00:09:20,400 --> 00:09:24,570
Well, let me go in and select another column, a second column to sort by.
136
00:09:25,230 --> 00:09:27,660
So this time I'm going to sort by the block.
137
00:09:27,990 --> 00:09:34,620
So let's select that column, and I'm going to do that in ascending order, which is a one argument
138
00:09:34,620 --> 00:09:35,220
on the end.
139
00:09:35,940 --> 00:09:38,280
And I could carry on going, let's do another column.
140
00:09:38,400 --> 00:09:40,340
I can choose another column to sort by.
141
00:09:40,350 --> 00:09:42,480
So let's do the student name.
142
00:09:43,080 --> 00:09:47,190
And I'm going to sort A2Z, which is a one on the end there.
143
00:09:48,000 --> 00:09:49,260
Now I'm going to leave it there.
144
00:09:49,380 --> 00:09:51,570
Let's close the bracket and hit enter.
145
00:09:52,260 --> 00:09:54,410
And there is my sorted list.
146
00:09:54,900 --> 00:09:59,700
I'm sorting by Mark, then by block and then by student.
14254
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.