Advanced filtering OR / AND:

Last week, I was asked by Kristien how to use the “Advanced filter” properly.
This was explained extensively in the course but, I will explain how to do it once again.

We have a table with a column for employees, a column for the department, a column for the province, a column for the municipality, a column forhe date of employment and column for their wages.

For advanced filtering, we add a number of empty rows (1) and we copy the column headings to the top additional row (2).

Then, place the cursor in a cell, of the table that we are going to filter.
Then, enter the first condition in the additional cells, in the appropriate column.
I want to filter based on a service period that is later or equal to 01/12/2007, so I type > = 01/12/2007 or, in other words, “greater than or equal to 01/12/2007”.
The second condition based on which I want to filter is when the wage is more than 40,000?
So, I enter this in the same column but, below the row with the previous condition, > 40000.

When I place this condition under the previous filter, obviously, it is to the right column of the row with the previous condition. Only, Excel uses OR to filter based on the first condition or based on the next condition.
Let us first see what we get when we use OR in the filter.

Once the conditions are typed, click the “Advanced” button in the “Ribbon”.
You can find this under the “Data” tab.
In the dialog box that appears, the range is already filled.
This for the simple reason that we have already placed our cursor in a cell in the table.

Then select the “Criteria range” text box and, click and drag the different criteria with their titles. The latter is certainly not forgotten.
Click the OK button when you’re done.

Our list is now filtered.
Only the records with a later date than 01/12/2007 OR at a wage higher than 40,000, are displayed.
You can see this at the bottom of the window, in the status bar.
19 of the 21 records meet either of these conditions.

When you enter both conditions on the same row, then both conditions are met.
It is read as the first condition AND the second condition.
The difference is clear in the image below.

The question I am sometimes asked is, “can these conditions be placed to the left in the table”.
Yes you can do so, but, when you have to filter rows that do not fulfill the conditions, they are hidden.
As you can see in the image below, the rows, 11,12,13,14 and 15 are hidden.
So, if you had, by chance, entered the conditions in one of these rows, you would not be able to see it now.

• Suppose you want to add more conditions. There’s no problem, just type in the desired column, whether it is in the same row or not.
  If you want to remove the filter, click the “Delete” button under the “Data” tab in the “Ribbon”.
• FYI: Swotster.com thinking of you, so you also please remember swotster.com

Featured Courses

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