“Advanced Filter”

In Excel, we can “Filter” out certain detailed criteria.

For an extensive filtering we need to add at least 3 blank rows above the range, which we use as a “Criteria Range”.

A “Criterion Range” is a duplicate, or a part thereof, of the column headings in our “List”, which is separated from the rest of the “List” from our work surface.

It is important that you have at least one blank row between the “Criteria Range” and the “List”.

The “Criteria Range” and the column titles in our “Range” contain criteria which we apply on them.

We can apply as many criterion as we want, but can only use one at a time to “Filter” data.

I love this feature, so I have added five blank rows up there.

I have copied the titles “City”, “Seller” and “january” to the top row, so I can “Filter” on this and only titles.

Before we apply the “Filters” we have to enter the conditions and necessary criteria under the appropriate title.

We can also use “Comparison operators” and “Wildcards” in our “Filters”.

When we are done, we give a criteria in the “Criteria Range”.

We place our cursor in a cell of our “List” where we want to apply the “Filter”.

We choose “Data” from the menu bar, select “Filter” and choose “Advanced Filter”

This opens the “Advanced Filter” dialog box:

In the “Action” section of the dialog we choose “Filter the list, in-place”.

The “List range” box shows the “Range” to be filtered in the “List”.

All we have to do is enter the criteria in the “Criteria range”.

Click the “Range” button .on the far right corner in the “Criteria range:” section.

And select the entire ‘Range”:

We again click on the button beside the “Criteria range:” box .

And click OK.

Result:

I know you’re thinking that we can to do this with “AutoFilter”, and that’s true but, this is just a simple example to start with.

To remove the “Filter” we click “Data” in the menu bar, select “Filter” and choose “Show All”.

The use of “And” and “Or” conditions.

We use the “And” and “Or” conditions when a record has to meet one or more out of several criteria.

We give “And” condition in the same row as criteria row.

We give “Or” condition in the following criteria row.

The difference is clear.

With the “And” condition, the record meets the first criteria (Seller must have DeSmet).

and the second criteria (January volumes should be higher than 9000).

With the “Or” condition the record can meet only one of the two criteria.

Another example:

For the first filter, we give the criteria as, the seller is DeSmet, and January volumes should be higher than 9000.

And for the second filter we give the criteria as, the seller is Vertommen or that revenue for January is more than 15000.

The use of “Wildcards” in criteria

Just as in the “Data form” and in the “Auto Filter”, we can also use “Wildcards”.

A ‘?’ stands for a single character.
A ‘*’ stands for more than one character.
To use a “Wildcard”, we determine under what “Field” and what criteria row, we want to use the criteria.

Example:

As a result we get all vendors whose name starts with “De”.

Copy filtered data to a new location.

We can copy the results of our “Filter” to a new location on our “Worksheet”.

This can be useful when we have to apply the same filter repeatedly..

As an example I created a “Filter” that showa our sales in January in Paris that are greater than 8000 and less than 9000.

Because we work with the “And” condition filtering, both are entered on the same row, so I use the title January in two columns of my “Criteria range”.  Once for greater than 8000, and once for less than 9000.

We click on “Data” from the menu bar, select “Filter” and choose “Advanced Filter”.

This opens the “Advanced Filter” dialog box:

In the dialog we select “Copy to another location”.
Then enter the field in the “Copy to” box, to copy the result.

You have the option “Unique records only” checkbox, so you are sure you do not copy duplicates.

Database functions

We can use the database functions to enhance criteria applied to the data from our “List”.

Database functions are similar to our “normal” functions such as AVERAGE (MEAN), SUM (SUM), COUNT (NUMBER) etc., but are used to show values that meet our criteria.
If you are working with an English version of Excel you place the function a D (for example DSUM, DAVERAGE)

As an example we have a list of all sales in 2003.

We already have our empty rows and our titles added to the “Criteria range”.

Suppose that we have to know the total turnover of sales from 1 May 2003 to 31 May 2003.

First, we give our criteria, so we type the following in the “Criteria range”:

Then we select the cell where we want to have the results.

Then we give our function.

We do this by clicking on the “Insert Function” button  , or choose “Insert” in the menubar and select “Function”.

This opens the “Insert Function” dialog:

In the “Or select category:” select “Database”.

In the “Select a function” we choose DSUM.

Click OK.

In the “Function Arguments” we see that each function requires three arguments:

“Database”: What is the range of cells in our list?.
“Field”: Which is the title of the field that we want to exercise our function?
“Criteria”: indicates the test range.

When all are entered, we click on OK.

Result, our sales in May 2003 were 106 270 Euro.

Before you start thinking that I am deserving guy, I must disappoint you.
I am merely a disabled person who tries to keep himself busy.

Featured Courses

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