Excel Advanced Filter

Excel Advanced Filter allows  to apply complex filtration criteria. It has all capabilities of AutoFilter plus some enhanced mechanism to filter data. For example if you want to filter only those products which have “juice” in their name or if you have a criteria on the basis of result of a function, this can be achieved by Advanced Filter.

Following are some examples of Advanced Filter

Example 1

Criteria: Find all products which have “juice” in their name, the aim is to get sale of all juices on a given date.

We shall begin with data shown in figure below. Advanced filter is different from Auto Filter. In Advanced Filter, criteria are given in cells above the actual data. As you can see in picture below our data is in range from A6 to E15 but we have similar headings above the data. Doing this we have created a criteria range available from A2 to E4. Remember we must keep an empty row between our data and criteria.

Now in the cell C3 type following

=”=*Juice*”

And press Enter key. This evaluate into =*juice*

Now click on Data menu, point to Filter and click on Advanced Filter, the Advanced Filter dialog box appears.

In “List Range” enter range from A6 to E15 and in  “Criteria range” enter range from B2 to B3 as shown in Figure B.

Now click Ok button on dialog box. You will see rows containing only juice related products as shown in figure below

.

Now click on Data menu, point to Filter and click on Show All. This will reset all the data .

Now in cell A3 type following

=”=02-jan-2007’

And press Enter key. This evaluate into =02-jan-2007. Now we have two criteria, as a result of this, rows satisfying both conditions will be shown. Now Apply Advanced filter again from Data menu and enter ranges as shown in figure below.

Now our results on contains “Juice Products” sales of 02-jan-2007.

As our both condition are on same row, it means AND between both conditions. If conditions are not on the same row for example if =02-jan-2007 criteria is typed in cell A4, this implies OR between condition. In this case all rows satisfying either of two conditions will be shown in filtered data.

Example 2

Criteria: Filter out row that has minimum sale.

Click on Data menu, point to Filter and click on Show All. This will reset all the data.

In Cell F3 type a formula as shown in figure below

Now apply Advanced Filter from Data menu and enter ranges as shown in figure below

Now click OK button, the final result is the row containing minimum sale figure as shown in figure blow.

MS Excel | RSS 2.0 | Respond | Trackback |