Meeting unusual filtering needs via Advanced Filter

- Excel's filtering capability, available on the Data tab by way of the Filter button, is a powerful tool, generally easy to use. There are some features and some aspects of it, though, that are not satisfactory, and there are certain things we just cannot do. I'm looking at a list here. It's about 900 rows. I'd like to isolate the data in a certain way. I'm trying to figure out which day of the week we have more sales. I want to narrow that down possibly by salesperson too, and come up with some kind of rating based on time of day even, day of the week.

A lot of things we might want to get to here. The drop arrows in a list do take into account the content of the data. For example, the drop arrow for Date provides us with Date Fiilters. On a numeric cell, like column H, we see Number Filters. And on a text field like column B, we see Text Filters. In each case, bringing up lots of useful and relevant choices here. Data Filters for example, gives us a ton of choices here. And as we move farther down the list, it even provides them by month or quarter.

So quite a few choices there. But notice, nothing about day of the week. So that's not available there. And when it comes to time, we might want to isolate these by hour. Now, ultimately, we will be able to do this. We'll show you how, but let's approach some other things that we cannot get to quite so easily with the filter. I'm interested in creating a list here that shows either any row here where the items are greater than or equal to 10.

I also want to see all those entries where the amount is greater than 10,000. Now, I'm not saying, when both of those are true. When either are true. In other words, this is 10 or more, but that's not. I want to see this. And I might have some other entries down here. I don't see any at the moment, but I could have some others down here where the entry's over 10,000, and maybe the actual items being sold are under 10. So we can have those kinds of situations as well too. In fact, we could even make one up.

The point is, we will have those from time to time, and we want to see all situations where either possibility occurs. Either the items are 10 or more, or the amount is 10,000 or more. Cannot do that with a standard filter. And we cannot isolate the data by day of the week either. We can't isolate the time in certain ways. So, the Advanced filter, just to the right of Filter, is recommended, but it does involve doing some things that are not at all indicated by the choice here.

Now, here's what's recommended. If you want to use some of the features of Advanced Filter, put in about six or seven rows above the data. Now, we want to experiment with this a little bit. Possibly we could be doing this to the right of the data. I'll explain why that's not a great choice, but let's put six rows above the data. Right-click and Insert. Copy these titles into row one. I'm just going to do a simple control-drag here, put them up here. We don't need the filter to be active down below, we can turn that off.

Now, what I'm about to do is certainly not obvious, but let's talk about it just a bit. Up here, we have potentially what's going to be called a criteria range. Now that's a term seemingly unrelated to anything you might have done in Excel. But here's one example of it, a simple example. I would like to see, for example, Dishwashers, I'm going to copy this up into B2. In all cases where the items are more than 10. But instead of putting a 10 here, I'm going to type ">=10".

Now, if I use an Advanced Filter for what I'm about to show you here, nothing really has been gained. I could do this with a regular filter. For example, if I click in this list, and activate Filter, and I choose Product, and then unselect all and choose Dishwashers only, for the moment I'm seeing all Dishwashers. If I click the arrow for Items and choose a Number Filter that says Greater Than, or possibly Greater Than Or Equal To, either one. How about greater than or equal to 10.

Click Ok. There are the entries. We see the list. Lower left corner says there are 28 of them. I could use the Advanced Filter for this, but I'd get the same answer and nothing really will have been changed. But let's show how it works with this simple example here. Although, we don't really need it here, let's show it anyway. So I'm clicking within the data here. I'm going to go to Advanced Filter. Now Excel makes a guess, and a good guess, and usually gets it right as to your list range in this dialogue box here.

We could copy the results to a different location. I don't want to do that. But here's that term, Criteria range. And I'm going to scroll upward and highlight these cells here. The empty cells in C2, D2, E2, F2, mean nothing, they're ignored. So I don't care about that. Click Ok. Same answer as before, lower left corner tells us there are 28. That works but it wasn't really necessary. So, let's show how this could be different though.

Earlier I suggested we might want to get a list where the items are greater than or equal to 10, or the amount is greater than or equal to 10,000. So I'm going to type right here, ">=10000." If I make this be the criteria range, it too will be no different than using the standard filter. In other words, when this is true and when this is true. So when we set up a criteria range, and it could involve multiple fields, if we were to use this right now, we would only see the Dishwasher entries greater than or equal to 10 and the amount being greater than or equal to 10,000.

