All language subtitles for 1. Performing Sorts on Multiple Columns

In this section, we're going to talk about sorting and filtering data, because sorting and filtering

are two of the primary tools when it comes to helping you analyze your data.

I'm going to start now in this lesson by running through how to sort on multiple columns.

Now, when we sought our data, it basically allows us to organize our data into a more logical format

that is a lot easier for us to read and interpret, and most of us know how to do a basic sort.

So for example, if I wanted to sort this table of data by the last name in alphabetical order, I could

click in that column and then I have a few different options when it comes to sorting.

I could use my home tab over in the editing group.

I have a certain field to drop down and I can choose to sort that column in a to z order or Z to eight.

I could also right click my mouse and I have sought options in here once again, A2Z all said today.

And I have a third option of the data ribbon in the sort of filter group we have sought A to Z and Z

to Albertson's in here.

Now I'm not going to demonstrate those because this is an intermediate course.

So by now we should know that that is how you do a basic sort.

What I want to focus on in this lesson is how we can do a more advanced sort where we're sorting on

multiple columns.

So a lot of the time when you're faced with a data set, you don't just simply want to sort by one column,

you want to sort by maybe two three, four five, so on and so forth.

And in fact, you can sort by every single column in your dataset if you wanted to.

So let's take a look at how we would perform a sort on multiple columns.

Now, for this particular sort, you don't need to click in any column in particular.

And as we already on the data tab, I'm going to use the salt button that we have in the Salt and Filter

Group.

So when we click on this large Ibbotson, it's going to open up this little salt dialog box.

And this allows us to choose which columns we want to so on.

So if I click this first one where we have sought by, it's going to show me all of the columns that

I currently have in my dataset.

So I want to sort by the last name.

First of all, I can then choose what I want to source on so I can sort on cell values.

So color, font, color or a conditional formatting icon.

Now in this example, we're going to sort on the cell value and then I can choose my sort order A to

Z, Z to A or using a custom list.

And I'm going to show you how to sort using custom lists in the next lesson.

For the time being, I'm just going to sort by last name A to Z.

Now, if I then want to sort by another column, I can simply add a level and then repeat this process.

So once we've sorted by last name A to Z, I might then want to sort by the city on the cell values,

A to Z at another level.

And this time let's sort by the town on the cell values.

But this time let's do Z to I and I could carry on going in the same way.

If I want to copy a level I can.

I have a copy level button above and that's just going to give me another one just here.

If I want to delete a sword, I can select it and click Delete Level, and I can also reorder my source.

So if I decide that I want to sort by the town, first of all, I can select it and move it up to the

top of the list.

Now I don't, so let's put that back down again.

And then we have an options button in here as well, which allows us to specify a fast sort is case

sensitive, and we can also choose the orientation of our data.

So when I have my data organized in this way, so vertically with my column headings in the columns,

I want to sort top to bottom.

But if I had my data organized horizontally, I could choose to saw from left to right.

So don't forget about that options button.

So let's click on OK and see how that affects our dataset.

Well, there we go.

So the first thought I did was to sort by last name A to Z, and I can see that, yes, that looks to

be correct.

I'm then sorting by City A to Z.

So this becomes more obvious when we go down to a last name where we have two entries.

So here we have two entries where the last name begins with C and these two should be in alphabetical

order.

So East Sussex and then London, the same here for Hernandez because we have two of them.

These are in a to z order.

And then finally, we sorted by Town Z to A.

So our sort appears to be working correctly.

Now you're not just limited to performing multiple sorts using the cell value.

For example, if I had some rows in my data that had a background fill, so I'm just going to go through

and let's just fill these, let's fill them with a bright yellow.

Is going to do a few rows just here like that.

What I could do if I wanted to bring all of the highlighted rose to the top is I can sort by the cell

color.

So once again, let's right click this time and take a look at a different way of getting to us sort

options.

I can right click go to salt and custom salt.

I want I'm going to do is just delete out the current salts that we have applied.

So now I can add a level I can choose to salt by, and let's go for last name again.

But this time, instead of sorting on the cell values, I'm going to salt by the cell color.

And then I get an additional box just here called order.

And that's going to pick up all of the background.

So colors that I have in this dataset, so I only have one.

So I want to soar by the yellow background fill.

I want to place those items at the top of the list.

Let's click on OK, and it pulls those to the top.

Now I'm going to undo a few times just to remove that yellow background shading because the same works,

if the font is a different color as well.

So let's just make these a few of these red, for example, those ones just there.

And this time we're going to use the home ribbon in the editing group, sort and filter and custom salts.

Once again, I'm going to delete out all of these current salt levels at a new level.

I'm going to salt by the last name, but this time we're going to salt by font color.

Now, when I click the dropdown here, you can see I have two options because my heading rows contain

a light blue colored font.

So I want to sort by the red font entries.

I want them on top.

Click on OK.

And those are pulled to the top of the list.

And in the same way, if I had conditional formatting icons in any of these cells on the conditional

formatting icon and bring those entries to the top of the list as well.

So pretty easy to sort.

Have a little play around with that and see how you get on.