And / Or filter with AutoFilter

In the previous lesson we had already seen how to enter a condition for our filter “Begins with …. ”

As an example,

I filtered on the representatives, whose names began with the letter d.

I can fill in a second condition below that.
For example, “does not end” with the letter e.

If I choose “And” both conditions must be met.

If I choose “Or” one of two conditions must be met.

Click the downward pointing arrow next to the various fields to set the conditions.
Click OK when all settings are complete.

Creating a Criteria range

Another way to link conditions to a filter, is by creating a criteria range.
To create a criteria range, first we add a number of empty rows at the top of our worksheet.
The criteria range itself will need at least three rows. Plus one blank row between the criteria range and the data.

Select the column titles from the range, and paste it on the first blank row.
The quickest way to copy and paste, I think, still with the Ctrl + C and Ctrl + V on your keyboard.

In the criteria range, we give a condition on which we want to filter, under the title column.
In this example, I would like to see data from my representative ‘De Cock’, so I type his name under the column title.

Select a cell in the table, select it in the “Advanced Filter” dialog box.
Click the “Advanced” button in the “Data” tab in the “Ribbon”.

This opens the “Advanced Filter” dialog box.
In the top two boxes, we make the choice whether we want to filter results in the list, or we want to copy the results to another location. I go for “Filter the List in-place”.

The first box is already filled in because we have selected a cell in the Range.

In the second Criteria range box, click the Collapse button and select the criteria range in the worksheet. This is cell A1 to cell J2.
Click OK.
Result:

Multiple conditions in a criteria range

We can use multiple conditions in a criteria range too.
For AND condition we put the criteria in the same row.
For example: In the City field I want to filter on “Limburg”, and representative field, I would like names that start with the letter d followed by a asterisk (*).
The asterisk stands for any character.
Click OK. Result:

To insert an “OR condition” we put the criteria in different rows.
Result:

The names of our representatives must begin with va or pe.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats