If you're using Google Sheets and you have a table of data, you want to
extract the top five amounts or the bottom three, there are several different
ways to do it. We're going to go through the best four of those and talk about
how each of them work and what's good about them what's bad about them.
Let's just dive right into it. The first one that we're going to do is just
sort it and then delete what we don't want. We want to highlight all of the
data that you want to sort. You can include the headers. That's fine. Go to
data, and then don't do these preset options because they're guessing the
wrong column. We don't want column B, we want column C. Click Sort range.
Tell it, yeah I have a header row and when you say you have a header row, it's
going to pre-populate these options with the header labels. We want to sort by
runtime so choose that. You can sort it forwards backwards. Let's just keep it
on ascending and click sort. Now all of our data is sorted by the runtime and
if you wanted the first one, two, three, four, five, six,
select all the others hit your delete key and you're done, right? That's a
simple way to do it, and if that's all you wanted to do, and the resulting data
is fine, then just do it that way. The disadvantages of doing it this
way, one it changes the order of it and two you don't have this data anymore.
You deleted it, it's gone. So, if any of this needs to change or if you just want to
access it for some reason, this isn't the best method to use. Let's go on to the
second method. I'm going to undo that sort by holding down the control key and
pressing Z. Let's select our data again. Go up to the Data menu and this time,
instead of doing Sort, we're going to create a Filter. Filter is kind of a first
cousin to sort. It can sort, but it can just do a lot more also. So let's expand
these columns a little bit though because it hid the headers. These are the
sort buttons that come onto the table now that you can use, but they're going over my
header there. Let's move this column to the right a little bit. We want to
sort by throw distance, right? Let's get the top three throwers. This drop-down
puts the data in order for you. We want the
longest three so the first thing that you want to do is click Clear. Scroll
down to the end and just put a checkmark back on these three now. There's other
things that you can do in here. You can do some mathematical functions, greater
than less than, is between, you see them here, but in this case all we want to do
is get the longest three. So, with those three checked, click OK.
There you go. You'll notice something. There were some ties and filter just, by
default, decides to show those. This is the top three values, there's just
duplicates of some of the values. You'll notice they are filtered by throw
distance for you, but they're still in their original order. One of the
important advantages of this over sort is that that the other data is still there.
If you just go in and you turn off the filter, everything comes back and
it's in its original order. Using the filter is a more dynamic way to do it
than sort and you don't have to worry about deleted data because things are
hidden, but they're not gone. Those are the first two. They're definitely the
most straightforward ways to do it. These next two, they're probably more fun,
they're definitely more powerful. I made some of these already over to the right.
Let's go grab them. Let's just delete these filler columns I put in. Voila!
All right. These are some pretty cool functions we used here. There's LARGE,
which is the first one that we're going to go over and over to the right here
will be using SMALL. Let's talk about the parameters that we use. So, what large
does is it returns "n", which is whatever number you give it. Let's just say, in
this case it's one. It returns the largest one value in a data set. This
parameter, and this little helper text, is going to show you the data that it's
going to look at. I'm saying, hey LARGE, look at everything in this column and
give me the first largest number which is H3. This could be just, you could
just hard type a 1 in here. I did it as a cell reference to H3 because I want to
be able to just drag this down and get number two, three, and four. You'll notice
I use dollar signs on the range. What that does is, it says don't shift this
range down when I drag this formula down. That's a fixed
reference to a range. If I go into the next one, it's the exact same because I fixed
it. But, the lookup of the N value has changed because I didn't fix that. So,
it's looking up the second largest value. This is pretty cool, right?
Drag it down. Do as many as you want. The same thing is happening over here
with SMALL. It's looking at the range, in this case it's run time because running
faster is better, right, and it's picking the fastest run time. The
advantages of this are that it leaves all of this data intact, right? It doesn't
move this at all. It doesn't sort it. It doesn't delete it. You can format this any
way that you want outside of the table, but one of the disadvantages is that it
doesn't grab any of the other data from the row. It won't return the jersey
number, or the first name, and the last name. This could be great, depending on
what you're looking to do. But, if you want to grab the entire row, the last
technique here, and I think it's probably the coolest one, I think it's
probably the best one as well, it's going to be the most dynamic for different
situations, is called I need a little drumroll here, in the meantime let me
grab my headers because we won't do headers. Build the anticipation a little
bit. I'm going to go into cell H9 and we're going to use a function called
SORTN. This is a function that's in Google Sheets, but it's not in Excel. So,
it might be new to you. What this function wants to do, it's going to look
at a table of data. So we're going to give it this table. Skip the header, just
do the data itself. You don't need to fix it because this function only will go in
this top left corner of where it's going to put the results. It's going to write
data below it and to the right of it - and diagonally - but you only have to do
it once. Let's do a comma. The second parameter that it wants is how
many of them do you want me to return? Let's say, let's do six. We'll have to go up
here in the upper left-hand corner because the helper text kind of covered
up our function. There, so we're going to return six.
I hit a comma and it says, what do you want me to do with the ties? Do you want me to just
show one of the ties? Do you want me to expand the results if I have them? If you want to
go more into understanding that, there's a different video on SORTN. I'll put a
little link to it in this card in the upper right hand corner if you want to
watch it. But, for now, let's just use a zero here. The next thing we need to do
now is tell it what column we're going to look at. So, be careful here. This isn't
column C or column D, it's column two or three. That number is the number of
columns from the left of where the data resides that you want to sort by. Let's
say we're going to sort by throw distance. That's the one, two, third
column. Put in a 3 and let's pick a FALSE for how to sort it and let's hit
enter. Here we go. This picked up the longest six row times and it picked up
the corresponding names and the jersey numbers. It gave me some funky
formatting, so let's fix that. It preserved this table of data. If something
changes in this table and it's part of this resulting query it will update here
as well. So you don't want to put anything in these cells. The formula
won't like that because it's using them. But, it's really only typed into H9, but
it's writing data all the way over here. If you want to learn more about the
SORTN function, I'm going to put that video right here, you can click through
that and watch it. If you enjoyed this video, and want to see others like
it, you can subscribe in the lower right-hand corner. Thanks!