I have an example spreadsheet here that has some pretty typical data
in it. When you have data like this, a lot of times you just want
to put it in a different order which would be SORT. Or you only want to show
part of it based on some sort of criteria and that would be FILTER.
I'll show you sort first and then we'll get into filtering a few items.
Keep in mind though, you can do this two main ways. We're going to sort and
filter from the menus first but then I'll show you a new concept that didn't
really exists in previous spreadsheets is that there's functions that can do
sort and filter. To do those, we'll go down below because they produce a new
list that's based on the data in this list. All right, what you typically want
to do is select the table. That's gonna make this easier. Then you go to the
Data menu and then do Sort range. There's shortcuts up here if you just
want to sort the entire sheet. That's not going to know where your header is though so
that's gonna sort the header too. Maybe that's okay. You can also just sort a
range but we already selected the range so let's just go down to the Sort range
option. It's going to give you a pop up. I think it's easier to use. There's a
couple options on here that we can talk through that you're gonna want to change
from time to time and they're not in those shortcuts if you do the other
options on the menu. It's telling you, look we're looking at sorting A10 through
G10 which is what you highlighted back here behind this box. But, we did have a
header row and I don't want that sorted so put the checkmark there.
It's smart enough to take the names from the header row and put them in this
drop-down list now that you told it it has a header. In this case we're just
gonna want to sort this by region because we want to see what happened in
every region region. Keep it from A to Z and within those regions were gonna want
to sort it by Rep name. You click sort. It did not delete any data. It's showing
you the exact same data and you don't have to unsort it to do anything. You can
leave it like this. If you notice it does it by region alphabetically and then
within that region it did it alphabetically by the name of
Rep. That maybe is all you wanted to do in which case all you need is sort and
you're done. But, if you want to take this a step further by doing some more
analysis, let's select the table again, go to data, and let's try filter. I turned
the filter on and you can see at the top of every column of data, you got this
upside down triangle. That's letting you know that you can filter that column. But,
there is no filter selected yet. If you want to see just the pencils that were
sold, you want to clear all of these. Just clicked unclear and just highlight the
pencils. Click OK. That filtered out everything else in the list. The data is
still there. As you can see, it goes from 3 to 9 so those rows still exist
but they're hidden from view. One concept here that's important to know is
that if I sum these, if I just do E2 to E3, then you'll get 21 which is these two
being added together. But, if you do the whole range, and again you can see this
goes down to row 10, it's just hidden, it still picks up those values.
327. They're underneath and your formulas can pick them up if you're
doing simple formulas like SUM and COUNT. But, I digress. That's one type of
filter. Reset your filter. Just click select all so all of them are in play
and then do text contains PEN. That'll pick up pencils, pen set, and pin. Then,
once you have a filter on, you can see that changes from an image of the
upside-down triangle to filter so you know that there's an active filter on
here so that tells you it's probably not showing everything right now and that's
true. It still goes 6, 7, 9. 8 is hidden and 11 is hidden.
While you have a filter on you could still do another sort or another filter but
let's just sort this by Rep. The filter stays. It's still showing only the things
that have the letters PEN in them but this is sorted so it's not filtered
so it didn't turn into a funnel but you could also do another filter if you
wanted. Let's clear all of them and just so to show Jones and Morgan. And there
you go. There's two filters applied. You can apply as many filters as you want.
That's the basics of how to use sort and filter through the menu. We're going
to turn that off. There's a couple different ways to turn it off. I'm gonna to
turn off filter. Imagine that this is the data that we started with. I want to get
rid of this row 11 that's highlighted like it's part of the table. I don't like
that. I'm gonna right-click, delete row. Now I want to show you the second
main way to sort and filter which is using functions. There's three different
functions that we're going to go over. The first one is SORT. Hit
=SORT. Open it up with an opening parenthesis which goes on every function
and then it knows. You do the parentheses and it's ready to rock. Let's give it
a range. In this case, let's do A1 to G10. Matter of fact, let's do A2
so we don't pick up the header, We want to leave the header there, to G10.
Let's look at the helper text again. The second thing it wants is the
sort column. It's, "what do you want me to sort it by?" Let's do it by the Rep.
That's sort column three. It wants a number, not the name of the header. We
want it to be ascending so leave that as true.
Now you see the next thing in the helper text is brackets which is saying it's not
necessary, it's optional. So let's end this here. When I hit enter
it's going to write this out to the right and down below it. So start it
where it has room to work. And they're sorted lists. You see it got rid of the
formatting. That's alright, you didn't need it. This is all dynamic so, if
you change what's in the original list, it will change down here. If you change what's up here
it'll change what was output down here. If you click in one of these
columns and hit delete, delete, delete, it doesn't go anywhere because it's all
being driven by the SORT function in A12. If you were to go in A12 and press
delete, it gets rid of everything.
The second function that you
do is a FILTER. You can guess how this is gonna work. we don't have to go
through it in much depth because it's the same concept. It's going to output a
new list that's filtered based on how you want it to be and it's dynamic. If
you change what's above, it'll change what's output and filter it. It may take
some time to study this one. It's a little bit trickier to use FILTER
through a function, I think. But the last one that's added is a new type of
function. It's called SORTN. This one kind of
deserves its own little tutorial I think. What it can do is, let's give it the
range 10 is, in plain English, it's saying "Hey, I want the top 10 salespeople from
here. I want the last five dates. It can sort and return N number of values
from your data set. We'll try one here. I won't get into it too much. Let's try A3.
The top three. That's all you have to do. The other parameters are
optional. There we go. That's the first three sales. If you want to learn more
about these functions or about the SORTN function in particular, you can
subscribe to my channel. I do lots of tutorials on little things, mostly in
Google Sheets with some other things as well. I hope you enjoyed this video and
that's all. Thanks!