So let's run that. Again, I'm pointing out what we're doing here could be done with the regular filter. And we'll use Advanced this time. The criteria range will be these cells right here. Click Ok. And it looks like there's only one of them for Dishwashers. Now as I press Control + Z to go backward, we see others here, but because these are in the same row, we mean "and." And so as we look up and down here on the amounts here, there's the one, and that's the only one.

So all of these are the Dishwashers here where the items are greater than 10, but if we include our criteria range to pick up the additional entry in column H out here, then we only have one answer. Once again, Advanced Filter, with the criteria range here. Okay. Only one. Now, here's where things change. I'm going to put this in a separate row. Now, if you are familiar with Access, some of you might be. If you're familiar with the Access query grid, maybe this is reminiscent of it.

If this becomes the criteria range, or possibly if this, if we only wanted to focus on Dishwashers. Now, this criteria range means the following: show me all rows where the product is Dishwasher and the items are greater than or equal to 10. Also show me rows where the amount is greater than or equal to 10,000. Now, if we just want to focus not on the product, but on the items and the amount, what if we make this the criteria range? I'm going to click back within the data here and go to Advanced Filter, change the criteria range to be those cells.

Click Ok. So we see our entries. Every entry that we see here either has the items 10 or more, and that looks like most of them, or in some cases it's the amount that's greater than 10,000. But wherever either occurs, you see what's happening there. And we can sort this list if we want to. I'm going to click here and simply press AZ to get the list this way. So all these entries are here because the amount is 10,000 or more.

And everything below that is there possibly for two reasons, for at least one reason though. We see what's happening. All these here are 10 or above, and some of them are above 10,000, some are not. So the idea here with the Advanced Filter is we can use these, the "or" capability. We're saying if this is true or if this is true. We can't do that with a standard filter. Something else we can't to that I eluded to earlier, we'd like to isolate all the data that's Tuesday only or Wednesday only, whatever day of the week it might be.

And what we do here is quite strange. I'm going to type, first of all, "=weekday." That part's not strange. This is a function that allows us to figure out day of the week, but we refer to the first cell in the list that has a date, and that happens to be E8. That's right under the heading. Now, if we're looking for Tuesdays we'll put in a three, =3. That will either pop up, for the moment, true or false, which simply means that April 12th, 2014 was not Tuesday, but that's how we put this in.

Now we're about to make this be the criteria range. And when we do this, surprise, this will not work. Advanced, Criteria range, this only, we click Ok, doesn't work. Now here's what's really strange. I'm going to erase that cell. Criteria range is still in the same location. Click back in the list, which for the moment only has the heading, run the Advanced Filter again, same criteria range, click Ok. These are all the Tuesday sales over this two year period.

You can test one of these by simply, outside here for the moment, "=weekday," pick any one of them here. It's going to be a Tuesday, which is a three. The weekday function gives us answers from one through seven, one is Sunday, two is Monday, etcetera. So that's very unusual, but that shows us all of our Tuesdays. We have no other way to do that, except by way of this Advanced Filter. Now, with time we have some other options. Suppose we only wanted to show that sales between three and four p.m. We can do this with the standard filter.

Filter, there are the filter arrows, Number Filters, Between, greater than or equal to three p.m. Probably the best way to do this would put "15:00," less than or equal to, how about less than. We want it to be lass than four o'clock. That would be 1600 hours, "16:00." So we see the answers that are popping up here. All the sales made between three and four, inclusive.

68 of them. So that we don't need any Advanced Filter. Now, sounding a little contrived maybe, but what if we only wanted to view the sales made in the last five minutes of the hour? Maybe from time to time you have a special kind of promotion you say, if you make a sale in the last five minutes of the hour, you get a special bonus here. So how would we do that? Similar to what we did over here, but using a different function. We're going to use the function called Minute, "=minute." We're looking for the minute of the entry for the first cell where there is a time entry.

That's going to be F8. We don't see that on the screen right now. F8, ">=55," in other words we're looking for all sales in the last five minutes of the hour. That means 55, six, seven, eight, nine, for all hours, we don't care what. And here too, if we want this to work, we erase the label above it. One more time. Advanced Filter. The criteria range now is right here. Remember, we're trying to find out all sales that occurred in the last five minutes of the hour Click Ok and there they are.

Then you see all the entires here that are all within the last five minutes. Admittedly unusual, and a bit obscure possibly in that last example, but from time to time, you'll need the Advanced Filter, as we saw it here with Minute. We also saw it over here with Weekday. That's probably a more common use of it. But also in other situations when we're trying to use combinations of entries here using different rows. Remember, think of the word "or," as we use the example over here. So, Advanced Filter, found in the Sort & Filter group on the Data tab.