SortandFilter

Speaker: In this video we're going to learn how to do sorting and filtering. Let's start with sorting. In our spreadsheet here we have a list with a header at the top.  It always helps to have a header of labels at the top for sorting. To sort, we simply select all of the data in the list that we want to sort. I'm going to select the upper left hand cell in the list. Control-shift-right, control-shift-down, we have the whole thing selected.  

I can go ahead and scroll up here. Then I go to the Home tab and off to the right you have Sort & Filter. If we click Sort A–Z, it will sort in ascending order everything by the first column selected. Z–A will go in descending order. If we know that we want to sort by the first column then we can go ahead and use one of those two. But if we want to sort by another column, or by multiple columns, we click Custom Sort.  

So in this case we have sort by, and then a drop down list. Let's say that we want to sort by the part classification over here. So we just click class, and then it says the sort order, A–Z or Z–A. So whether we want it to be ascending or descending. We'll go ahead and keep it in ascending order. Click OK. And notice that it puts all of the items here that are A items first, and then down below we eventually get to the B items and then the C items at the very bottom.  

So let's suppose that we want to do a little bit different sort. I'm going to select it all again. Scroll up here. Go to Sort and Filter. Custom sort. Notice that it remembers the sort settings that we had previously. We can add another level to sort by. Notice that when we sorted it by class it put all the A's at the top. But the rest of the information was in no particular order. Actually it was in the order that it was already in. We can add another level and then sort by a second category.

So in this case let's suppose that we want to sort by price. And we're going to make that largest to smallest. So what this is going to do now is it's going to take our list and it's going to sort first by class, so it's going to put all of the A's at the top. But then within the A class it's going to sort those parts by price from highest to lowest.  So we go ahead and click OK. And notice that it puts all of the A's at the top, but the most expensive parts are at the top of the A list. I'm going to scroll down and see if we can find some of the B parts.

There's where the B parts start right there. So you can see here. Notice that the prices went down and then we eventually got to the first B part, and then it's the most expensive one and it goes down from there. So let's do this one more time.  I'm going to go ahead and do another sort here. Custom sort. We could go ahead and sort by class. And instead of price we could change it to say, lead time. And we could go from smallest to largest. Click OK. Now notice that it sorts it by class, and then by lead time in ascending order.

So that's how the sorting works. I'm going to show you one more thing in it though. The one thing to note here is that when you do a custom sort, there's this check box here that says my data has headers. And what that means is that there is a header row here at the top. And that allows it to know what options should exist in these drop down lists. If I unclick this, now it will include the top row in the sorting and it only does it by column. So generally it makes sense to have a header row, and then to checkmark that your data has a header row in it.

A couple last things to mention. We can add more than two levels. We can add a third level and a fourth level. Or we can select them and delete levels as well. Once we have levels specified we can change the order of them by selecting it and then clicking the up or the down arrows to move the particular sort criteria where we want it to be. So in this case where we moved lead time up, it's going to sort first by lead time and then by class.

If I click OK notice that it sorts all of the one lead times first, and then once I finished out with the one lead times it goes to the two lead times, and the A, B, and C class are inside the different lead time categories instead of the other way around. So I'm going to go ahead and resort this list. Custom sort. I'm just going to sort it by part number. And then I'll go ahead and sort it by price. There we go. Now click OK. Now we've got the list of parts by part number and then by price.

The second thing that I wanted to show you in this video is data filters. So to turn on a filter we go onto the Home tab again, and off to the upper right hand corner here under Sort and Filter we click the down arrow. And we click Filter. And when we do this it'll recognize all of the data on the sheet and it'll put a filter in place at the top. A filter is a series of drop down lists that allows us to manage a table using those drop down arrows.

So for instance, it put a drop down here with part number. I can click that drop down list and it will allow me to quickly sort in ascending or descending order by part number, since that's where this drop down list is. So if I click Sort Z–A it'll reorder my list. Because it's sorted right now in descending order, there's a little arrow that shows here. I can click that again and go Sort A–Z and it's showing that it's sorted in ascending order this time.

Or instead of sorting, if I want, I can go down here and filter out certain entries. So right now by default everything is selected. If I uncheck the select all box nothing will be selected, and then I can scroll down and select the items that I want to be selected. I'm going to select this one here and let's say this one here. And maybe a couple more. There. When I click OK notice that it filters out everything in the list, except for those four items that I had check marked.

I can do further filtering by say lead time, where I come over here, uncheck select all, and I'm going to only do, say, 12. Click OK. And it only shows the particular items in the list that have the lead time of 12. I can click on those again and click select all to restore them to what they were. And so that's how the filtering works.

Now how could this be useful? Let's suppose that I only want to look at the A class items right now. I could come over to class, click the down arrow, uncheck select all, and just select A. Click OK. And now only the parts that are A items are showing in the list. All of the other ones are filtered out. Now it's important to note that they're not gone. They're just hidden. You'll notice over here with the row, the first item is in row 3. The next item showing is actually in row 10. That means rows 4–9 are actually hidden inside here because they're filtered out.  

If I unfilter it by the class and select all, notice that 4–9 are showing up again and none of those were A items. That's why they were filtered out previously. If I want to turn off the filtering I just come up to Sort and Filter and click filter again. And everything will be back to normal. So that's how the filtering works.