We’ve covered a variety of different ways to filter tables in the last 4 lectures. Excel Video 37 introduces advanced filters in Excel 2007, where the basic drop-down filters are not enough. The filters we’ve looked at so far allow you select multiple choices in the same column (September or October) or to select filters in multiple columns, all of which have to be true (September and Florida). If you want more filtering choices, watch Excel Video 37.
Start by adding several rows above your table to make a criteria area, then copy the column headings from the table to the first row in the criteria. Now, enter as many filters as you need to in the criteria area. Each row represents criteria that must all be true. If you have more than one row of criteria, Excel looks for data where all of row 1’s criteria are true or where all of row 2’s criteria are true or where all of row 3’s criteria are true, etc. Be careful not to include a blank row of criteria in your criteria range, or Excel will select all of the records in your table since all records meet the blank criteria.
Note the syntax for the criteria cells. To select all of the records where the city equals Atlanta, I enter =“=Atlanta” as the criteria in the city column. You can enter a variety of different formulas in the criteria area as long as Excel can calculate whether the criteria equals true or false. Wildcards (? and *) also work in these formulas.
Most of the time the basic filters will select the records you want to see. When the basic filters don’t get the job done, try these advanced filters for much more flexibility.
