Data Analysis with Microsoft Excel: Updated for Office 2007

58 Excel

4 Click OK.

Excel modifi es the list of service stations to show stations 1, 2, 6,

and 9. See Figure 2-18.

Figure 2-18

Stations

with

gas sales

< $8,500

The service station data for the other stations has not been lost, merely

hidden. You can retrieve the data by choosing the All option from the Gas

drop-down list.

Let’s say that you need to add a second fi lter that also fi lters out those

service stations selling less than $7,500 worth of other products. This fi lter

does not negate the one you just created; it adds to it.

To add a second fi lter:

1 Click the Other drop-down fi lter arrow, click Number Filters and

then click Less Than Or Equal to.

2 Type 7500 in the Custom AutoFilter dialog box.

3 Click OK.

Excel reduces the number of displayed stations to Stations 1, 2, and 6.

Stations 1, 2, and 6 are the only stations that have <$8,500 in gasoline

sales and <= $7,500 in other sales. Combining fi lters in this way is known

as an And condition because only stations that fulfill both criteria are

displayed.

You can also create fi lters using Or conditions in which only one of the

criteria must be true.

To remove the AutoFilter from your data set, you can either stop running

the AutoFilter or remove each fi lter individually. Try both methods now.