Advance Filter Primer

ADVANCE FILTER PRIMER

We use AdvanceFilter function when we want to filter our data in data area (database) based on more than one column titles. If we want to filter our data in data area (database) based on one column title only, we use AutoFilter.

We can access the AdvanceFilter function by clicking menu 'Data/Filter/AdvanceFilter'.

Before we use this function, we must have data in a proper data area (database). A data area is a rectangular area of adjacent cells boundaried by a row on top, a row at the bottom, a column in the left and a column in the right. A data area is proper and ready for use by the function if it has the following characteristics :

∙ The cells in the first row of the data area must be used as data titles. ∙ Cells used as data titles must not have a blank cell between each other. (You can start data area from any row, not necessarily the first row of the sheet). ∙ Actual data must appear in rows below the data titles row. ∙ There must not be a row which all its cells are blank in each row below the data titles. (You must have at least one cell in a row which is not blank. Other cells can be blank in that row). If there is a row which all its cells are blank, this row is considered as the last row of the data area.

When we want to use AdvanceFilter, we need to identify what columns titles (or data titles) we want to use as basis of filtering. We identify them by writing the relevant data titles in another area (preferably in other sheet). In that other sheet, we write the data titles in a separate cell in a row (any row) and must be adjacent to each other. In cells below each titles, write the item that we want to see as a result of the filtering. (It could be more than one cell filled with item under each title). The item must be one that is actually available in cells below that title in the actual data area.

The area where we write the selected data titles and the selected items below it is called a 'CRITERIA'.

So, a simple Criteria looks like this :

A B C 1 Title A Title B Title C 2 xxxx Yyyy aaaa 3 bbbb kk 4 hhh

If we fill the Criteria that look like this :

A B 1 Title A Title B 2 xxxx Yyyy 3 4

This read we want to see rows where each row has item 'xxx' below a data title A AND has item 'yyyy' below data title B. Only rows having these 2 items will get selected, the rest will be hidden.

If we fill the Criteria like this :

A B 1 Title A Title B 2 xxxx 3 4

This means we want to see rows where each row has item 'xxx' below a data title A AND has any other item under data title B. Only rows having item 'xxx' under title A will get selected (Data under title B can consist anything). The rest will be hidden.

If we fill the Criteria like this :

A B 1 Title A Title B 2 Xxx Yyyy 3 Aa 4

This means we want to see rows where each row has item 'xxx' below a data title A AND has item 'yyyy' below data title B AND ALSO each row which has item 'aa' below a data title A AND has any other item below data title B . Only rows satisfying these conditions will get selected. The rest will be hidden.

If we fill the Criteria like this :

A B 1 Title A Title B 2 xxxx 3 aa Yyyy 4

This means we want to see rows where each row has item 'xxx' below a data title A AND has any item below data title B AND ALSO each row which has item 'aa' below a data title A AND has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden. If we fill the Criteria like this :

A B 1 Title A Title B 2 Xxx 3 aa Yyyy 4

This means we want to see rows where each row has item 'xxx' below a data title B AND has any item below data title A AND ALSO each row which has item 'aa' below a data title A AND has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.

If we fill the Criteria like this :

A B 1 Title A Title B 2 xxxx 3 Yyyy 4

This means we want to see rows where each row has item 'xxx' below a data title A AND has any item below data title B AND ALSO rows which each row which has item 'yyyy' below a data title B AND has any item below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.

By now, you would see that a blank cell in the ‘Criteria’ block represents ANY. A row in the Criteria represents a row in the actual data area.

After you set the Criteria then you can start using the menu 'Data/Filter/AdvanceFilter'.

You can set more than one Criteria in one sheet to serve different filtering.

After the Criteria is ready, activate the sheet which contain the data you want to filter. Click menu 'Data/ Filter/ AdvanceFilter'. You will see a dialog with title 'Advance Filter'.On the right of 'List range' label is your data area automatically identified by Excel. (Check this range. If wrong, click cancel and correct you data setting to follow the rules, and start again). Click on the 'Criteria range' box. Click at the sheet where the criteria that you want to use reside. Select the criteria range. Automatically the 'Criteria range' box will be filled with the address of your selection area (criteria range). Click OK. Your data in the data area will get filtered accordingly (You see only data that you want to see).

Summary

To filter data based on one item in one column, use AutoFilter operation. To filter data based on more than one item from one column, use 'Custom' in the AutoFilter menu. To filter data based on more than one item from more than one column, use AdvanceFilter operation.