Hey, thanks for joining me on this video.
My name is Jason Morrell.
I'm the senior trainer here at Two Rivers Software Training.
It is a beautiful Queensland afternoon, and it's a great day to be alive.
Today, I want to talk through everything you ever wanted to know about sorting data within
Excel.
There's lots of myths, misconceptions, wrong ways of doing things, common mistakes, and
I want to walk you through all of those and make sure you don't do them anymore.
I'm going to show you how to sort on a single column, multiple columns, sorting by value,
colour, or icon.
You can even sort in a custom way if you want to.
Rather than alphabetically or numerically, maybe there's certain things you want to put
in a certain order.
You can do that.
I'll show you how deal with duplicate data.
Sorting a list and putting all your [inaudible 00:00:49] at the top so you can then deal
with them as you need to.
I'll finish off today by talking about how sort columns into order.
A lot of people do this by cutting, pasting, hacking, [fixing 00:00:58] their mistakes
and tidying things up afterwards.
It's messy.
I'll show you a nice elegant way to get that done.
Sound good?
Let's dive in.
Here we have a table.
It doesn't really matter what's in the table as far as this demonstration goes.
Let me just explain what's here, quickly.
It's a contact database of tradespeople.
It shows what they're called, first name and last name, where they live, when the entry
was logged, what car they drive, what their trade is, what their hourly rate is, and how
many jobs they've been allocated.
Just a mixture of text, dates, and numbers.
All the common stuff you'll be working with on a day-to-day basis, if you use tables.
At the moment, the data is sorted into [suburb 00:01:43] order.
A to Z on suburb.
What we're going to do in just a second is to resort this table into a different order,
simply, but before I do that, let me show you a misconception, a common thing that people
do before they do their sort process.
That is, to select the whole table.
You don't need to select the whole table before you sort.
That's just a wasted step.
The other thing that people do, which is even more dangerous, is they'll select a column
of data within the table.
Now, Excel will warn you before it does this, but if you go ahead and ignore the warning,
it actually just sorts that column, and leaves everything else static.
Everybody will be allocated a new trade if you're to do that.
Don't ever do that.
That's the second thing you shouldn't do.
To sort simply, let's say you want to sort it by hourly rates, you simply click a single
cell in that column, go to your [dialogue tab 00:02:34], find the sort and filter group,
and click either A to Z or Z to A, depending on whether you want the highest rates first
or the lowest rates first.
That's it.
Maybe you want to resort this list into trade order.
You click a single cell in the trade column, click either A to Z or Z to A, and again,
it's sorted your list properly.
Every row stays intact.
Every trade belongs to the person it belonged to before, which is what you want.
You quite often need to do multidimensional, or multilevel sorts.
Let's say for example, you want to sort first of all by state, and then by trade, and then
within each trade, put the hourly rates into order.
For this, you need to go to the big sort button at the top.
This opens up another dialogue box with a few more options.
What we do first is, we sort by the top level item, which is state.
That is A to Z.
That's good.
We then add a level, and on this one we choose trade.
Values A to Z again.
That's good.
Then one last level.
We add, and then we choose hourly rates, values smallest to largest.
Because hourly rare is numeric, it shows smallest to largest, rather than A to Z, but it's still
sorting into order.
If we click okay now, have a quick look at the data, you'll see the states here are blocked.
You'll see within each state the trades are blocked up, and then for each trade, it shows
the hourly rates in order.
That's a three way sort.
A three level sort.
Very easy to do.
Another thing you'll see here, which you may or may not recognize, in the jobs column,
you've got these icons next to each number.
This is created using what's called conditional formatting.
That's another whole topic, but basically every number has been assigned a colour, or
a symbol.
If you want to sort the list into colour order rather than value order, it's very easy.
You can start anywhere in the table.
Click the big sort button.
We're going to remove the existing levels, so delete and delete, and on the one that's
left, we're going to choose to sort by jobs.
Now, rather than sorting on values, smallest to largest, we're going to change that to
say cell icon.
It shows the icons over here.
We're going to put red on the top.
In a minute we're going to do a couple of other levels, but just take a note that in
that list, there's also cell colour and font colour.
If you want to do either your own manual formatting, or manual tagging, or use some of the tools
like finding duplicate values or conditional formatting, you can colour code your cells
and then sort by colour, either font colour or cell colour.
To continue this example, we're going to add two more levels here.
We put red on top already.
To add the extra levels, you could add level and then reconfigure the next one, but there's
also a copy level button, which duplicates, or clones the current level.
We're going to click that twice.
Then all we've got to do for each one is to change the red to yellow, and the red to green,
on each subsequent row.
It now shows red on the top, then yellow on the top, then green on the top.
Because black is the only thing left, it will default to the bottom, which is what you want.
When we click okay, you will see traffic lights sequence.
It's ordered by colour.
You can do that for any icon set, any colour that you come up with.
Let me take you through another example that deals with duplicate data.
We're going to do a two step process.
We're first of all going to highlight any duplicates in a list with a different cell
colour and font colour.
We're then going to sort that list to put the coloured cells at the top, so when you've
got your duplicate data together, you can then see which one's the most current, or
which one do I want to keep, and you can deal with it and delete the stuff you don't need.
For this, you can use a single column if you wanted to, but quite often with first names
or last names, if you're just using those things, then there could well be two Freds
or three Joes or four Maureens.
Surnames can also be repeated in the same list.
You don't really want to rely just on one column.
It's better if you can, in the case of name, to join the two things together and get a
full name, but even then you might still have two Fred Blogs in your list.
Quite often, something unique is better, like an employee number, or a customer code, or
a product code, or something like that.
Something that's designated and unique to that item.
What we're going to do here is we're going to insert a column.
I'm going to call this one full name.
I'm going to create a formula which concatenates the first name and last name together.
Now, concatenate is just a long word which means join things together, or connect things
together.
The simplest way is to use the ampersand character, which is shift and 7 on your keyboard.
Let me show you this.
We're going to say equals, click on the first name, then ampersand, quote, space, quote,
ampersand again, then last name.
If you look at the formula here, the ampersand is the connecting character, so it's joining
the first name with a space with the last name.
The notation you see at the top here is the model notation for modern tables.
It's just referring to what's in the first name column and what's in the last name column
for each row.
Press enter.
There's your full name.
Because it recognizes it's the same pattern all the way down, it's populated for us.
That again is a benefit of modern tables.
I'm going to re-size this, and we can see, here's our list of full names.
On this list of full names, we're going to highlight the duplicates.
For this, you just need to select all the names, like so.
We then go to the home tab, find conditional formatting, highlight cell rules, and then
duplicate values.
It comes up with a few preset formats, and because all we want to do here is to highlight
them, I'm just going to stick with the standard format here.
Click okay.
You can see straight away there's two people that have duplicate entries.
Shark-tooth and Jimbo.
To put them at the top, we're now going to sort the list and put the coloured cells at
the top.
For this, doesn't matter where you are on the table.
Go back to the dialogue tab, click sort, get rid of these entries here, and on the sort
by we're going to choose now full name.
Instead of cell icon, we're going to choose either cell colour, because that's like the
light red, or the font colour, which is now the dark red.
Either of those would work.
Then choose the colour we want to put to the top.
Full name, based on cell colour, putting the pink or the red at the top.
Click okay, and you've now got all your duplicate entries at the top of the list.
Sometimes, and especially if there's a few, you might well have an order here which says,
"Shark-tooth, Jimbo, Shark-tooth, Jimbo."
Again, it could be quite hard to actually work with.
Especially if there's a few of them.
What you may want to do is to add a second level here.
Click the sort button again.
We're going to add a level, also based on full name, based on values this time, A to
Z.
Let's leave that as a second level.
Click okay.
It's now put the names into order for each segment.
The red segment and the non formatted segments.
If they weren't in order before, they will be now.
You'll notice that it's actually switched the two names around, because it's not alphabetical,
A to Z.
Because we've got all the duplicate entries together, you can now decide which of these
to keep, and which of these to delete.
If you look over here at log date, you can see that for Jimbo there's one entry on the
13th of February, 2016, and an older one on the 12th of November, 2015.
For Jimbo, you would delete the oldest one.
For Shark-tooth, it's got two entries again.
One in July 2015, one in October 2015.
You have to assume that the October one is the latest, so you'd then delete the July
one.
That's not hard and fast rule.
Obviously you know your data, and you can decide what situation.
Sometimes you don't want to rely on A to Z or Z to A, either alphabetically or numerically.
You want to put stuff in your order, your particular order.
Maybe there's some elements that are more important than others, and you want to put
them to the top of the list, even though they're not alphabetically first.
To do that, you create what's called a custom order.
Click in anywhere in the table.
Click the sort button.
Get rid of the second level.
What I'm going to do here is to sort on the trade.
We're going to base it on values, but rather than going A to Z, I want to create my own
order.
Maybe I want to put plasterers first, and then roofers, and then brickies, and then
plumbers.
Instead of A to Z, I choose custom list.
You'll see some existing entries.
The days of the week, the months of the year, and both the short and the long versions of
those.
They're built into Excel.
They're existing lists you could use.
What we're going to do in the list entries box is to put our custom list in for the order
we want our trades to be listed.
Let's put plasterer first, and then roofer, and then brickie, and then plumber.
It's going to match what's in the table, so make sure there's no typos.
Click add.
It adds it to the left-hand list there, and I can now click okay.
You'll see the list is begin used there as the order, and then click okay.
Now if you look at your trades list, there's your plasterers, then your roofers, then your
brickies, and then your plumbers.
The final thing I want to show you is how to sort your columns into order.
Mostly when I ask people how they would do this themselves, they'd say, "You just cut
and paste, or copy and paste."
You have to insert your columns.
You have to paste it all in.
Often that goes wrong and you have to tidy things up.
Hacking stuff around.
If you're careful, you can make it work, but let me show you a more elegant way to go here.
Notice the blank row above the table.
We're going to use this to put a number sequence in above the main headings.
Let's assume that we're interested in say first name and last names.
They can stay.
But maybe we want to put trade and hourly rates in columns three and four, and then
maybe everything else can just stay where it is.
For this, we would choose one, two, three, four, or whatever order you want to do for
any column.
Anything you leave blank, it just falls into its natural order.
Just the four columns here is what we're after.
We then select that whole row of numbers and the entire table.
Now, when I mentioned it earlier, you should never select your whole table, before sorting,
but in this instance you have to, because if we don't, it won't recognize that row one
as part of the table, and we won't be able to use it for our sorting.
Now it's all selected.
Go back to data if you need to.
Click the sort button.
Now, if you look at the sort by, it's currently showing the column letters within the spreadsheet,
so column B, column C, column D. That is of no use to us at this stage.
The first thing we're going to do, and this is the key step, is to go to options, sort
left to right, which means we're now sorting columns into order, rather than top to bottom,
which is rows.
Click okay.
The sort by options now change to Excel row numbers.
What it's asking is, within the range you chose, which of those rows contains your sort
sequence?
For us, that's row one, at the top here.
The numbers are going to get used, based on the value smallest to largest.
That's the way it's going to sort.
We click okay.
You can see now, one, two, three and four.
You see the table has reordered neatly.
One, two, three and four is the first four columns we wanted.
The rest are just in a natural order.
You may have noticed a couple of things.
Firstly, all the original column widths stay where they are.
It's just the data that gets moved.
Things like the street is now in a narrow column, you'll have to re-size all of those.
Quickest way to re-size everything is to click the block in the corner between A and one.
That selects every cell on the sheet.
Then just go to any column divider between any two columns and double click.
That best fits each column separately.
You'll also notice, in the trade column, it's still got the duplicate checks in that we
had there before.
Previously it was checking full name.
It doesn't really make a lot of sense now for trade, because each trade is used a lot
of times.
You may need to just go through and clear up the conditional formatting.
Home, conditional formatting, then down at bottom, clear the rules, and it goes back
to regular data.
We've been through quite a few things there.
Let me just recap briefly.
There's quite a few common mistakes that people make, and I hope they're clear now.
You shouldn't be either selecting the whole table, or selecting an individual column before
you sort.
Just select a single cell, if it's a one column sort you want to do, or go to your data tab
and the big sort button, if you want to do more than one level.
We've talked about how to sort by value.
Numerically or alphabetically.
We can sort by cell colour, or font colour, or cell icon.
We can create a custom order, like we did for trade, to put things into a particular
order.
We've covered how to deal with duplicates.
Then what we just did, which was to sort the columns into order.
Hopefully, you picked a few gems out of that video, a few value bombs.
Now, if it has been useful, I would always appreciate if you subscribe to the channel,
or if you could like, if you're in Facebook, or if you want to connect in any way at all,
keep watching, the details will follow.
That's it for today.
Have yourselves a great day.