Data Analysis with Microsoft Excel: Updated for Office 2007

60 Excel

gas sales < $8,500 and other sales < 5 $7,500. Thus, these criteria match

what you created earlier using the AutoFilter. Now apply these criteria to

the service station data. To do this, open the Advanced Filter dialog box and

specify both the range of the data you want fi ltered and the range containing

the fi lter criteria.

To run the Advanced Filter command:

1 Select the cell range A1:D11.

2 Click the Advanced button from the Sort & Filter group on the Data

tab. Excel opens the Advanced Filter dialog box.

3 Make sure that the Filter the list, in-place option button is selected

and that $A$1:$D$11 is displayed in the List range box.

4 Enter B16:C17 in the Criteria range box. This is the cell range con-

taining the fi lter criteria you just typed in. See Figure 2-19.

Figure 2-19
The Advanced
Filter dialog
box

range of

data values

range containing

the filter criteria

5 Click OK.

As before, only Stations 1, 2, and 6 are displayed. Note that the column

totals displayed in row 12 are not adjusted for the hidden values. You have

to be careful when fi ltering data in Excel because formulas will still be based

on the entire data set, including hidden values.

What if you wanted to look at only those service stations with either

gasoline sales < $8,500 or other sales <= $7,500? Entering an Or condition

between two different columns in your data set is not possible with the

AutoFilter, but you can do it with the Advanced Filter. You do this by plac-

ing the different criteria in different rows in the worksheet.