Using Advanced Filtering


In many cases, standard filtering does the job just fine. If you run up against its limitations, you need to use advanced filtering. Advanced filtering is much more flexible than standard filtering, but it takes a bit of up-front work to use it. Advanced filtering provides you with the following capabilities.

  • You can specify more complex filtering criteria.

  • You can specify computed filtering criteria.

  • You can extract a copy of the rows that meet the criteria and place them in another location.

You can use advanced filtering with a worksheet database or with a table.

The examples in this section use a real estate listing worksheet database (shown in Figure 9-15), which has 125 records and 10 fields. This database contains a good assortment of data types: values, text strings, logicals, and dates. The database occupies the range A8:H133. (Rows above the table are used for the criteria range.)

image from book
Figure 9-15: This real estate listing database is used to demonstrate advanced filtering.

On the CD 

This workbook, named image from book real estate database.xlsx, is available on the companion CD-ROM.

Setting Up a Criteria Range

Before you can use the advanced filtering feature, you must set up a criteria range, which is a range on a worksheet that conforms to certain requirements. The criteria range holds the information that Excel uses to filter the table. The criteria range must conform to the following specifications:

  • It must consist of at least two rows, and the first row must contain some or all field names from the table. An exception to this is when you use computed criteria. Computed criteria can use an empty header row. (See "Specifying Computed Criteria," later in this chapter.)

  • The other rows of the criteria range must consist of your filtering criteria.

You can put the criteria range anywhere in the worksheet or even in a different worksheet. However, you should avoid putting the criteria range in rows that are occupied by the worksheet database or table. Because Excel may hide some of these rows when filtering, you may find that your criteria range is no longer visible after filtering. Therefore, you should generally place the criteria range above or below the table.

Figure 9-16 shows a criteria range in A1:B2, above the worksheet database that it uses. Notice that the criteria range does not include all the field names from the table. You can include only the field names for fields that you use in the selection criteria.

image from book
Figure 9-16: A criteria range for advanced filtering.

In this example, the criteria range has only one row of criteria. The fields in each row of the criteria range (except for the header row) are joined with an AND operator. Therefore, after applying the advanced filter, the worksheet database shows only the rows in which the Bedrooms field is 3 and the Pool field is TRUE. In other words, it shows only the listings for three-bedroom homes with a pool.

You may find specifying criteria in the criteria range a bit tricky. I discuss this topic in detail later in this chapter, in the section, "Specifying Advanced Filter Criteria."

Applying an Advanced Filter

To perform the advanced filtering:

  1. Ensure that you've set up a criteria range.

  2. Choose Data image from book Sort & Filter image from book Advanced. Excel displays the Advanced Filter dialog box, as shown in Figure 9-17.

    image from book
    Figure 9-17: The Advanced Filter dialog box.

  3. Excel guesses your database range if the active cell is within or adjacent to a block of data, but you can change it if necessary.

  4. Specify the criteria range. If you happen to have a named range with the name Criteria, Excel will insert that range in the Criteria range box-you can also change this range if you like.

  5. To filter the database in place (that is, to hide rows that don't qualify), select the option labeled Filter the List, In-Place. If you select the Copy to Another Location option, you need to specify a range in the Copy To box.

  6. Click OK, and Excel filters the table by the criteria that you specify.

Figure 9-18 shows the worksheet database after applying the advanced filter that displays three-bedroom homes with a pool.

image from book
Figure 9-18: The result of applying an advanced filter.

Tip 

When you select the Copy to Another Location option, you can specify which columns to include in the copy. Before displaying the Advanced Filter dialog box, copy the desired field labels to the first row of the area where you plan to paste the filtered rows. In the Advanced Filter dialog box, specify a reference to the copied column labels in the Copy To box. The copied rows then include only the columns for which you copied the labels.

Clearing an Advanced Filter

When you apply an advanced filter, Excel hides all row that don't meet the criteria you specified. To clear the advanced filter and display all rows, choose Data image from book Sort & Filter image from book Clear.




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net