Meeting unusual filtering needs via Advanced Filter

- [Instructor] Excel users who work with lists are not doubt familiar with one of Excel's most important data management tools found on the Data tab. It's called Filter. And if you were working with this list, you might use the filter to show just the products refrigerators and washers, or possibly data out of a certain region, or maybe just focused on a certain person, or even combinations of those. But there's certain kinds of constructions you can't get to with a standard filter. For example, suppose we wanted to find all rows where the product is dryers or the customer is Home USA. In other words, we'd like to see both of these rows here. And anytime we see either one of those or both, we want to see that row. Now admittedly, that's an unusual kind of construction, but we cannot do that with a standard filter. And something else we can't do either, we can't look at the data here by day of the week. So if you were to use the filter here, you might be inclined to say, well surely there's a way to do that. We'll click the drop arrow here for date, go to date filters and lots of choices here, but nothing here about day of the week. So we can't get to that either. So what we typically do and if I click Advanced Filter, you'll see something pop up that you probably have not seen before, the phrase Criteria range. That's some that needs to be defined. I'm going to set this up, and then along the way define what that means. If you are going to use the Advanced Filter, you'll need to set up an area, typically done above the data called a criteria range. I'll turn off the standard filter here, and insert some new rows above the data. What I'm doing here is not a requirement but a suggestion. And just copy these, they're going to come in handy as we define a criteria range. I'll simply drag this with the Control key, put it up top. And I'm going to copy the word dryers. I'll just do another Control drag up here, put it there. And then Home USA. And I'm about to define these cells as I run Advanced Filter as a criteria range. However, if I go with this construction, what this means is find all rows where the product is dryers and the customer is Home USA. If I put this in a separate row, and this becomes the criteria range, what Advanced Filter will read this as will be find all rows where the product is dryers, or the customer is Home USA. And that includes situations where both occur. And again, you wouldn't necessarily know that until you actually run the Advanced Filter, or you've been told ahead of time how this works. So clicking within the data, Advanced, always give this a quick look, Excel figures out the extent of the data. Criteria range, click in this panel. And in this example, this is the criteria range. The region cells are blank, they simply get ignored. We click OK and we're expecting to see any row where the word dryers appears, or the phrase Home USA appears and there it is. We see dryers over here but not Home USA. Home USA here but not dryers, different ones, and of course sometimes both. 343 of the records out of this roughly 900 list. That's one example of how to use a criteria range that you cannot get to with the regular filter. The reason we put this above the data is had we put it off to the right for example, in the filtering process a number of the rows are hidden and we might not be able to review what our criteria range looks like if we have this off to the right. Now, the other issue that I mentioned, how about day of the week? What I'm going to do here first of all is clear the filter. Just to the right of Filter is Clear. That means bring back all the data. The weekday function tells us which day of the week we're looking at. If it's one it's Sunday, two, Monday and so on. But to get to the weekday, let's say I'm interested in seeing only the sales on Tuesdays. Equal weekday, if you're familiar with this function you know that one is Sunday, two is Monday et cetera. But we refer to the very first cell in the list that has a weekday. We want to check to see if that's equal to three. And we are about to see the word true or false, and that simply means that for the moment cell E3 either is a Tuesday or it isn't. So in this case it isn't, that's not a Tuesday. But that's the criterion that we use here. And when we run the Advanced Filter, it's going to be a bit of a surprise the first time, we do have to change the Criteria range first of all. That's expected. But when we run the filter like this, we get nothing. And I wish I could give you a good reason why we need do to this but this cell E1 needs to be empty, so I click it and press Delete. And we'll come back to the data. For the moment we see only the header row. We use Advanced Filter, same criteria range, click OK and there's the data. And that's a Tuesday, that's a Tuesday. Just to be sure, we might write that weekday function out here to check out one or more of these. Weekday of this particular cell right here is three, that means it's a Tuesday. So we found our Tuesday data. And we could not of done that with a standard filter. And it might be a good idea in the long run maybe to have a new column in the original data showing day of the week. But we see how we can use this Advanced Filter. And we could not of used the standard filter to get these results.