Preview
Video Player
00:00
00:00
Sorting & Filtering
6:39Use spreadsheet sorting features to sort rows of data: for example, list the rows of sales data in the order in which the sales took place by sorting on a sales date. Filter options let you view just the data you're interested in — just the sales in December or just the grades for a single student.
Another great thing about
spreadsheets is that it's
0:00
easy to organize data in different ways.
0:03
In our example, what were our most
expensive items, what were our total sales
0:06
at a specific location,
how about a specific location and item.
0:11
There are multiple ways to approach
answering these questions with a spreadsheet,
0:16
but in this video, we'll explore the sort
and filter aspects of spreadsheets.
0:20
Let's start with sorting,
which lets us order our data.
0:26
For example, we may want to list
orders by the date they were placed,
0:29
listing newest orders first and
older orders last.
0:33
How about we sort all this data by price?
0:36
So we can see the most expensive item
all the way down to the cheapest
0:39
product we sold?
0:42
To sort data in Sheets,
first select the data you want to sort.
0:44
It's important that you select
all the data you want to sort,
0:48
not just a particular parameter.
0:52
So, in this case if
we're sorting by price,
0:53
I don't just select everything in
column C, I want to select everything.
0:56
After you've selected all the data,
go up to the data menu,
1:00
select sort range notice, that this box
data has header row, is not checked here.
1:05
And that's because, I did not select
the top row one, that has the headers,
1:11
for the data.
1:15
You can sort alphabetical data, like the
names of cities from, A to Z or, Z to A.
1:16
You can choose from lowest to highest,
or vise versa.
1:21
In this case, we're sorting by price,
which is column C.
1:25
And I wanna see the most expensive item
at the top, so I'm gonna do descending.
1:31
Click sort, and voila.
1:37
We can see that the most expensive item
we sold was a statue of Mike the Frog.
1:40
As I said earlier, it's important to
select all the data you want to sort,
1:45
not just the data you're
interested in sorting.
1:49
See, if I select just Price here,
and not the Location or
1:52
any of the other information
in the sort and go run a sort.
1:58
We see that all that was sorted
was the Price information,
2:08
but the rest of it did
not go along with it,
2:11
and we know that Mike the Frog
statues do not cost $5.
2:13
Not selecting all the data that needs to
be included in a sort before running your
2:17
sort, is a common mistake
newcomers to spreadsheets make,
2:21
and can lead to bad analysis,
so watch out.
2:25
I just showed you how to sort
based on one parameter, Price.
2:29
But if we want to ask questions that
involve two different pieces of data like,
2:32
what is the cheapest
product we sold downtown?
2:37
In this example, it's fairly easy
to scroll down and look manually.
2:39
But that's not always the case.
2:43
Fortunately, we can sort on multiple
parameters like Location and Price.
2:45
We start off the same.
2:51
We select al the data we want to sort.
2:53
We go to the data menu, select sort range,
and start with our first parameter.
2:56
In this case, location which is column D.
3:03
Then click this link here,
Add another sort column and
3:09
choose your second parameter,
Price, boom, it's that easy.
3:13
You can add more sort criteria,
if you like, but
3:20
be careful as trying to sort by too
many parameters can become unwieldy.
3:22
And honestly, at that point you're
approaching some use cases where
3:26
more advanced techniques
are likely better.
3:30
Generally, when you start
getting above three parameters,
3:33
you should be using filters or
more advanced spreadsheet functionality.
3:36
Okay, so we've done the double sort and
can see that the cheapest
3:40
item sold Downtown is a $5.00 Handkerchief.
3:44
Sorting is great, but if you think you're
going to be doing a lot of sorting on
3:49
a spreadsheet, and regularly changing
those sorts, it's better to use filters.
3:53
This is because filters are generally
easier to turn on and off, and
3:59
don't require you to select
all the data you want to sort
4:03
each time you run your filter.
4:06
So, if you think you'll just need to sort
your data once or twice, sorting is fine.
4:08
If you're gonna be doing it much
more than that, you use filters.
4:14
Don't worry, similar to sort,
filters are pretty simple to set up.
4:18
Okay, so first we need to select
the data we want to create filters for.
4:23
Unlike sort, we do not have to select
all the data we want to sort or filter.
4:28
We just need to choose
the data header rows.
4:34
Then, we can go to the tool bar menu and
use the filter here,
4:38
or we can go back to the data menu and
select Filter.
4:43
So we have selected our Filters and
now can start using them.
4:47
How about we just choose items
that are in the shoes department.
4:52
Department, I'm wanna go down and
I'm wann to deselect General and
4:55
clothing and
I've just kept shoes selected.
4:59
I click OK and bam.
5:02
All the other departments
are removed from this set of data.
5:07
The items in the other departments
have just been hidden,
5:11
they're not gone from the spreadsheet.
5:14
Filters is also allows to sort as well.
5:16
So, in this view we got just
choice through the filters but
5:19
now let's sort by price.
5:23
We can go over to the filter
button under price column and
5:24
see there is a sort option.
5:29
So filters are great.
5:32
Now only do they allow us to filter data,
but
5:34
they also allowed us to
sort it very easily.
5:36
So, I'm gonna undo all these filters.
5:39
I can either manually click them here.
5:42
I could also just click the Select All.
5:44
But now all the data's back for
the different departments.
5:49
And I just wanna emphasize again
that when you filter out data,
5:53
it does not get removed
from the spreadsheet,
5:56
it's just hidden from your view,
so the data is still there.
5:59
Here's an example of hiding rows
when everything is still visible.
6:02
So, I'm selecting three rows,
five through seven.
6:06
I'm going to right-click on them.
6:10
And then I scroll down and
select Hide rows 5- 7.
6:12
And you can see that there's these two
arrows which signal that there are hidden
6:17
rows in there.
6:20
And you can also see that we
go right from row 4 to 8.
6:21
To show these back again,
you can click here.
6:24
You could also right click and
say, select or show hidden rows.
6:27
You can not only hide rows,
you can also hide columns.
6:32