Using Advanced Filtering

 

Many people use Excel’s Autofilter feature to filter database records.  While that feature is valuable, it is somewhat limited, so Excel provides an Advanced Filtering feature.

 

The worksheet below lists invoice payments by different departments.  Download this worksheet

 

 

 

 

In order to use Advanced Filtering, you must first create a criteria range on your worksheet, where you can enter the criteria that Excel will use to filter your records..  The criteria range must be located  outside of your database and consist of at least  2 cells.  The first cell will contain a heading that exactly matches the headings of one of your database columns.  The second cell is where you will enter your criteria.  In the worksheet below, the criteria range consists of cells G1 and G2.  Cell G1 contains the column heading and cell G2 contains the criteria range.  The criteria  (>200) means that Excel will display all records with an amount of over $200.00.

 

 

 

After entering the  criteria, click in any cell in your database, then select Data, Filter, Advanced Filter from the Excel menu. The Advanced Filter dialog box displays.

 

 

The list range is the area of the worksheet that Excel determines is your database. On your worksheet, it will be outlined with dotted lines.  To indicate the criteria range, click in the Criteria range portion of the dialog box, then scroll over the cells that contain the criteria range.  Note:  If the criteria range in the dialog box already contains cell references, highlight them prior to scrolling over your criteria range.

 

 

You have the option of filtering the database “in place” or copying the filtered records to another location in the worksheet.  For now, leave “Filer the list, in-place” selected.  Click OK. 

 

Excel displays all records that contain an amount greater than $200.00.  To return to viewing all records, select Data, Filter, Show All from the Excel menu.

Criteria Operators

You can use the following criteria operators:

 

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

<>

Not Equal to

 

 

Applying Multiple Criteria

 

You can use Advanced Filter in a variety of ways.  The example below will filter any invoices with an amount greater than $200.00 but less than $700.00.  Note that your criteria range in this case would be cells G1 through H2.

 

 

 

 

 

When the filter is applied:

 

 

You can use dates in your criteria as well.  The following criteria filters all invoices for the month of February.

 

 

Using “OR” in Criteria

 

You can use “OR” in your criteria if you want to match two or more conditions.  For example, to filter the invoices for Accounting and Computing, you would use the following criteria

 

 

To filter records for Computing in February, your criteria would be:

 

 

Filtering the List to Another Location

 

You have the option of copying the filtered records to another location.  In the Advanced Filter dialog box, select “Copy to Another Location” then click in the Copy To box.  Click in an empty cell in another part of your worksheet, and click OK to have Excel place the filtered records there. Filtered data can only be copied to the active worksheet.

 

 

 

Filtering Unique Records

 

You can also filter unique records from your database.  For example:  In this database,  you might want a list of all departments that you do business with.  To filter unique records, click on any cell in the database and select Filter, Advanced Filter from the menu.  Complete the dialog box in this manner:

 

§         Under “Action”, select “Copy to Another Location”

§         Click in the List Range field, then click on the column heading that you want to filter by. (For example, in the database below, to filter by Department, click column heading B).

§         Leave the criteria range blank

§         Complete the Copy To field by clicking first in the field, then in an empty cell in your worksheet.

§         Click “Unique Records Only”, then select OK.