All right, we are now into week four of the course. I going to show you a lot of cool tricks that you can do with managing large sets of data, that's one of the fortes of Excel. And so in this screencast I'm going to show you how to apply a filter. The first example is in a file called Roster. I've got a bunch of hypothetical student names here, their hypothetical student ID and their hypothetical section. And maybe we wanted to just separate this into the various sections, so we want to filter by section. We don't necessarily want to sort, which I'll show you in a subsequent screencast. We just kind of want to pluck out section 101, 102 and so on. So I can go up here, if I am on the Home tab. You can either go over here to sort and filter and apply the filter there. Or you can go up to the Data tab, and you've got filter here. As long as my active cell is somewhere within that contiguous block of cells, I can do Filter. This will actually apply filter to all of them. These filters are denoted by these little drop-down lists at the top. For example, I can go up here to the Section filter and I can click on that. And maybe I just wanted to select the section 101 students. Then what I can do is I can deselect and then I can select the 101, and then I can click OK. And you see that it has filtered over here on the left, you see that it's hiding a lot of the rows. The nice thing about this is maybe I want to prepare a section spreadsheet file for each of the TAs for those sections. I can just do CTRL+Shift+8, and I can do CTRL+Copy, and I can insert a new workbook and then I can just do CTRL+V. And now I can just save this and then I can email that to my section 101 TA. We can filter by section. Maybe we wanted to show section 101 and104, you can select those, click OK. And then if you want to remove all filters, you go up here and you can just do Clear Filters From "Section". You could also filter by student number and name if you would like. So that's a simple filter and you can always go back into the Data tab and you can deselect filter to get it back to how you had it before. I got another example here, it's called Batches in this file. Now, we have batch ID, production date and the ship date maybe for whatever reason. Maybe our boss wanted us to put together a list of all the batch IDs between two different production dates. So I can go up here to data, I'm going to add a filter. And now you can drop down this filter, and here you could select the dates. But maybe I want to do a more specific filter. So if you click on Date you hover over Date Filters, it brings up all these different options. We're just going to do between, I'm just gonan show you one example. So we have a Production Date is after or equal to. So maybe we wanted to do between May 1st, so I can kind of use this calendar thing, May 1st of 2019. Click on that so between May 1st and December 31st. So I can then click on OK and it's going to filter for batch IDs between those two production dates. And then if you wanted to, you could just copy all these, put them in a separate spreadsheet, save that file and send it off to your boss. We can go ahead and remove that filter. Just clear filter there to get us back to our original data. Maybe we wanted to just filter out the batch IDs that started with a C or an F so I can go up here to the Batch ID filter. I'm going to do text filters begins with. So maybe we wanted to screen for those that begin with C or maybe we can scroll down here to begins with an F. So maybe for whatever reason those batches are important and we want to know more information about them. We can filter those and then we'd have more information on the production date and the ship date. So maybe you wanan figure out who received those batches, maybe they're bad batches and you need to recal them. So I'm going to go ahead and clear those filters. There's also one for maybe it ends. So ends with maybe ends with an N is important for whatever reason and so we filter out the batch IDs that end with an N. Let's go ahead and remove that filter, Clear filters. Maybe it's the end of the year and we wanted to reward those people who were in charge of shipping within five days of the production date. So we want to reward those types of people. I could put in a column here for Ship Time and we can subtract from the Ship Date the Production Date. Make sure that this is general or number, and then I can double-click thisdown. And I'm going to add a filter, so we haven't added a filter here. So you actually have to go back to data, deselect the filter and then reselect it and then add the filter. By the way, if you just want to add a filter to a single column, we could do CTRL+Shift+Down and add the filter there. Either way, it doesn't matter, or you can add the filter to all the columns of your data. So maybe we want to filter ship time, you can either select here, 2, 3, 4, so five days or fewer, those are the ones that satisfy that constraint. Or you can always do number filters and you can do less than or equal to and you could put in a 5 here. These guys, you might want to add a bonus. And so for these ones you can just do drop down that yes. And then you can always clear all the filters by deselecting there, and that's a easy way to filter by that criteria. So, hopefully this screencast gives you a better idea of how to use filters when managing large sets of data.
