(upbeat music)
- As you work with lists and Excel
it's not uncommon to want to rearrange the order
of rows within a list.
We call that sorting.
And although there is a way sort by columns,
moving columns left and right.
Nearly always sort means let's rearrange the order
of the rows based on what we see in one or more columns.
As we look at this worksheet here,
appears to be sorted by either column H or J
descending order.
We might want to rearrange it by department.
Anytime you're about to sort of list,
if there's other data on the same worksheet.
And I do have data off to the right, I need it there.
Perhaps I've got more way off to the right too.
I need that there, but I certainly don't want to sort it
along with the other rows.
So, before sorting lists always make sure that your list
is delineated by at least an empty column on the right.
And if there happens to be data below it,
make sure you've got a few empty rows
separating that data as well.
When we're ready to sort, we can highlight the data.
Or if we know that we have no empty rows or columns
within N we can just click within the data
and start using the sword commands.
It's not a bad idea,
particularly if you haven't seen the data in a while
or someone else sent it to you.
Click within the data, press control A.
Think of A for all.
then press control period a few times.
This will simply move the active cell around the corners
of the range.
What you don't want to have happen is to end up sorting
and then realize that only some of your data got sorted.
We get onto the bottom here, keep pressing control period.
And again, you don't have to do that very often.
Just a quick reminder.
Okay, we're ready to sort.
We can keep the data highlighted or click within it.
On the home tab you will see sort and filter,
you can click there and then go into custom sort.
Or you can go to the data tab
and use the larger the sort buttons right here, sort.
This doesn't say custom sort,
but it means the same thing.
Two small buttons here we'll get to in a minute.
Sort, Excel highlights the data.
And if there's a header row,
Excel nearly always figures it out.
If that's the case,
you want to make sure this box is checked.
We don't want row one treated like all the others.
Rare times, would you not have a header.
And of course this should be unchecked.
We can sort on the basis of what's in one column,
two columns, three columns.
Older versions of sort only let us work on the basis
of three, we can now go as high as 64.
We want our major grouping here.
Let's say by department.
So, sort by, click the drop arrow.
It shows our field names, department.
Now in some departments,
there are going to be a lot of people there.
So as we're looking at that department
we might want to add level.
Let's sort by status.
And we might even, at some departments have a lot of people
with the same status in the same department.
So we could add another level.
Maybe even another level.
If it's a text field,
you probably want these to be in alphabetical order.
If it's a numeric field, say that third level in.
We might want this to be by years.
Consider whether you want it to be smallest to largest
or largest to smallest.
Maybe we'll have a fourth level here by employee name.
Sorting is very fast.
Click okay.
Sometimes it seems practically instantaneous.
We're about to see a new list sorted by department,
within each department, in order by status,
within that by years, within that by employee name.
And there it is.
There are a bunch of contract people,
account management, here are the full-time people.
Quite a few of them here.
Full-time people, account management.
Notice that in column F,
although we're not seeing the heading
that's years of service.
So they're in order by years of service.
And in this group right here
they have the same status, years of service, department.
They're in order of alphabetical
you quickly get the feel of what sorting does for you.
Simply moves rows up and down nearly all the time.
At a later time, if you simply wanted to resort this list
by employee name, you could click somewhere in column A.
Click the simple AZ button right here,
going to rearrange the entire list by employee name.
Now, if the next sort you do
either immediately or a few hours or days later.
If we click in column C and then choose AZ.
We're now rearranging the data based on what's in column C.
But Excel does remember that right now
it's in order by employee name.
So now within each department
it's an order by employee name.
And if at a later time I come back and click in column D,
I'm going to sort by status.
Excel remembers the previous orders.
So sometimes when you're sorting on multiple levels,
if you remember that the last sort always overrides
but remembers the previous order.
You can even use these buttons in succession
as you get more comfortable with sorting.
Also from time to time, there are special options in here.
You can sort on cell color.
We don't have any on the screen here or by font color
or if you're familiar with conditional formatting icons
you can sort on that basis as well too.
If there is that occasional but rare need
to sort by moving columns left and right.
You'll jump into options and you'll see that choice,
sort left to right.
So sorting is generally full-proof, fast, relatively easy
and it's absolutely indispensable
as you work with large lists of dates.
(upbeat music)