In our previous article, we looked at using the Advanced Filter function of Excel to copy certain records from one database to another using simple criteria. However, the Advanced Filter really comes into it’s own where you have multiple AND/OR criteria that you want to use. If you didn’t see part 1, read it here.
Creating the Advanced Filter Criteria
With the advanced filter, we can set multiple criteria to filter our data. This could be multiple AND criteria (data that meets ‘this’ criteria AND also ‘that’ criteria) or it could be multiple OR criteria (data this has’this’ value OR has ‘that’ value). The Excel Advanced Filter determines whether your are looking for the AND operator or the OR operator by the way you criteria range is formatted.
Recap on Formatting your Criteria Range
We covered this briefly before, but just to recap, the format of your criteria range is very important, it should:
- Be placed directly above your data, on the same sheet (you might need to insert a few blanks rows above your data to achieve this)
- Have headings that match exactly the headings in your database
- Have a blank row between the criteria range and the headings in your data
- Have no blank rows in the data
- Have a blank row at the bottom of your data
Setting your statements
Using AND criteria
Lets take our RSVP database example. Here we want to know which attendees responded ‘Yes’ AND responded before the RSVP deadline. To do this, we set up our criteria all on the same row like this:
Using OR criteria
Alternatively, we might want to know which attendees responded ‘Yes’ OR those who responded before the RSVP deadline. Therefore, we need to put the criteria on different rows as below:
You can create advanced filter criteria this way by using multiple factors and combining AND/OR statements.
For example here we are looking for:
Those that responded ‘Yes’ AND haven’t yet received a confirmation letter
Those who responded after the RSVP deadline of 7/5/12 AND responded ‘Yes’
Sometimes you don’t want to search for just the whole value of a cell, but perhaps for for data that meets part of your criteria. In this case, we can use a wildcard.
You can use the asterisk (*) wildcard to search for text within a cell that has any number of characters in the position of the asterisk. For example; if you want to search for attendees whose surname starts with ‘S’ your criteria would be ‘S*’.
Alternatively you can use the question mark (?) wildcard to search for one character in that position. For example; the criteria ‘Sm?th’ would bring up results for ‘Smith’ and ‘Smyth’, this can be a very useful tool if you are unsure of the spelling of a word, or to account for potential typos in your data.