Daily Dose of Excel

The Advance Filter feature (Data > Filter > Advanced Filter) can be used to filter a list based on the criteria range you specify. For instance, to find the week that Team3 hosts Team4 on a schedule, your worksheet might look like this:

The result being one time in Week 1.

The criteria you specify contains a header row with headers that match the column headings of the range you want to filter. I’m not filtering on the Week column, so I didn’t include that in my criteria range. Because Team3 and Team4 are on the same line in the criteria range, they are treated as an AND comparison. It says “Return all rows where Team3 is in the Home column AND Team4 is in the Away column”.

When the criteria are on different lines, it’s an OR comparison. If you want to print out Team3’s entire schedule, you would structure it like this:

It’s says “Return all rows where Team3 is the Home team OR Team3 is the Away team”. It returns every game that Team3 plays.

To be accurate, it really says “Return all rows where (Team3 is Home AND AnyTeam is Away) OR (Team3 is Away AND AnyTeam is Home)”. Leaving a blank under the criteria indicates that all records should be returned for that column as it relates to that particular criteria row.

In the first example, Team3 was Home and Team4 was Away. In this example, we’ll filter to see any time that Team3 plays Team4, regardless of whose Home. It also requires an OR criterion.

It’s a good practice to have the criteria range above the range to be filtered. If it’s adjacent, the criteria range might be made invisible when certain rows are hidden. You could also put it below the range, but you’ll have to move it if your list grows and it changes position on the screen when you filter.